CODEWITHSUNDEEP
vbams-accessrelative-pathms-access-2010
varilight
varilight

Reputation: 53

Microsoft Access 2010 Relative Addressing Problems

After learning that MS Access only allows absolute addressing for its linking of tables and that the only workaround for this problem was throught the use of VBA code I started coding up a way for it to do so. I found a relatively simple code and modified to suit my purpose which you can see below. However this method seems to have 2 main problems.

1 - I can't seem to link Excel Spreedsheets, as the first attempt lead to my whole module corrupting itself. Is there a way to link them as well?

2 - More importantly the size of the file increases each time it is open and the only modification to the database has been the addition of the code within the module. I've made it so it automatically executes upon opening of the file and after closing I've noticed it increases in size by several 100 kbs. Which is disturbing.

Also if there is a better method of doing this I'd be very interested in seeing how its done.

Public Sub RelinkTables(newPathName As String, backEnd As String, excel1 As String, excel2 As String)
Dim Dbs As Database
Dim Tdf As TableDef
Dim Tdfs As TableDefs
Set Dbs = CurrentDb
Set Tdfs = Dbs.TableDefs
'Loop through the tables collection
   For Each Tdf In Tdfs
    If Tdf.SourceTableName <> "" Then 'If the table source is other than a base table
        If Tdf.SourceTableName = "CClas$" Or Tdf.SourceTableName = "Sheet1$" Then

        Else
            Tdf.Connect = ";DATABASE=" & newPathName & backEnd 'Set the new source
            Tdf.RefreshLink 'Refresh the link
        End If
    End If
Next 'Goto next table

End Sub

Function ReLinker()
Dim currPath As String
Dim backEnd As String
Dim excel1 As String
Dim excel2 As String
currPath = CurrentProject.Path
Debug.Print currPath
backEnd = "\backEnd.accdb"
excel1 = "\excel1.xls"
excel2 = "\excel2.xls"

RelinkTables currPath, backEnd, excel1, excel2
End Function

Upvotes: 1

Views: 1012

Answers (1)

HansUp
HansUp

Reputation: 97101

"the size of the file increases each time it is open"

That makes sense. Relinking normally increases the size of your db file. And since you're relinking again every time you open the db, you should expect that size increase. Perform a compact to shrink the db file back down again.

However, I would examine the existing links and only perform the relink if they need changing.

Also, consider verifying that your link file targets are present before proceeding with the relink.

If Len(Dir(currPath & backEnd)) = 0 _
        Or Len(Dir(currPath & excel1)) = 0 _
        Or Len(Dir(currPath & excel2)) = 0 Then
    MsgBox "Oops!"
End If

For the Excel links, see if you can build on any of the following ...

? CurrentDb.TableDefs("tblExcelData").Connect Like "Excel*"
True
? CurrentDb.TableDefs("tblExcelData").Connect
Excel 8.0;HDR=YES;IMEX=2;DATABASE=C:\share\Access\temp.xls
? Split(CurrentDb.TableDefs("tblExcelData").Connect, "DATABASE=")(1)
C:\share\Access\temp.xls

Upvotes: 1

Related Questions