Relink Tables 1

[FancyPrairie]

I'm trying to create a routine that will relink my tables from a Live database to a Test database and visa-versa. My code works if both databases are SQL databases. Or if both databases are Access databases. However, I can't get it to work if, for example, the Live database is a SQL database and the Test databases is an Access database.

Any ideas?

In this example, I'm attempting to relink from a SQL database to a Access database. Note that prior to executing this code, the Jet OLEDB:Link Provider String contains the connection string to the SQL database and Jet OLEDB:Link Datasource is blank

tbl.Properties("Jet OLEDB:Link Provider String") = ""
tbl.Properties("Jet OLEDB:Link Datasource") = "\\path\BEdatabase.mdb"

I don't receive any errors but the connection is not made.

 

[jsteph]

I do the below in a loop of a local table containing the tablenames to relink, i've just used harcoded variables for clarity here..

    strN = "Your table name"
    strCN = "Your connection string"
    docmd.deleteobject acTable, strN
    Set td = db.CreateTableDef(strN)
    td.Connect = strCn
    td.Attributes = 131072 'dbAttachSavePWD
    td.SourceTableName = strN 'if it's same as on the server
    db.TableDefs.Append td

--Jim

 

[FancyPrairie]

Deleting the table and linking it again will work. I was hoping there was a way I could just relink (I'm using ADOX to currently do the relink).

This is how it looks using ADOX

    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table

    Set cat = New ADOX.Catalog
    cat.ActiveConnection = CurrentProject.Connection

    For Each tbl In cat.Tables

        If (the tbl is a SQL table) then
           tbl.Properties("Jet OLEDB:Link Provider String") = [MySQLConnectionString]
           tbl.Properties("Jet OLEDB:Link Datasource") = vbNullString
        Else
           tbl.Properties("Jet OLEDB:Link Provider String") = vbNullString
           tbl.Properties("Jet OLEDB:Link Datasource") = "\\path\BEdatabase.mdb"
        End If
    Next

This works if both the Test and Live databases are both SQL databases or both Access databases. But, again, if one is a SQL database and the other an Access database, I can't seem to get it to work.

 

[jsteph]

I rarely use ADO in Access (only in VB) so I can't add much.

But do I recall that it was cleaner to delete the tabledef (at least with DAO) because of some other properties that hung around if just the .connect was changed. I can say that from a performance standpoint deleting/appending the tabledef is very fast, if that's your concern against the delete/reappend method.
--Jim

 

[FancyPrairie]

I'll give it a try. Thanks