Linking access tables?

  • Thread starter Thread starter Guest
  • Start date Start date
What do you mean by Linking as this is an Access term to mean "getting data
from another database application"
 
Hi John

I am not talking about relationships. I want to create a link to a table
which belongs to a separate mdb. In access we right click in Tables windows
and use “Link Tables†option to create a link to table which resides in some
other db.

Is it possible to create such link programmatically?
 
..Net assemblies are NOT MDB files, nor does Access use .Net hence why the
comment does not make sense.

To perform the equivalent of a right click in the tables window, open the
Server Explorer, right click Data Connections and add new connection. You
can then navigate to the MDB.

To programatitcally do this look at the ADO.Net documentation and look for
OleDBConnection
 
¤ Is there any way to programmatically link access tables using vb.net

Yes, you can use ADOX (Microsoft ADO Ext 2.x for DDL and Security) via interop. The below example
links to a table in an Access database:

Sub CreateAttachedAccessTableWithADOX()

Dim ADOXTable As New ADOX.Table
Dim ADOXCatalog As New ADOX.Catalog
Dim ADOConnection As New ADODB.Connection

Try

ADOConnection.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=e:\My Documents\db1.mdb;" & _
"Jet OLEDB:Engine Type=4;")

ADOXCatalog.ActiveConnection = ADOConnection

ADOXTable.Name = "Table11Linked"
ADOXTable.ParentCatalog = ADOXCatalog
ADOXTable.Properties("Jet OLEDB:Link Provider String").Value = "MS Access;DATABASE=E:\My
Documents\AccessDB.mdb"
ADOXTable.Properties("Jet OLEDB:Remote Table Name").Value = "Table11"
ADOXTable.Properties("Jet OLEDB:Create Link").Value = True
ADOXCatalog.Tables.Append(ADOXTable)
Catch ex As Exception
MessageBox.Show(ex.Message)
Finally
ADOConnection.Close()
End Try

End Sub


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
One more question

Before creating link, how can I check to see whether or not link already
exists? In my application I get an error if link is already there in the
database.

Thanks
 
I got it working as

For Each table As ADOX.Table In ADOXCatalog.Tables
If table.Name = tableName Then
Exit Sub
End If
Next

While I was iterating through tables collection of ADOX.Catalog, I noticed
there were actually more tables in the collection than displayed in tables
windows of access db. Most of these tables started with TMP. Where did these
tables come from?
 
¤ I got it working as
¤
¤ For Each table As ADOX.Table In ADOXCatalog.Tables
¤ If table.Name = tableName Then
¤ Exit Sub
¤ End If
¤ Next
¤
¤ While I was iterating through tables collection of ADOX.Catalog, I noticed
¤ there were actually more tables in the collection than displayed in tables
¤ windows of access db. Most of these tables started with TMP. Where did these
¤ tables come from?
¤

Don't think I've seen those before but it sounds like they're internal temporary tables. You can
probably identify them by checking the Type property of the ADOX Table object.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
thanks paul. another question though :-)

I have a friend who is trying to create access db in asp.net, he do asp.net
in a text editor as he doesn’t have vs. How can he add references to
Microsoft ADO Ext 2.7 for DDL and Security? Is there an “Import Namespace=â€
equivalent?
 
¤ thanks paul. another question though :-)
¤
¤ I have a friend who is trying to create access db in asp.net, he do asp.net
¤ in a text editor as he doesn’t have vs. How can he add references to
¤ Microsoft ADO Ext 2.7 for DDL and Security? Is there an “Import Namespace=”
¤ equivalent?
¤

You have to use late binding. See the following:

http://aspnet101.com/aspnet101/tutorials.aspx?id=27


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top