Relink backend tables on current database using VBA

Relink Access tables from code

This article is about to create a link to tables of another database. Microsoft Access has many built in function to do this task. But sometime we need to accomplish this task in programmatic way. Using this VBA function we are able to link with another database. In this article we are create an attached table in the current database from a table in a different MDB file.

To implement this function firstly we have to create the form with following controls as shown in Fig 1.1.

Relink Access tables using VBA code. Fig-1.1

Fig-1.1

On button click we have to implement the function. And after implementation we have to write the path of other database and the table of that database. As we press the linked button, table is link with current database as shown in fig 1.2.

Relink Access tables using VBA code. Fig-1.2

Fig-1.2

VBA code

Option Compare Database
Private Sub Command0_Click()
If IsNull(Me.txtdbname) Then
MsgBox "Enter Data Base Path"
Exit Sub
End If
If IsNull(Me.txttblname) Then
MsgBox "Enter Table Name"
Exit Sub
End If
If (createAttached(Me!txttblname, Me!txtdbname, Me!txttblname)) Then
MsgBox ("Table is created")
End If
End Sub
Function createAttached(strTable As String, strPath As String, strBaseTable As String) As Boolean
On Error GoTo CreateAttachedError
Dim tdf As TableDef, strConnect As String, fRetval As Boolean, myDB As Database
DoCmd.SetWarnings False
Set myDB = CurrentDb
Set tdf = myDB.CreateTableDef(strTable)
With tdf
.Connect = ";DATABASE=" & strPath
.SourceTableName = strBaseTable
End With
myDB.TableDefs.Append tdf
fRetval = True
DoCmd.SetWarnings True
CreateAttachedExit:
createAttached = fRetval
Exit Function
CreateAttachedError:
If Err = 3110 Then
Resume CreateAttachedExit
Else
If Err = 3011 Then
Resume Next
End If
End If
End Function


DISCLAIMER

It is advised that the information provided in the article should not be used for any kind formal or production programming purposes as content of the article may not be complete or well tested. Access Guru will not be responsible for any kind of damage (monetary, time, personal or any other type) which may take place because of the usage of the content in the article.