Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 1234»»»

Access to SQL Server: Linking Tables Expand / Collapse
Author
Message
Posted Thursday, February 10, 2005 10:58 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, September 15, 2014 8:25 AM
Points: 769, Visits: 251
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kKellenberger/accesstosqlserverlinkingtables.asp

Aunt Kathi
Microsoft
(Former SQL Server MVP)
Post #160886
Posted Wednesday, February 23, 2005 1:46 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 2:56 PM
Points: 2,903, Visits: 1,820
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
Newbie on www.simple-talk.com
Post #163416
Posted Wednesday, February 23, 2005 4:47 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, April 4, 2005 5:52 AM
Points: 1, 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).

Post #163446
Posted Wednesday, February 23, 2005 6:59 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, August 26, 2014 9:18 AM
Points: 207, Visits: 200

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.




Post #163474
Posted Wednesday, February 23, 2005 7:07 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 5:47 AM
Points: 6,266, Visits: 2,029

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
Post #163476
Posted Wednesday, February 23, 2005 8:39 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, September 15, 2014 8:25 AM
Points: 769, Visits: 251
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
Microsoft
(Former SQL Server MVP)
Post #163530
Posted Wednesday, February 23, 2005 10:06 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, September 21, 2012 7:39 AM
Points: 135, 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.




Post #163545
Posted Wednesday, February 23, 2005 10:10 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 16, 2009 6:32 AM
Points: 331, 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.
Post #163547
Posted Wednesday, February 23, 2005 11:06 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: Monday, September 15, 2014 8:25 AM
Points: 769, Visits: 251

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
Microsoft
(Former SQL Server MVP)
Post #163555
Posted Wednesday, February 23, 2005 11:12 AM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Tuesday, September 9, 2014 5:00 PM
Points: 91, Visits: 195

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




Post #163556
« Prev Topic | Next Topic »

Add to briefcase 1234»»»

Permissions Expand / Collapse