Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Other Forums
 MS Access
 Tables linked to SQL-Server

Author  Topic 

luisf
Starting Member

23 Posts

Posted - 2005-01-29 : 12:37:04
Hi all,
Please, some help will be highly appreciated
I've got some legacy code wich worked with an Access database, then I need to kept tables in SQL-Server.
Well, this can be done easilly by transfering the DB into SQL-Server, and defining the access' tables to be linked to the SQL-Server. This way I can operate into access database, while the data itself is kept in SQL-Server.
The problem is that the aplication crashes, and I don't know why. Do anyone overthere know the limitations -if they exist- for linked tables? Must the aplication fulfill any requirements the legacy application is not?
Thanks a lot

Auric
Yak Posting Veteran

70 Posts

Posted - 2005-01-29 : 14:55:47
What version of access and SQL-Server are you using?

Most times I just create the db locally in Access, use the upsize wizard, upsize the tables (all except switchboard table!) to the server.


Select * from users where clue > 0
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-30 : 16:54:22
But how namely IT crashes? At which action/moment?
Go to Top of Page

eyechart
Master Smack Fu Yak Hacker

3575 Posts

Posted - 2005-01-30 : 17:58:24
Freaky Yak Linguist is right! :)



-ec
Go to Top of Page

luisf
Starting Member

23 Posts

Posted - 2005-01-31 : 03:49:10
It seems to crash when creating the connection. I say it seems, because it is not easy to locate: it is in a DLL, and seems that exception arises... but no information about that. I didn't intend to obtain a concrete reply, because it is not easy to explain the problem.
Instead I would like to know general problems that can arise whe using an aplication connecting to an Access DB with the tables are linked to SQL-Server tables.
By the way, the app is using DAO.
I've tested with Access 97, 2000 and 2003, obtaining same results.
SQL-Server 2000 is used.
And the app is in VC++6.
By the way I dont know what "upsizing databse/tables" neither "switchboard table" is -sorry about that Im using spanish version-, but I think is exactly what I'm doing.

Thanx
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-31 : 05:30:06
I'm absolutely not sure but maybe when your vc++ app tries to open the dao.recordset on a linked sqlserver table in the mdb, it (sqlserver) sends a request for login and password, which (the request) the dll just ignores and the whole thing simply freezes.

ps: i never heard about any general limitations.
Go to Top of Page

luisf
Starting Member

23 Posts

Posted - 2005-01-31 : 06:24:34
That can be a cue...
Access DB has a user and password -as it was in the past-. Maybe it is needed to have same user and password in the SQL-Server?
When performing the creation of the tables in the access db being linked to SQL-Server Tablaes (by the way, ODBC link type), I set a user and pass (different from access, but valid in the SQL-Server).
The mdb file can be opened (mdb user and pass requiered), and then operations are done correctly: I can view and modify data, and evan local queries created wich work with the linked tables, and results are OK.
The problem is just with the connection when using legacy application.
I'll test some more posibilities playing with usr&pass in local and SQL bds...
Thanx
More sugestions?
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-31 : 06:36:59
pps: it occurs when an ODBC data source is set to use
SQL Server Authentication (instead of Windows NT Authentication).
Just my wild guess. Drop and relink all linked sqlserver tables
with an "corrected" DSN.

ppps: it absolutely does not matter that when you open linked
tables from inside the mdb it never asks for login & pwd.
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-01-31 : 06:42:16
> Access DB has a user and password -as it was in the past-.
> Maybe it is needed to have same user and password in the SQL-Server?

NO! Forget about Access' user & his password. They maybe whatever you like and have nothing to do with sqlserver (or windows) users & pwds.
Go to Top of Page

JimL
SQL Slinging Yak Ranger

1537 Posts

Posted - 2005-02-01 : 09:46:25
luisf
It has been my experience when trying to patch together an Access type front end into SQL. That.............

You are better off in the long run rebuilding the front end for SQL.



Jim
Users <> Logic
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2005-02-01 : 16:47:32
I second that opinion

I have never seen one instance where it worked.

1). because the original "developers" are not developers

2). if a true developer had something to do, it wouldn't have been done in access in the first place


Brett

8-)
Go to Top of Page

Stoad
Freaky Yak Linguist

1983 Posts

Posted - 2005-02-02 : 02:26:30
quote:
Originally posted by X002548

2). if a true developer had something to do, it wouldn't have been done in access in the first place


btw, who is "the true developer"? how it's defined there over the pond?
Go to Top of Page

Auric
Yak Posting Veteran

70 Posts

Posted - 2005-02-03 : 11:03:53
I'm with stoad..what is a true developer??

I have used Access/SQL a couple times. It is quick and dirty, but functional.

Select * from users where clue > 0
Go to Top of Page

luisf
Starting Member

23 Posts

Posted - 2005-02-09 : 10:10:05
I've been involved in other stuff during the past week...

What I'm found around the www is that this technique is common and very useful -didnt hear something like "better not to use"!-

Finally I get it running but only when querying.
Some test points to an error handling indexed columns...
Go to Top of Page

luisf
Starting Member

23 Posts

Posted - 2005-02-15 : 07:20:49
And finally the problem was:

-Linked tables can not be accesed by DAO CDaoTableDef for modification; it is correct for queries. Instead, it is requiered a CDaoQueryDef for modifying the table at hand.

Thanks anyway!
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2005-02-17 : 15:33:17
I hope you are still checking this thread.
From my personal experience, linked table is a way of empowering Access app to a distributed client/server level. Your number of concurrent users could be doubled or tripled (from 20-30 to near 100).
But you have to watch out for performance bottleneck down the road.
When you have around 50 users, and the size of it grows to about 100m, it will grind down like a snail. The reason is that Access front end does not do a good job of releasing resources (tables. queries, views...) until a compact is called.
Go to Top of Page

luisf
Starting Member

23 Posts

Posted - 2005-02-18 : 03:24:09
I'll take that into account.
Thanx!
Go to Top of Page
   

- Advertisement -