Access Linked Table Setup using VBA

[pd2004]

hello,

I would like to use VBA to set up the linked tables in my database when it opens. I have searched, and there are related topics, but I think I need a little help. I am doing this because some users have their drives mapped differently, and I need to use the base server instead of the letter of the drive in the linking. The linked table manager won't let me do that, as far as I understand.

Anyway, I have put together this code. I would like the simplest solution possible. I was thinking just delete all linked tables, then set each one up when the database opens. I need to specify the table within the database as well.

Any help is greatly appreciated! Big thanks to all!

Pat





Dim tdf As TableDef


tdf.Connect = ";DATABASE=" & "\\WN2123\myfolder\mydatabase.mdb"

tdf.Name = "Table_1

 

[SkipVought]

forum705

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[MajP]

The linked table manager won't let me do that

Not exactly.
You can do it, but you cannot use the browse feature. You have to type in the UNC and it will work.

 

[jpl458]

Here is code that I use to relink tables. I am assuming that you linked the tables when you created the ap. My tables are linked via ODBC, so the DSN (in Blue) is the name of the odbc connection.

Dim ctl As Control
Dim tdf As DAO.TableDef
For Each tdf In CurrentDb.TableDefs
' check if table is a linked table
If Len(tdf.Connect) > 0 Then
tdf.Connect = "ODBC;DSN=Name;APP=Microsoft Office 2010;DATABASE=DBNAME;Uid=XXXXX;Pwd=XXXXXX;Network=XXXXXX"
tdf.RefreshLink
End If
Next

Hope this helps.

jpl

 

[d0wns3t]

where i work we were just using the standard linked table manager with unc paths. I got particularly annoyed one day at the slowness when i went to deploy a solution that after some troubleshooting I realised we were having issues with 8.3 filenames. Its a very old network, and i think it may be remnants of when they moved onto the current xp/office 2003 platform. It appears that it is making a huge amount of calls to GetShortPathName internally, to account for the observed differences it must be making the call a LARGE number of times (ie not just once per access to the db). Long story short, the technique that i developed for us (with a little help from my team) was that we have no linked tables visible through the database itself. I had played around with checking for available drives to do a new mapping etc, which was a solution but caused a pretty big delay at the start. In the end the method we settled on is all references to currentdb are replaced with backendDB, and bound controls etc have recordsets reapplied as needed. If you normally do all your control data through code anyway, it becomes a breeze to fix. Normally first thing is a find and replace currentdb with backenddb. BackendDB is a module function that will check the global var beDB, and create a connection to the backend db if needed (via unc, no path restrictions etc), and will then return that for use, or return a reference to beDB if connected already. On shutdown of the app you close the connection. It also handles the recommended method of keeping an open connection.

About the only thing that you cant do (well i suspect you can somehow) is use things like docmd.runsql (not necessairly good practice anyway!). As i said, i reckon it could be improved but this is how we have settled on it. Hopefully the next network upgrade should handle it.

With some testing I proved this method was 100 times faster than using the standard linked tables through access. People had always written it off to bad network performance (it is such a vast network it wasnt that hard to believe). Thats with VERY simple queries, it's pretty scary when you start to throw in a few joins etc! There may be some slight tweaks as to how to use this backend db connection (ie you guys are talking connecting to tables, if this is something that affected you you may be able to just make a slight change)

 

[d0wns3t]

sorry forgot to add, to simplify development it's easiest to HAVE the linked tables there, just delete them prior to deployment to get the speed boost, the module can be tweaked about whether it is connecting to the backend, or just the currentdb

 

[combo]

I use ODBC to link to external database. Some time ago both tables and datasource names were changed due to upgrade of the database. I built an external tool (excel, more precisely: VBA in excel) that allowed to change links. The code below require references to ADO and ADOX libraries:

Dim catDB As ADOX.Catalog
Dim conDB As ADODB.Connection
Dim tblLinkOld As ADOX.Table, tblLinkNew As ADOX.Table
Dim sDBFullPathName As String
Dim sTableName As String
Dim sLinkProviderStringOld As String, sLinkProviderStringNew As String
Dim sRemoteTableNameOld As String, sRemoteTableNameNew As String

' assign input database path and table name:
' sDBFullPathName =
' sTableName =
Set catDB = New ADOX.Catalog
Set conDB = New ADODB.Connection
conDB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sDBFullPathName
catDB.ActiveConnection = conDB
catDB.Tables.Refresh
Set tblLinkOld = catDB.Tables(sTableName)

' check link type: PASS-THROUGH for ODBC, LINK for linked access or excel table.
If tblLinkOld.Type = "PASS-THROUGH" Or tblLinkOld.Type = "LINK" Then
    sLinkProviderStringOld = tblLinkOld.Properties("Jet OLEDB:Link Provider String")
    sRemoteTableNameOld = tblLinkOld.Properties("Jet OLEDB:Remote Table Name")
    ' rename old table
    tblLinkOld.Name = sTableName & "_xxx"
    ' add and configure new
    Set tblLinkNew = New ADOX.Table
    With tblLinkNew
        ' assign old name to new table
        .Name = sTableName
        Set .ParentCatalog = catDB
        .Properties("Jet OLEDB:Create Link") = True
        ' build new provider string:
        ' sLinkProviderStringNew =
        .Properties("Jet OLEDB:Link Provider String") = sLinkProviderStringNew
        ' build new remote table name
        ' sRemoteTableNameNew =
        .Properties("Jet OLEDB:Remote Table Name") = sRemoteTableNameNew
    End With
    ' add table to database ...
    catDB.Tables.Append tblLinkNew
    ' ...delete old...
    catDB.Tables.Delete tblLinkOld
    ' ...and refresh
    catDB.Tables.Refresh
Else
    MsgBox sTableName & " is not a linked table"
End If

combo

 

[pd2004]

I wanted to post a thank you for all of the help I have received. I really appreciate it.

Thank you,

Pat