Programatically linking to tables 1

[Kalisto]

Access 2003

Ive got a databse that has been split into a front end (fe) and a back end (be)

This all works fine, as I positioned the back end on the server share I wanted, and then linked the tables into my front end.

However, the product has now been sold to a third party, who may have a different network set up.

To the Front End, Ive added a table called linkTablesURL that has the name of the table, and then a path to that table.

Now, when the front end is run up, I need to look at the table in the front end, and remap the links to the tables in the back end.

However, any of the code samples Ive tried dont work.

Can anyone point me to a useful resource, or give me some sample code so that I can set this to work correctly?

(The alternative is Im sent to the other end of the country to physically map the tables which is a waste of time !)

Cheers

K

 

[Remou]

First, create a table of tables to be linked. Its safer. Here are a few notes.

Sub RelinkTables(Optional strConnect As String = "")
Dim db As DAO.Database
Dim RS As DAO.Recordset
Dim strSQL
Dim tdf As DAO.TableDef


On Error GoTo TrapError

    Set db = CurrentDb
    
    If strConnect = "" Then
        'txtNewDataDirectory= textbox with relevant folder
        strConnect = "MS Access;PWD=SomePassword;DATABASE=" & Me.txtNewDataDirectory
    End If
    
    Set RS = CurrentDb.OpenRecordset("Select TableName From sysTables " _
    & "WHERE TableType = 'LINK'")
    
    Do While Not RS.EOF
        'If the table is not linked, create a link
        If IsNull(DLookup("[Name]", "MSysObjects", "[Name]='" & RS!tablename & "'")) Then
            Set tdf = db.CreateTableDef(RS!tablename, dbAttachSavePWD And dbHiddenObject, _
                RS!tablename, strConnect)
            db.TableDefs.Append tdf
        Else
            'If the table is linked, refer to the new folder
            db.TableDefs(RS!tablename).Connect = strConnect
        End If
        db.TableDefs(RS!tablename).RefreshLink
        RS.MoveNext
    Loop
     
    Set db = Nothing
    RS.Close
    Set RS = Nothing

End Sub

 

[Kalisto]

Cheers, I'll play with that in a bit. Where would you call that, in a module called from the autoexec macro?

 

[Remou]

I generally use a startup form that checks a few things and then opens the menu, if everything is ok.

 

[Kalisto]

Cheers, that Kind of does what I need, however, it looks like you are doing the startup check each time the database is loaded.

I Have 2 issues now.

1) The code objects to 2 of the table names in RS!tablename. These are Defaults and Recommendations. All teh other tables are fine, any idea why it objects to these 2 tables? (If it was just defaults I could argue a case for it being a key word, but not recommendations.

2) Once the form has made all the mappings (if it needs to) is there a way of automatically closing the startup form, or do I have to have a "Setup Complete, press ok to continue" button?

And if my project currently has an autoexec macro, Im guessing I just need to rename it, and trigger it on the table mappings being complete?

Cheers again

K

 

[Remou]

1. In what way does it object to the table names? Are there any error codes? Have you tried manually linking the two problem tables?

2. You can add:

[tt]DoCmd.Close acForm, Me.Name[/tt]

Do not forget that the above is not completed code, for example, there is no error handling.

 

[Kalisto]

I dont have the code in front of me now to give you the exact message, but basically those table names are null in the collection.
(it fails on)

 If IsNull(DLookup("[Name]", "MSysObjects", "[Name]='" & RS!tablename & "'")) Then
            Set tdf = db.CreateTableDef(RS!tablename, dbAttachSavePWD And dbHiddenObject, _
                RS!tablename, strConnect)

If I manually add them then they are there, so its got to be something stupid thats stopping them. At present they are removed from my list of tables to link, but I cant keep it like that.

and cheers, Ive added in some error handling, though it does need tidying up (I want to get the system working properly for a genuine case before I start to look at what can go wrong :-/

K

 

[Remou]

I should have taken out a bit, try this:

[tt]Set tdf = db.CreateTableDef(RS!tablename, dbAttachSavePWD, _
RS!tablename, strConnect)[/tt]

If that does not work, try and get the error codes.

 

[dpimental]

Thanks Remou. I will take a look.

I might put the table list in an array, rather than another table. I'll see which is more optimum to me. Thanks again.

David Pimental
(US, Oh)