Link Tables in VB

[Dan2kx]

Hi peeps,

i have a current need to change the linked tables in my latest access project, and want to do it using VB. normally i like most people use the linked tables manager.

What i am designing is a stock database to be used on multiple sites, and want to have a BE for each site (to reduce WAN lag with one massive DB), however i would like the option to re-link (in rare circumstances), i have found ADOX? linking code previously but couldnt seem to get it to work (so i left it alone) now i think it would be more useful.

I basically want to mirror the link tables manager functio in VB

Any help would be much appreciated.

Dan

[ajalwaysus]

Here are 2 functions I use to link to external tables and files:

Public Function LinkMain(strFileName As String)
Dim strConnection As String
Dim sourceTable As String
Dim TableAlias As String
 
strConnection = "DATABASE=C:\Temp\Access\MY_MDB_be.mdb;TABLE=" & strFileName
 
sourceTable = strFileName 'Access Table Name
 
TableAlias = "MY_TABLE_ALIAS"
 
LinkExternal strConnection, sourceTable, TableAlias
 
End Function
 
Function LinkExternal(ByVal conString As String, sourceTable As String, TableAlias As String)
Dim db As Database
Dim linktbldef As TableDef, rst As Recordset
 
Set db = CurrentDb
Set linktbldef = db.CreateTableDef("tmptable") 'create temporary table definition
 
linktbldef.Connect = conString 'set the connection string
linktbldef.SourceTableName = sourceTable 'attach the source table
db.TableDefs.Append linktbldef 'add the table definition to the group
db.TableDefs.Refresh 'refresh the tabledefinitions
 
linktbldef.Name = TableAlias 'rename the tmptable to original source table name
 
db.Close
Set rst = Nothing
Set linktbldef = Nothing
Set db = Nothing
 
End Function

I don't know how much you may pick up from this but this can get you started. Let me know if/when you have any questions that I can clarify for you.

-AJ

[Dan2kx]

HI,
Thanks for the response, firstly i got a error 3170 at line #25 of your posted code.

secondly is the strFileName the name of the table? it looks like it should be to me? is there a need for the table alias if this is true?

and i am unsure of the need for a tmptable? can you not just name it straight off? does it cause a problem if the tables are named the same (in the different dbs)?

Thanks

[ajalwaysus]

@Dan2kx
You will need to give me the error text, I don't have the time to look them up.

@Dan2kx
Yes strFileName is the name of the table/file, the table alias is not required but I leave it in there if the need for an alias arises, which in my case has.

@Dan2kx
You can do with the code what you wish, I won't take credit for creating this code, and I don't know what would happen because if it ain't broke, I won't fix it. Also, I am pretty sure you cannot overwrite tables with the same name using this code, so you will need to drop the table before you "relink".

-AJ

[Dan2kx]

OK, just making sure/trying to understand the code properly

Could not find installable ISAM. (Error 3170)
The DLL for an installable ISAM file could not be found. This file is required for linking external tables (other than ODBC or Microsoft Jet database tables). The locations for all ISAM drivers are maintained in the Microsoft® Windows® Registry. These entries are created automatically when you install your application. If you change the location of these drivers, you need to correct your application Setup program to reflect this change and make the correct entries in the Registry.

Possible causes:

An entry in the Registry is not valid. For example, this error occurs if you are using a Paradox external database, and the Paradox entry points to a nonexistent directory or driver. Exit the application, correct the Windows Registry, and try the operation again.
One of the entries in the Registry points to a network drive and that network is not connected. Make sure the network is available, and then try the operation again.

[ajalwaysus]

Absolutely, I have no problem answering your questions.I could have posted more info with the code, but I feel we all learn better figuring it out on our own with some help and not being spoon fed the answers to everything. I found this code online and I think I learned more by having to figure out how to adapt it myself. =)

What version of Access are you using, and please post your code as you have adjusted it.

-AJ

[Dan2kx]

Found what i was looking for http://support.microsoft.com/kb/209841
this is very similar to your example exception being the source table name,
and it works.

Took me a long time to find it though

Thanks for the help, problem solved

Dan, Out

[NeoPa]

TableDefs in Access have a property .Connection and a method .RefreshLink. First change the .Connection property to the new value required, then call the .RefreshLink method.

[Dan2kx]

@NeoPa
Hello NeoPa, could you elaborate for me??

[NeoPa]

@Dan2kx
Not in a vacuum Dan. What do you need clarified?

[Dan2kx]

@NeoPa
Sorry NeoPa i dont know what to say, im just not sure what you mean?

[NeoPa]

@Dan2kx
That's a fair enough response Dan.

