Relink all tables in other database

[Jim]

I have a database with a front and back end setup as follows:

Front:
\\nt\database\NewDB\Stats.mdb = forms, reports, queries

Back:
"\\nt\database\NewDB\StatsData.mdb" = tables

When the front is opened, I want to check and see if all the tables
that are in the back end are currently linked to the front end... is
there a way to do this without hard-keying all the names of the tables?

Thanks for helping.

[Tom van Stiphout]

On 7 Jun 2005 06:14:32 -0700, "Jim" <jl*******@gmail.com> wrote:

Yes. You can loop over the TableDefs collection, checking the
Attributes property for dbAttachedTable.

-Tom.

I have a database with a front and back end setup as follows:

Front:
\\nt\database\NewDB\Stats.mdb = forms, reports, queries

Back:
"\\nt\database\NewDB\StatsData.mdb" = tables

When the front is opened, I want to check and see if all the tables
that are in the back end are currently linked to the front end... is
there a way to do this without hard-keying all the names of the tables?

Thanks for helping.

[Jim]

I'm not that much of a guru... can you point me to where I can find
more information on how to do what you suggest above?

[Chuck Grimsby]

Create a new module, name it "basCheckIfReLinkNeeded", and place the
code below in it. Then you can call the function from your AutoExec
macro, or in the Startup Form.

Public Function CheckIfRelinkRequired2()
Dim lngUnLinkedTables As Long
lngUnLinkedTables = _
DCount("Database", _
"MSysObjects", _
"[Type]=6 AND " & _
"Dir$([Database])=''")
If lngUnLinkedTables > 0 Then
MsgBox lngUnLinkedTables & " Tables Cannot Be Found" &
vbNewLine & _
"You Must Relink The External DataBases!", _
vbCritical + vbOKOnly, _
"Relink Required"
End If
End Function

You may need A2K or above for this to work. I haven't tried in in
Access 97 or below... (Yeah, laziness....)
Jim wrote:

I have a database with a front and back end setup as follows:

Front:
\\nt\database\NewDB\Stats.mdb = forms, reports, queries

Back:
"\\nt\database\NewDB\StatsData.mdb" = tables

When the front is opened, I want to check and see if all the tables
that are in the back end are currently linked to the front end... is
there a way to do this without hard-keying all the names of the tables?

Thanks for helping.