Linked table problem

[Judy Ward]

We have two different Access databases, the SAR database and the Use Case
database. The SAR database has code to "kick out" users which needs to be
done daily in order to make code changes and/or compact & repair the db. The
Use Case db wants to link to a table in the SAR db to provide information for
a report. The problem is that when the Use Case db has the SAR db linked
table open, the "kick out" code does not work and the SAR database cannot be
compacted and repaired.

The only solution I can think of is to programatically link the table, run
the query and then unlink the table. Can anyone provide sample code to do
this? Or does anyone have a different idea?

Thank you,
Judy

 

[Clifford Bass]

Hi Judy,

Here is a way that should work. There may well be other ways. This one
is based on something I was in someone's post not too long ago. You can
actually use the external database name in the query. Start a new query and
enter something like this in the SQL View:

SELECT A.Field1, A.Field2
FROM [C:\Databases\SomeDatabase.mdb].[tblSomeTable] AS A;

Base your report off of it. When the report is run it will make the
connection and then disconnect when done. Note that Access may modify the
format of the FROM clause and then become finicky about allowing you to
modify it. In which case just adjust the FROM clause back to its original
format. Also, it does not seem to disconnect the first time that you create
the query and report. So exit out of Access and go back in. Then it should
connect and disconnect pretty much right away.

Hope this helps,

Clifford Bass