SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Access to SQL Server: Linking Tables


Access to SQL Server: Linking Tables

Author
Message
Kathi Kellenberger
Kathi Kellenberger
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2451 Visits: 347
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kKellenberger/accesstosqlserverlinkingtables.asp

Aunt Kathi
Linchpin People Teammate
SQL Server MVP
Author of Expert T-SQL Window Functions
Dave Poole
Dave Poole
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16580 Visits: 3403
I notice that you are using DAO.

I last touched MS Access 6 years ago and at the time Microsoft were pushing ADOX as the way to manipulate MS Access objects and were actively trying to kill DAO. Is this still the case or is it that DAO works and if it ain't broke don't fix it?

LinkedIn Profile
www.simple-talk.com
Luis Fernando Robledano...
Luis Fernando Robledano Esteban
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 1
I've been around Linked tables, but found -testing and forums- a great problem when using.

1. Mainly, DBs has to be updated
2. Mainly, DBs operations has to be protected in transactions
3. Passthrough queries are needed for updating linked tables
4. Passthrough queries cannot be (properly) used in transactions

=> Mainly you cannot use linked tables !?!?! Amazing

Even if you dont want to use transactions, updating (inserting new records or updating existing) seems to need changing the code of existing applications.
I've been many times sugested to rebuilt the application just to work directly to SQL-server (by the way, using ADO instead of DAO).
LeeFAR
LeeFAR
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1023 Visits: 327

I want to share a problem we have recently encountered. We have an Access application that is connecting to SQL Server using ODBC just as this article shows. This application has been working this way for more than two years. The SQL Server this app’s database is on also hosts a number of other production databases.

About six months ago, we began to see a problem with our server. Out of the blue, the server would become very unresponsive. All applications will have very slow query execution times. Looking at the resources of the server, the only thing that would stand out is an excessive I/O utilization. The only “cure” to the problem was to fail the server over (it is a cluster server) to the other node and things would return to normal for a while. This problem had no pattern and could not be predicated. After about three months of our own investigation, we had to bring Microsoft into the picture. They have spent around three months pouring over trace logs and all kinds of information. They finally pointed out the little Access application I mentioned above. They showed concern with this application because it was showing extensive “transaction” times on tables. They told us to investigate this application. After six months of having our production server go down at least once a week, management decided to move this application to a server by itself to see if the problem on our production server would go away. We have been running over three weeks now without a problem on our production server.

We haven’t really dug into the application in question, but I do know this is an Access front end that is linked to SQL tables. And I know the application’s forms are bound to the tables. And I know that when you open one of the forms, you will see an “open transaction” to the table. As long as the form is open, the transaction will remain open. We at this point suspect this kind of behavior is indicative to Access linked tables and nothing really can be done about it, short of rewriting the application.

I do want to point out the Access application is written in Access 97 (…yes I know). It is using MDAC 2.8 ODBC to connect to a SQL Server 2000 server. It is connecting using the TCP/IP protocol. There are about 15 continuous users of the application.

If anyone out there has ever seen or heard of problems like this, I would love to hear from you.





noeld
noeld
SSC-Insane
SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)SSC-Insane (22K reputation)

Group: General Forum Members
Points: 22414 Visits: 2048

Just to add my $0.02 ADP (Access Data Projects) are the ones that in my opinion should be used if you plan to use SQL Server on the Back end and MSACCESS on the front end.

It can handle sp, functions, views, Transaction Processing and by default uses ADO.

HTH




* Noel
Kathi Kellenberger
Kathi Kellenberger
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2451 Visits: 347
While working on this article, I found that Access 2003 had references to DAO and ADO by default. I haven't done a lot with Access for the last 2 years and don't have any inside information on Microsoft's plans. I tried using ADOX to manipulate objects, but it always seemed much more difficult, it was always missing something. I'll have to see if I can come up with the same examples using ADO code.

Aunt Kathi
Linchpin People Teammate
SQL Server MVP
Author of Expert T-SQL Window Functions
Jags2001
Jags2001
Old Hand
Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)

Group: General Forum Members
Points: 325 Visits: 128

Luis and Lee's problems are caused by linked tables -- because you can not control the locking of linked tables (which by default will give you all the contents of th whole table). The table is locked while the linked table is open. (correct me if am wrong)

For an access app, I would suggest not linking but querying the SQL Server table (if possible filtering the rows with a select) using ADO as a recordset then release the recordset as soon as possible to reduce locking- which is handled by ADP.

The other minor problem is that you have to relink the table if there is a table change like adding or deleting a table.





Keith Henry
Keith Henry
SSChasing Mays
SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)SSChasing Mays (649 reputation)

Group: General Forum Members
Points: 649 Visits: 6

As a rule 99% of the time you should never use linked tables from Access to SQL. It's just a very good way to snarl up your SQL server.

I always advise using Access projects instead, they give you the easy forms and macros, but with SQL as the backend (rather than Jet accessing SQL)






Keith Henry


According to everyone I know I "do something with computers?" for a living, so there you go.
Kathi Kellenberger
Kathi Kellenberger
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2451 Visits: 347

I have written several successful applications that did update SQL data using linked tables using Access queries, DAO or ADO code. The last big project I worked on I actually translated all of the DAO to ADO using Access 2000/SQL 2000. I think that like any programming environment, things can be done correctly or incorrectly, with good performance or bad depending on the tecniques used. I did primarily Access/SQL programming for about 5 years so I learned a lot about making it work.

Tomorrow there will be an article about Access Projects. I think that is probably the way to go if you want an Access GUI to your SQL apps.



Aunt Kathi
Linchpin People Teammate
SQL Server MVP
Author of Expert T-SQL Window Functions
Steve Rosenbach
Steve Rosenbach
SSC-Addicted
SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)SSC-Addicted (477 reputation)

Group: General Forum Members
Points: 477 Visits: 206

I strongly agree with NoelD and Kieth Henry - Access Data Projects (.adp) is the way to go for a number of reasons.

One of them is that each linked table uses a connection to SQL Server - with an .adp, you're generally using only two or so connections regardless of how many tables are in the database.

I refer to Access Database Projects as "Access on Steroids." As a longtime Access developer (I started in early 1993 with Access 1.0) and a big proponent of Access, I am convinced that using linked tables and DAO with SQL Server is a bad idea.

Stay with DAO for applications that will never migrate from JET - but for SQL Server front ends - go with ADPs and OLE DB.

Best regards,
SteveR





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search