Link tables to SQL via odbc using VB (1 Viewer)

[Epic]

Hi guys,
Could anyone give me a code to create linked table to SQL server using ODBC Connection string?

Thanks.

 

[Pat Hartman]

Link the table manually. Unhide the system tables if they are hidden. Open the MSysObjects table and find the linked table so you can copy the connection string.

Then use the TransferDatabase method to link to the table.

FYI, linking tables "on the fly" adds substantial overhead since there is a lot of "hand shaking" that goes on between Jet and the RDBMS during the linking process. It is more efficient to link them and leave them linked.

 

[Epic]

Thank you Pat!

One more: How can I check the table "type" in vb? If is local or linked...

Thanks.

 

[Pat Hartman]

Here's a query that will return all tables in your db. You should be able to modify it to suit your purposes:

SELECT MSysObjects.Name, MSysObjects.Type
FROM MSysObjects
WHERE (((MSysObjects.Type)=1 Or (MSysObjects.Type)=4 Or (MSysObjects.Type)=6));

See this Thread for a list of all of the object types that I have been able to identify.

Type = 1 are local. Type = 4 or 6 are linked.