February 10, 2005 at 10:58 am
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/kKellenberger/accesstosqlserverlinkingtables.asp
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
February 23, 2005 at 1:46 am
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?
February 23, 2005 at 4:47 am
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).
February 23, 2005 at 6:59 am
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.
February 23, 2005 at 7:07 am
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
February 23, 2005 at 8:39 am
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 Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
February 23, 2005 at 10:06 am
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.
February 23, 2005 at 10:10 am
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
February 23, 2005 at 11:06 am
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 Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
February 23, 2005 at 11:12 am
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
February 23, 2005 at 11:19 am
I agree, the Access Projects are the way to go.
Back when I was doing developement in Access 2000, if I recall correctly, there was no way to import other types of data, like text files, using Projects. That functionality was important for several projects I was working on, so I stayed with the traditional approach. But, the functionality is there in Access 2003. So, if I had a new application to write, I would consider using Access Projects.
As far as traditional linking to SQL, I suggest that you keep the user from ever seeing the linked tables. I also always designed the app so that the user would only have one record attached to a form at a time. That worked very well.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
February 23, 2005 at 11:43 am
I decided that maybe I should check my idea of only allowing one record at a time to be attached to a form to make sure that it is really not pulling the whole table and that is the case. Opening the linked tables and attaching a form to a linked table is bad. Filtering, not so bad. I used Profiler to check.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
February 24, 2005 at 3:17 am
I found what I considered to be a bug in ADOX and therefore stopped using it.
The exact details as far as I remember were as follows.
As far as ADOX is concerned a SELECT query with no parameters was considered to be a VIEW. An action query or a SELECT with parameters was considered to be a PROCEDURE.
I was using ADOX to try and modify a QueryDef. The query should have been a view but the view collection said it didn't exist. Great, the Proc collection said it contained the query, but when I tried to edit it and save it I got a message basically saying that although it was in the proc collection it was a view.
I found DAO to be more stable and faster and so gave up on ADO within Access.
On the subject of linked tables, the problem is that you have to be very careful how much data you bind to your controls. Access gives all encompassing functionality to bound data and it is this that causes the locking problems.
The idea of limiting what is brought back from SQL to display on a form is basic good practice.
One of the problems I had when I first switched to VB was how to get the functionality that was standard in Access within my VB app. It wasn't long before I worked out that a completely different design approach was needed and that a VB front end to an Access database allowed far greater concurrency than an Access front end to an Access data file.
February 24, 2005 at 8:54 am
As far as updating data, I am equally comfortable using either ADO or DAO. If ADO is more efficient, that is what should be used. But when manipulating objects inside Access, DAO is a perfect fit when using the traditional MDB applications.
I am so impressed with the Access Data Projects, that any future Access/SQL work I do will be with this technology.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
February 24, 2005 at 9:00 am
I knew writing Access articles on a SQL Server site was going to be interesting. The only comment so far about my Access Project article is that they can only be used by one user at a time. Have any of you Access Project fans run into problems with this? My thought is that you give each user a copy of the ADP file.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor
Viewing 15 posts - 1 through 15 (of 31 total)
You must be logged in to reply to this topic. Login to reply