Linking access tables?

[Job Lot]

Is there any way to programmatically link access tables using vb.net

[JohnFol]

What do you mean by Linking as this is an Access term to mean "getting data
from another database application"
"Job Lot" <Jo****@discussions.microsoft.com> wrote in message
news:09**********************************@microsof t.com...

Is there any way to programmatically link access tables using vb.net

[Job Lot]

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?

"JohnFol" wrote:

What do you mean by Linking as this is an Access term to mean "getting data
from another database application"
"Job Lot" <Jo****@discussions.microsoft.com> wrote in message
news:09**********************************@microsof t.com...

Is there any way to programmatically link access tables using vb.net

[JohnFol]

..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


"Job Lot" <Jo****@discussions.microsoft.com> wrote in message
news:FD**********************************@microsof t.com...

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?

"JohnFol" wrote:

What do you mean by Linking as this is an Access term to mean "getting
data
from another database application"
"Job Lot" <Jo****@discussions.microsoft.com> wrote in message
news:09**********************************@microsof t.com...

> Is there any way to programmatically link access tables using vb.net

[Paul Clement]

On Thu, 6 Jan 2005 16:19:01 -0800, "Job Lot" <Jo****@discussions.microsoft.com> wrote:

¤ 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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[Job Lot]

Thanks Paul that worked.

"Paul Clement" wrote:

On Thu, 6 Jan 2005 16:19:01 -0800, "Job Lot" <Jo****@discussions.microsoft.com> wrote:

¤ 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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[Job Lot]

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


"Paul Clement" wrote:

On Thu, 6 Jan 2005 16:19:01 -0800, "Job Lot" <Jo****@discussions.microsoft.com> wrote:

¤ 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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[Job Lot]

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?

"Job Lot" wrote:

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


"Paul Clement" wrote:

On Thu, 6 Jan 2005 16:19:01 -0800, "Job Lot" <Jo****@discussions.microsoft.com> wrote:

¤ 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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[Paul Clement]

On Sun, 9 Jan 2005 18:33:02 -0800, "Job Lot" <Jo****@discussions.microsoft.com> wrote:

¤ 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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[Job Lot]

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?
"Paul Clement" wrote:

On Sun, 9 Jan 2005 18:33:02 -0800, "Job Lot" <Jo****@discussions.microsoft.com> wrote:

¤ 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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

[Paul Clement]

On Mon, 10 Jan 2005 14:13:03 -0800, "Job Lot" <Jo****@discussions.microsoft.com> wrote:

¤ 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 ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)