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]

firstly i got a error 3170 at line #25 of your posted code.

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

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?

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.

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)?

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]

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.

Hello NeoPa, could you elaborate for me??

[NeoPa]

Hello NeoPa, could you elaborate for me??

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

[Dan2kx]

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

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

[NeoPa]

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

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