Link table at runtime error

[richard1458]

I have tried reconnecting linked tables at runtime using the very helpful suggestions in thread181-38038 .
It works fine on two different computers that store their data on different drives (one has drive C:, the other has drive Z:, with folder\datafile names the same in both cases). However, I still have the following problem:
When I copy the 'front-end' database from one computer to the other, the first time I open it, it gives me an error message that it can't find the linked tables. In other words, it is trying to link to the tables using the previous settings before running the AutoExec macro that runs the 'ConnectToData' code. Is there a way to either:
i) ensure this AutoExec code runs before Access tries to link to the existing linked tables OR
ii) Trap the error message, and then run the ConnectToData code?
(By the way, I don't want to go any further down the 'Map Network Drive' route - other people use this database on their machines, and I am the only user trying to share it across a network connection, and also I'm not in a position to suggest they fiddle with their network settings)
- Richard

 

[RickSpr]

Access doesn't automatically open the back end database nor check that it exists. Somewhere in your startup code you must be trying to access a back end table. I would think this would have to be in your Autoexec code; I don't know what else could be running ahead of it.

Can you identify where your error message is occurring (as a line in your code)? If so, you should be able to trap the error and fix the connect strings up there.

I have written several databases that check the back end at startup, and if not found invoke a dialog to help the user find it. I check it by trying to open each table and trapping the error.

Rick Sprague

 

[richard1458]

I may have not explained the situation well here.
I run the database from one of two computers - my desktop and my laptop. I've networked them together (using an RJ45 crossover cable) I've tried copying the front and back ends from one to the other, but it really causes problems if I forget to copy them back afterwards. I therefore want to keep just one copy of both the front end and the data tables - on the C: drive of my laptop.
The tables are therefore already linked, normally to a file on the C: drive. The error therefore occurs when running the front-end for the first time on the other computer - before it can get to relink the tables on the new drive (Z: as it happens) it displays an error message along the lines of 'Couldn't find file'). I just want to find a way of trapping this error message.
Any clearer now?
- Richard

 

[RickSpr]

Thanks for the details, but I don't think it changes anything. On your desktop, you open the front end on its Z: drive, right? When you open the front end, Access shouldn't have any problem. It doesn't attempt to open the back end until the first time you open a linked table. You should be able to RefreshLink the tables in your Autoexec code and not get this error.

Does your Autoexec, prior to doing its RefreshLink, open a form bound to the back end tables? That would cause the problem. If not, something in your Autoexec must be causing Access to open the tables too soon.

Try this experiment: On your laptop, open the front end while holding down the Shift key. That prevents your Autoexec from running. You should get the database window, and no error message. If that's what happens, it tells you the problem is in your Autoexec code.

You can trap the error if it's caused by your code, but you have to know exactly where it's occurring. And you shouldn't need to trap the error, once you know how it's being raised. You can probably just rearrange your code and avoid it in the first place.

Feel free to post your Autoexec code. I'll be happy to take a look at it. Rick Sprague

 

[richard1458]

Many thanks Rick - when I thought through what was happening on startup I realised that althought the Autoexc code did not refer to any tables or forms before reconnecting to the data, I had a sturtup form which did, and it seems Access opens the startup form before running any Autoexec code.
Consistent with your suggestion, I put the form-opening instruction into the Autoexec code instead, after refreshing the link to the tables.
Works perfectly now. Thank you!
- Richard