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:

Expand|Select|Wrap|Line Numbers

 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

Quote:

Originally Posted by Dan2kx

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.

Quote:

Originally Posted by Dan2kx

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.

Quote:

Originally Posted by Dan2kx

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

Quote:

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

Quote:

Originally Posted by 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.

Hello NeoPa, could you elaborate for me??

NeoPa

Quote:

Originally Posted by Dan2kx

Hello NeoPa, could you elaborate for me??

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

Dan2kx

Quote:

Originally Posted by NeoPa

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

Quote:

Originally Posted by Dan2kx

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?

Expand|Select|Wrap|Line Numbers

 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

Quote:

Originally Posted by 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.

Expand|Select|Wrap|Line Numbers

 '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

Link Tables in VB

re: Link Tables in VB

re: Link Tables in VB

re: Link Tables in VB

re: Link Tables in VB

re: Link Tables in VB

re: Link Tables in VB

re: Link Tables in VB

re: Link Tables in VB

re: Link Tables in VB

re: Link Tables in VB

re: Link Tables in VB

re: Link Tables in VB

re: Link Tables in VB

re: Link Tables in VB

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

/bytes/about