Code to check table links

[AlexNeil]

Hi,

I am upsizing an Access2000 application to use SQL Server 7. The application will still exist in Access2000, but the data tables will be stored in SQL Server 7.

The Access2000 application uses linked tables, and the startup code checks the links to these tables. To do this, it attempts to open each linked table by using a "Do While" loop ...
[tt]
Do While
Set rstTestTable = New ADODB.Recordset
rstTestTable.CursorLocation = adUseServer
'Open Table as recordset
rstTestTable.Open TName, _
Proj.Connection, adOpenStatic, adLockOptimistic
rstTestTable.Close
Loop
[/tt]
This code works when the tables are in SQL Server 7. However, I tested the scenario of the tables' links being checked when the server was not running. In this case, the rstTestTable.Open statement took around 20 seconds to time out. Given that there are many linked tables to check, this means that the application hangs for around 15 minutes!

Is there a quicker way to check that the linked tables are accessible?

thanks
Alex

 

[moonshadow]

I haven't done any Access for 2 years, and that was Access '97, but it appears to me that you're not actually testing the links in Access, but are opening the tables explicitly on the server. Don't you need to open the tables actually in Access? Either way, wouldn't you also want to take some sort of remedial action when you find that the first of your tables is inaccessible?

 

[AlexNeil]

I think the point of the code is to use Access to open the tables - certainly the code is run from Access.

There is an error trap in the code at the moment, but I have removed that from the above extract for simplicity. Performing remedial action is easy - right now I need a (relatively) quick way of checking that the linked tables are working.

Do you know a way of performing this check?

Thanks
Alex

 

[moonshadow]

Try looking at the following MS Articles:

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q208295&GSSNB=1

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q291264&GSSNB=1

They discuss linked tables (the second is linked tables from another Access database, but some of the content should still apply).
Unless it's completely different in Access 2000, you should check your linked tables, and not create a new connection to the server, and open tables from there. For example, how do you know your linked tables are pointing to the same server? Surely if connection to your first table fails, it's pointless to check others, so it should only take 20 seconds to fail. I'm sorry, I'm obviously not really understanding your problem.

 

[AlexNeil]

Thanks for those articles - they're a great help.

I've taken this work on as a short project - someone else wrote the original app. I'm not sure why they wanted to check the link to every table - I agree with you, if 1 fails then there's obviously something wrong so what's the point in carrying on.

The code that's being used - the rstTestTable.Open bit - I had assumed that this was opening the linked table within Access (as that's where the code is run from) and was therefore a good way to check if the link is working. Is that assumption wrong?

cheers,
Alex

 

[moonshadow]

Naw - it looks like the rstTestTable.Open bit is opening up a new recordset based on the table in TName (just a piece of text), using the proj.connection connection. I don't know what that is - maybe new in Access2000. But what if tables were being linked from two different servers ? proj.connection can only point to one server. Sorry I can't be much more help - trying to get work to give me Access2000.