" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi
" Live as if you were to die tomorrow. Learn as if you were to live forever.. "
- Mahatma Gandhi
We may have requirement to display the other database(SQL Server, ) data in our MSAccess database. For this we have the functionality in MS Access to access/view the data from the SQL Server database. We can create Link Table whose source will be the particular table from SQL Server database.
The below example explains how to create a LinkTable in MSAccess by taking the source as the SQL Server table. Also add the Description property value to the table. If the particular definition is not exists then create the property and add the value to it.
Sub CreateLinkTable(ByVal connectionString As String, ByVal tableName As String, ByVal description As String)
Dim db As Database
Dim tdef As TableDef
Set db = CurrentDb
Set tdef = New TableDef
'set the new table name
tdef.Name = tableName
'set the connection string for the SQL Server DB
tdef.Connect = connectionString
'set the source table name from the SQL Server DB
tdef.SourceTableName = tableName
On Error Resume Next
' if no property with name Description exists then it will throw error
tdef.Properties("Description") = description
'If no property named Description exists then create the property and assign a value to it
If Err.Number = 3270 Then
Set prp = tdef.CreateProperty("Description", dbText, description)
tdef.Properties.Append prp
End If
'add the new table definition to the table definition collection
db.TableDefs.Append tdef
End Sub