Linked SQL Tables in Access
-
Our company has a third-party Visual Basic application that uses an Access database on the server and another at the workstation, with linked tables, for data access. This is an older program and was developed using DAO and the old Data Control for all reads,writes, updates, etc. I am wanting to port the primary database over to SQL Server and have designed a database with the needed tables and even did a few test runs. Just wondering what problems I might encounter using linked SQL tables. This app generates a large amount of data and is really not suitable for Access. Unfortunately, this app is very poorly designed and often queries an entire table for lookups (unbelievable, but they bring the whole table over and search for the value by iterating the recordset). I was also hoping that by going to SQL, I could gain some advantages for other projects I am developing against the database and cut down on network traffic.
-
This was removed by the editor as SPAM
-
Don't use linked tables for this, as they are generally even worse than looping through the recordset. In Microsoft Access 2000 and above you can create projects that connect direct to SQL to make this sort of development task very very easy (I still prototype stuff in Access this way and I can churn out very complex apps in under a day with it) but you will need to compleatly throw out DAO and Data controls
Keith Henry
DBA/Developer/BI Manager
Keith Henry
According to everyone I know I "do something with computers?" for a living, so there you go. -
Upgrading to SQL Server alone may not be enough; you will end up re-writing many of the controls (if not all of them) to use that advantage. The investment would not be just the SQL Server but the time you will need to redo and test everything.
Don't use linked tables unless you must. It is only a small band-aid to a much larger problem (your application is out-dated).
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
-
Thanks for responses, but maybe I stated my problem incorrectly. I, unfortunately, cannot changes the source code since this was developed byan outside party. I was simply wanting to change the backend database to SQL instead of Access. Like I said, the frontend application uses an Access database with linked tables to the server database. I was simply going to replace this frontend with an Access database that used only linked tables to SQL Server. This app does not hit the server database directly. I was just wondering what, if any, problems I might see by using the linked SQL tables. I am developing some reporting apps against this and also some data import interfaces from our ERP system. Things would be alot easier if the backend database was in SQL Server. Thanks.
-
From that description stick with access for the backend. jet to jet is marginly better than jet to sql, but still awful
Given how amazingly easy Access 2000 is to write on, you might still be better off re-writing the app to work as a Access Project.
Keith Henry
DBA/Developer/BI Manager
Keith Henry
According to everyone I know I "do something with computers?" for a living, so there you go. -
Yes, based on what you said there is no valid reason to upgrade to SQL Server. The application will not be able to take advantage of the upgrade.
Bonne chance
Dr. Peter Venkman: Generally you don't see that kind of behavior in a major appliance.
Patrick
Quand on parle du loup, on en voit la queue
-
I don't do any Access development, but we do use it (at work, not here) for data management and the linked tables work well for the most part. Not always, some queries seem to end up consisting of bringing all the data back to Jet to resolve.
Linking in the tables and trying it wouldnt take very long, based on my experience I think I'd invest the time to see what happened.
Andy
-
You should first make sure that you are still supported when you change the configuration yourself.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Thanks for all of your responses. Yes, Frank, I will still be supported by the vendor. Like I mentioned earlier, I have already ported the backend into a matching SQL database, so I think I will give it a shot. The few short test runs have seemd to work fine, I was just worried about full production over a longer time period. We will see what happens. Again, thanks for everyone's input.
-
quote:
I don't do any Access development, but we do use it (at work, not here) for data management and the linked tables work well for the most part. Not always, some queries seem to end up consisting of bringing all the data back to Jet to resolve.Linking in the tables and trying it wouldnt take very long, based on my experience I think I'd invest the time to see what happened.
With Jet as the front end (ie linked tables) ALL queries on linked tables bring the entire dataset to the front end and then do the all the work on the local client. You may see some improvement, depending on where the original back end is and where the new SQL version is, but you may be wasting your time.
Keith Henry
DBA/Developer/BI Manager
Keith Henry
According to everyone I know I "do something with computers?" for a living, so there you go. -
quote:
With Jet as the front end (ie linked tables) ALL queries on linked tables bring the entire dataset to the front end and then do the all the work on the local client. You may see some improvement, depending on where the original back end is and where the new SQL version is, but you may be wasting your time.
What about linked tables and ODBCDirect (don't know if it still exists) or SQL Passthrough Queries?
Don't know if it works with linked tables, but using this will bypass Jet and your statements fire directly against the underlying db.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Keith, do you have a reference that supports that? My experience (viewed, not tested) doesnt match that - I've got tables with 5 mil rows, dont see I could be pulling all of those each time.
Andy
-
quote:
What about linked tables and ODBCDirect (don't know if it still exists) or SQL Passthrough Queries?
SQL Passthrough Queries will be handled by SQL, but clangley is talking about linked tables only
quote:
Keith, do you have a reference that supports that? My experience (viewed, not tested) doesnt match that - I've got tables with 5 mil rows, dont see I could be pulling all of those each time.
No, but it doesn't execute any SQL, just gets all of the table. Maybe Jet is smarter than I thought and is doing something at a deeper level. I've handled very large datasets linked in Access and in general it is very very slow and network heavy, but most of this is from my Access 97 days, so maybe XP/2003 is better.
Keith Henry
DBA/Developer/BI Manager
Keith Henry
According to everyone I know I "do something with computers?" for a living, so there you go. -
quote:
SQL Passthrough Queries will be handled by SQL, but clangley is talking about linked tables onlySomehow I remembered you need linked tables to run PassThrough queries.
My mistake.
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply