Our business LAN is often quite slow, and as a result, my Access databases that link to ODBC tables over the LAN run very slowly, even when I am not using those tables. Can I temporarily break/ remake the connections to these linked tables using code?
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
Milewskp,
<<Can I temporarily break/ remake the connections to these linked tables using code?>>
You can certainly do that, but I don't think it's going to get you anything. If your not using the tables, there is no connection to the ODBC source. Som simply not using the tables while the LAN is slow should do it without deleting the links and then readding them.
What is it that your trying to achieve?
JimD
Hi Jim,
I'm trying to keep the database fast regardless of the network speed. The database in question is on my C drive, yet when the network is slow, the performance of my database slows down too. If I replace the linked tables with local copies, it's fine. The linked tables are only used by queries that only run at startup.
Milewskp,
... besides the performance will still be the same once the tabls are relinked.
I'm with Jim:
<What is it that your trying to achieve?>
Are you trying to speed up the LAN or the DB?
There are tons of factors that contribute to LAN performance.
Can you list some of your network specs?
JeffCoachman
<<The database in question is on my C drive, yet when the network is slow, the performance of my database slows down too. If I replace the linked tables with local copies, it's fine. The linked tables are only used by queries that only run at startup.>>
That's odd, unless I'm not understanding the setup. Even if linked through a share, it would still be local to your machine and would be fast. Let's make sure we understand the setup first.
1. Is this a split app? (Front end with everything but tables and a back end with only tables). Sounds like it is.
2. What is the BE database engine; JET or something else (ie. SQL server)?
3. Where is the BE database located?
JimD.
Milewskp, perhaps we are not explaing this clearly, from our point of view. Ehen we say that dropping the link will not help, we are not saying that a local table is not faster than a linked table but that at some point in time, you will have to connect through your network and when that happens it will be slow. Once the connection is made and your queries have been run, the fact that you have a connection through the network to tables you are no longer using should not make any difference in how the rest of your app works.
Not sure you can just *blank* out the connect info but gicve this a try
First, get the connect string as it is now when the table is linked
in the immediate window
?CurrentDb.TableDefs("MyTa
Public Function RelInkODBC()
CurrentDb.TableDefs("MyTab
CurrentDb.TableDefs("MyTab
End Function
Steve
<<I was hoping there is a method by which I can break the connection without actually deleting the linked table from the database.>>
If you break the link, then nothing that uses the table is going to work correctly. Again I'm a little confused as to what the problem is exactly. For example, opening a query based on the table in design mode, you'll get an error. If your not using the table, then there is no impact to your application.
I just don't see how doing anything with the link is going to help.
FWIW, I have full code for dropping and recreating ODBC tables based on a local table and for refreshing table links based on the current tableDefs (I have code for JET based tables as well).
JimD
Business Accounts
Answer for Membership
by: capricorn1Posted on 2008-03-20 at 06:07:30ID: 21170889
see this links
DSN
http://www.carlprothman.ne
DSN-Less
http://www.carlprothman.ne