Dynamic Linking of sql tables to Access

[venkiatmarut]

Hi:hi: ,

Is it possible to link the tables through VBA code when the program executing or say when a button is pressed. I don't have MS Access in the system so I want to do it through VBA.

If it is possible, please share some code to do that?
If it is possible in other languages i.e. VB, .Net also would helpful.

[CreganTur]

Is it possible to link the tables through VBA code when the program executing or say when a button is pressed.
It is very possible to create linked tables via VBA programming. Here's some example code that uses an ADO (ActiveX Data Objects) connection to create a linked table from an external DB. Now, you need to be aware that when you link to an external table that link will exist until you delete it. Once you link to an external source that link will remain unless you delete it.
Dim cat As ADOX.Catalog
Dim linkTbl As ADOX.Table
Dim strDB As String
Dim strTable As String
On Error GoTo ErrorHandler
strDB = CurrentProject.Path & "\Northwind.mdb"
strTable = "Customers"
Set cat = New ADOX.Catalog
cat.ActiveConnection = CurrentProject.Connection
Set linkTbl = New ADOX.Table
With linkTbl
'name the new table and set its ParentCatalog property to the
'open catalog to allow access to the properties collection
.Name = strTable
Set .ParentCatalog = cat

'set the properties to create the link
.Properties("Jet OLEDB:Create Link") = True
.Properties("Jet OLEDB:Link Datasource") = strDB
.Properties("Jet OLEDB:Remote Table Name") = strTable
End With
'append the table to the tables collection
cat.Tables.Append linkTbl
Set cat = Nothing
MsgBox "The current database containes a linked table named " & strTable
Exit Sub
ErrorHandler:
MsgBox Err.Number & ": " & Err.Description


I don't have MS Access in the system so I want to do it through VBA.
That doesn't make any sense. If you don't have Access then how are you going to create a linked table in Access?

[venkiatmarut]

That doesn't make any sense. If you don't have Access then how are you going to create a linked table in Access?

Yes you are absolutely right.

But here what I mean is, at first time, we will link the table in development machine. But once we dispatch it to users, we can not go and change manually, we need to do it dynamically, if any change in database name or server name.

That's why I asked this question.

Thanks for your code sharing. But I have got another doubt. ADOX means what and is there any reference needs to be added except
Microsoft Activex Data objects 2.8 library

I tried check it but I didn't get the ADOX class.

Please let me know.

[CreganTur]

Sorry about that. You need a reference to Microsoft ADO Ext. 2.8 for DDL and Security- that should bring up ADOX.

[venkiatmarut]

Thanks alot Randy. I will try this code.