auto link backend

[agehoops]

I've looked all through this Forum and through Google and found a few solutions but have struggled to get them to work exactly as I'm after.

What I'm looking for, is some code that runs when the system is launched, which checks to see if the backend exists. If it doesn't, then bring up a file browser box for the user to find the backend, and once selected, have it automatically relink the database. If the backend does exist, then just carry on as normal.

The solutions I've found have either requested a new backend file every single time, not created the link when told to, or ask for a new link even when there is one in place. A couple of them were probably just because I didn't truly understand the code but still.

Thanks
Aidy

 

[gemma-the-husky]

what i do is this - assuming that all your tables connect to the same back end - otherwise its a bit more complex

if you look at your tables, a linked access table shows with an arrow against. foxpro or sql tables have a different symbol

now a native table has no arrow

the table information is stored in the tabledefs collection, the attributes for each tabledef will show up in the intellisense syntax checker when you use them.

so pick a table you know OUGHT to be connected. the connection method is held in the tabledef 's connect property

so

currentdb.tabledefs("mytable").connect is the connection string

if its an access table it will look something like

database="path to backend"

so knowing this, you can test if this path is what you expect.

if not (eg you've moved the backend to a different location) then you need code to drop the current links and recreate the correct links,

you've probably got the code to do this already with what you've got from the sound of it.

i actually store all the tables i want to link in another local table, and just iterate a recordset to pick up and link all the indicated tables.

but basically for each table you need
a) the name you want to call it in your database
b) the tablename IN the backend database (probably the same)
c) the name and path of the backend database

 

[agehoops]

Alright cool thanks. I'll look through, see if i can figure something out. Will post back later. Thanks

 

[WayneRyan]

Hoops,

If you just want to relink all tables to the newly found BE database:

Dim tdf As DAO.TableDef

For Each tdf In dbs.TableDefs
   If Len(tdf.Connect) > 0 Then
      tdf.Connect = ";DATABASE=" & Me.txtFilterDatabase
      tdf.RefreshLink
   End If
   Next tdf

Or a specific table:

Dim tdf As DAO.TableDef

Set tdf = CurrentDb.TableDefs("SomeTable")
tdf.Connect = ";DATABASE=" & Me.txtFilterDatabase
df.RefreshLink

I think this is where wanting to explicitly write to MsysObjects came from.

hth,
Wayne

 

[agehoops]

Thanks for the responses people, really big help.

WayneRyan

I don't know if that code works as it is giving me the error "Object Required" and then highlighting the "For Each tdf In dbs.TableDefs" line ??

This is the code i'm trying to use now:

Dim BackendLocation As String

BackendLocation = DLookup("[Database]", "MSysObjects", "Not IsNull(Database)")

If Dir$(BackendLocation) = "" Then
MsgBox "The Backend Cannot Be Found. Please Choose The Location of The Backend", vbCritical, "Cannot Find Backend"

Dim bRepeat As Boolean
Dim strFilter As String
Dim strInputFileName As String
Dim tdf As DAO.TableDef

bRepeat = True
While bRepeat = True
strFilter = ahtAddFilterItem(strFilter, "Database File (*.MDB)", "*.MDB")
strInputFileName = ahtCommonFileOpenSave( _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please Select a Backend File...", _
                Flags:=ahtOFN_HIDEREADONLY)
                
If strInputFileName = "" Then
 If MsgBox("You Must Select A Backend. Without It, The System Cannot Run. Are You Sure?", vbYesNo, "Are You Sure?") = vbYes Then
    MsgBox "The Database Will Now Close.", vbCritical, "Warning"
    DoCmd.CloseDatabase
 End If
Else
 bRepeat = False



For Each tdf In dbs.TableDefs
   If Len(tdf.Connect) > 0 Then
      tdf.Connect = ";DATABASE=" & strInputFileName
      tdf.RefreshLink
   End If
   Next tdf
   
   
End If
Wend
End If

And the reason i was looking at simply writing to the MSysObjects table, is that this is where i found the location of the current backend for use in something else. Thought i would've been easier to simply change this. Apparently not. haha

 

[agehoops]

Ah don't worry people, have sorted it out now

Thanks so much for all your help, really appreciate it

Aidy

 

[The Sky Heart]

please agehoops
can you post the code u used for that
thanks

 

[agehoops]

Yea sure thing

Public Sub RelinkTables(NewPathname As String)
    Dim Dbs As Database
    Dim Tdf As TableDef
    Dim Tdfs As TableDefs
    Set Dbs = CurrentDb
    Set Tdfs = Dbs.TableDefs
'Loop through the tables collection
       For Each Tdf In Tdfs
        If Tdf.SourceTableName <> "" Then 'If the table source is other than a base table
            Tdf.Connect = ";DATABASE=" & NewPathname 'Set the new source
            Tdf.RefreshLink 'Refresh the link
        End If
    Next 'Goto next table
End Sub
Private Sub Form_Load()
Dim BackEndLocation As String

BackEndLocation = DLookup("[Database]", "MSysObjects", "Not IsNull(Database)")

If Dir$(BackEndLocation) = "" Then
MsgBox "The Backend Cannot Be Found. Please Choose The Location of The Backend", vbCritical, "Cannot Find Backend"

Dim bRepeat As Boolean
Dim strFilter As String
Dim strInputFileName As String
Dim Tdf As DAO.TableDef

bRepeat = True
While bRepeat = True
strFilter = ahtAddFilterItem(strFilter, "Database File (*.MDB)", "*.MDB")
strInputFileName = ahtCommonFileOpenSave( _
                Filter:=strFilter, OpenFile:=True, _
                DialogTitle:="Please Select a Backend File...", _
                Flags:=ahtOFN_HIDEREADONLY)
                
If strInputFileName = "" Then
 If MsgBox("You Must Select A Backend. Without It, The System Cannot Run. Are You Sure?", vbYesNo, "Are You Sure?") = vbYes Then
    MsgBox "The Database Will Now Close.", vbCritical, "Warning"
    DoCmd.CloseDatabase
 End If
Else
 bRepeat = False



RelinkTables (strInputFileName)
   
   
End If
Wend
End If
End Sub

It's quite a bit, a lot of that code isn't actually linking the tables, it's just the other coding i've added to warn the user that it doesn't exist, and then ask them to relink it. But i'm sure you'll be able to figure out the bits you need. Let me know if not

 

[The Sky Heart]

thank you sooo much agehoops
and thanks for all the Forum members

 

[agehoops]

No problem, glad I could help