I use a variation of Dev Ashish table relink code. But I have added support
for the code to automatically search for the backend in three different
directories based on priority. The reason for this is that they would like to
take a copy of the backend on the road with them for read only purposes.

I have that part all figured out.

What I don't know how to do is check if the computer is connected to the
network and if so always default to the "main" backend.

My current code uses the function below to test the data. I need to add this
logic:
If linked tables are already connected to \\BPS2\MMData\MMData_be.mdb then
return true. Else if \\BPS2\MMData\MMData_be.mdb exists but NOT linked to it
then return false.

I know how to use dir(), but don't know how to check which BE my FE is
currently linked to.

'*********Code*************
Private Function IsDataOk() As Boolean
On Error Resume Next
'Return: True if the recordset can be opened, else False.
Dim rs As DAO.Recordset

Set rs = DBEngine(0)(0).OpenRecordset("tblContacts")
If Err.Number = 0 Then
IsDataOk = True
rs.Close
End If

Set rs = Nothing
End Function
'**********End Code***********

Try checking out the Tabledefs connection string. I'm not sure the specifics
on how to find it, its been a while, but when you access the tabledef for any
given table, all that info should be in there (this is how Dev's relink works
with multiple tables in the backend... parses the tabledef connection string
to determine the correct backend. You should be able to find most of what
you need inside his function.).

hth
--
Jack Leach
www.tristatemachine.com

"I haven't failed, I've found ten thousand ways that don't work."
-Thomas Edison (1847-1931)



"Doctor" wrote:

> I use a variation of Dev Ashish table relink code. But I have added support
> for the code to automatically search for the backend in three different
> directories based on priority. The reason for this is that they would like to
> take a copy of the backend on the road with them for read only purposes.
>
> I have that part all figured out.
>
> What I don't know how to do is check if the computer is connected to the
> network and if so always default to the "main" backend.
>
> My current code uses the function below to test the data. I need to add this
> logic:
> If linked tables are already connected to \\BPS2\MMData\MMData_be.mdb then
> return true. Else if \\BPS2\MMData\MMData_be.mdb exists but NOT linked to it
> then return false.
>
> I know how to use dir(), but don't know how to check which BE my FE is
> currently linked to.
>
> '*********Code*************
> Private Function IsDataOk() As Boolean
> On Error Resume Next
> 'Return: True if the recordset can be opened, else False.
> Dim rs As DAO.Recordset
>
> Set rs = DBEngine(0)(0).OpenRecordset("tblContacts")
> If Err.Number = 0 Then
> IsDataOk = True
> rs.Close
> End If
>
> Set rs = Nothing
> End Function
> '**********End Code***********

Thanks. I discovered that MSysObjects stores the current location for a
linked table. Now I can just compare that with my preferred connection.

"Jack Leach" wrote:

> Try checking out the Tabledefs connection string. I'm not sure the specifics
> on how to find it, its been a while, but when you access the tabledef for any
> given table, all that info should be in there (this is how Dev's relink works
> with multiple tables in the backend... parses the tabledef connection string
> to determine the correct backend. You should be able to find most of what
> you need inside his function.).
>
> hth
> --
> Jack Leach
> www.tristatemachine.com
>
> "I haven't failed, I've found ten thousand ways that don't work."
> -Thomas Edison (1847-1931)
>
>
>
> "Doctor" wrote:
>
> > I use a variation of Dev Ashish table relink code. But I have added support
> > for the code to automatically search for the backend in three different
> > directories based on priority. The reason for this is that they would like to
> > take a copy of the backend on the road with them for read only purposes.
> >
> > I have that part all figured out.
> >
> > What I don't know how to do is check if the computer is connected to the
> > network and if so always default to the "main" backend.
> >
> > My current code uses the function below to test the data. I need to add this
> > logic:
> > If linked tables are already connected to \\BPS2\MMData\MMData_be.mdb then
> > return true. Else if \\BPS2\MMData\MMData_be.mdb exists but NOT linked to it
> > then return false.
> >
> > I know how to use dir(), but don't know how to check which BE my FE is
> > currently linked to.
> >
> > '*********Code*************
> > Private Function IsDataOk() As Boolean
> > On Error Resume Next
> > 'Return: True if the recordset can be opened, else False.
> > Dim rs As DAO.Recordset
> >
> > Set rs = DBEngine(0)(0).OpenRecordset("tblContacts")
> > If Err.Number = 0 Then
> > IsDataOk = True
> > rs.Close
> > End If
> >
> > Set rs = Nothing
> > End Function
> > '**********End Code***********

Don't be messing around with any tables that start with Msys. You may cause
problems.
The correct syntax is:
Currentdb.Tabledefs("TableName").Connect

That will give you the path to the BE.
--
Dave Hargis, Microsoft Access MVP


"Doctor" wrote:

> Thanks. I discovered that MSysObjects stores the current location for a
> linked table. Now I can just compare that with my preferred connection.
>
> "Jack Leach" wrote:
>
> > Try checking out the Tabledefs connection string. I'm not sure the specifics
> > on how to find it, its been a while, but when you access the tabledef for any
> > given table, all that info should be in there (this is how Dev's relink works
> > with multiple tables in the backend... parses the tabledef connection string
> > to determine the correct backend. You should be able to find most of what
> > you need inside his function.).
> >
> > hth
> > --
> > Jack Leach
> > www.tristatemachine.com
> >
> > "I haven't failed, I've found ten thousand ways that don't work."
> > -Thomas Edison (1847-1931)
> >
> >
> >
> > "Doctor" wrote:
> >
> > > I use a variation of Dev Ashish table relink code. But I have added support
> > > for the code to automatically search for the backend in three different
> > > directories based on priority. The reason for this is that they would like to
> > > take a copy of the backend on the road with them for read only purposes.
> > >
> > > I have that part all figured out.
> > >
> > > What I don't know how to do is check if the computer is connected to the
> > > network and if so always default to the "main" backend.
> > >
> > > My current code uses the function below to test the data. I need to add this
> > > logic:
> > > If linked tables are already connected to \\BPS2\MMData\MMData_be.mdb then
> > > return true. Else if \\BPS2\MMData\MMData_be.mdb exists but NOT linked to it
> > > then return false.
> > >
> > > I know how to use dir(), but don't know how to check which BE my FE is
> > > currently linked to.
> > >
> > > '*********Code*************
> > > Private Function IsDataOk() As Boolean
> > > On Error Resume Next
> > > 'Return: True if the recordset can be opened, else False.
> > > Dim rs As DAO.Recordset
> > >
> > > Set rs = DBEngine(0)(0).OpenRecordset("tblContacts")
> > > If Err.Number = 0 Then
> > > IsDataOk = True
> > > rs.Close
> > > End If
> > >
> > > Set rs = Nothing
> > > End Function
> > > '**********End Code***********