I think that what I have posted covers the whole subject completely. It's pretty well the whole shebang. If you need more then I need to understand what you need. I don't have time to put together an article covering everything remotely related to the subject, so I need to understand what you need clarification on.

[Dan2kx]

So to re-map a linked table you would use something like this?

TableDef.Connection = "Database=C:\Blah......" 'Path to file
Tabdledef.RefreshLink

[NeoPa]

That's about the size of it yes.

In case it helps, I will also include some code which remaps a linked table safely. There are various reasons why a relink will fail, if it does then the connection should revert to the value that actually matches the link. Give me a while to knock it up.

[NeoPa]

NeoPa: TableDefs in Access have a property .Connection and a method .RefreshLink. First change the .Connection property to the new value required, then call the .RefreshLink method.

This code is not compiled or tested so let me know if there are any problems with it, but it should allow you to relink any of your Access linked tables to a new, specified destination database.

'ReLink() Updates the links of a table to point to the specified database.
Public Sub ReLink(ByVal strTable As String, ByVal strDBName As String)
    Dim intParam As Integer
    Dim strMsg As String, strOldLink As String
    Dim varLinkAry As Variant
 
    On Error GoTo Error_RL
    'Test that the table actually exists first.
    With CurrentDB.TableDefs(strTable)
        If .Attributes And dbAttachedTable Then
            varLinkAry = Split(.Connect, ";")
            For intParam = LBound(varLinkAry) To UBound(varLinkAry)
                If Left(varLinkAry(intParam), 9) = "DATABASE=" Then Exit For
            Next intParam
            strOldLink = Mid(varLinkAry(intParam), 10)
            If strOldLink <> strDBName Then
                varLinkAry(intParam) = "DATABASE=" & strDBName
                .Connect = Join(varLinkAry, ";")
                Call .RefreshLink
                strMsg = "[%T] relinked to ""%F"""
                strMsg = Replace(strMsg, "%T", strTable)
                strMsg = Replace(strMsg, "%F", strDBName)
                Debug.Print strMsg
            End If
        End If
    End With
    Exit Sub
 
Error_RL:
    Select Case Err.Number
    Case 3265
        MsgBox = Replace("Table (%T) not found in database", "%T", .strTable)
    Case 3011, 3024, 3044, 3055, 7874
        varLinkAry(intParam) = "DATABASE=" & strOldLink
        .Connect = Join(varLinkAry, ";")
        strMsg = "Database file (%F) not found.%L" & _
                 "Unable to ReLink [%T]."
        strMsg = Replace(strMsg, "%F", strNewLink)
        strMsg = Replace(strMsg, "%L", vbCrLf)
        strMsg = Replace(strMsg, "%T", strTable)
    End Select
    Call MsgBox(Prompt:=strMsg, _
                Buttons:=vbExclamation Or vbOKOnly, _
                Title:="ReLink")
End Sub

[Dan2kx]

NeoPa (or anyone else), i need your help with this again,
I had it working before but want to use it in a new database here is my code:

Function TableReMap(tbl As String, Path As String)
Dim DB As DAO.Database, tblDef As DAO.TableDef
    Set DB = CurrentDb
    Set tblDef = DB.CreateTableDef(tbl)
    tblDef.Connect = ";Database=" & Path
    tblDef.RefreshLink

It errors at the refreshlink line and says "invalid operation" (3219)

Any ideas? (all the variables are set correctly (AFAIK)

Dan

[Dan2kx]

False alarm, i found my original code

Function TableReMap(tbl As String, Path As String)
Dim tdf As DAO.TableDef, strPath As String
    strPath = ";Database=" & Path
    For Each tdf In CurrentDb.TableDefs
        If tdf.Name = tbl Then
            If strPath <> tdf.Connect Then
                tdf.Connect = strPath
                tdf.RefreshLink
            End If
        End If
    Next
End Function

not quite sure how that differs (apart from the loop) but that access for ya huh (or just me i dont know)

Dan

[NeoPa]

Your code for post #16 is actually trying to create a new TableDef rather than amend an existing one (See line #4 particularly).

[FishVal]

@NeoPa
And TableDef object returned by DAO.Database.CreateTableDef method has to be appended to TableDefs collection via TableDefs.Append method to become an existing Access table.

[Dan2kx]

Ok thanks fellas, i did try some other fiddles before i got to that code but they didnt seem to work either.

the trouble i had was trying to set a string value as a tabledef eg

set tbl = "TableName"

how would you do that (for future reference)?

[NeoPa]

If you look at post #15 then line #9 illustrates this (as a With version rather than a Set mind). For that you would use something like :

Set tbl = CurrentDB.TableDefs("TableName")