creating linked tables using ADOX and C#

[Guest]

I'm trying to convert a procedure from an old VB6 app that creates an access
linked table and am having syntax problems with setting the properties. The
code is listed below. Anyone have ideas on how to do this?



private static void CreateLinkedExternalTable(string strTargetDB, string
strProviderString, string strSourceTbl, string strLinkTblName)
{
ADOX.CatalogClass catDB = new ADOX.CatalogClass();
ADOX.Table tblLink = new ADOX.Table();
// Open a Catalog on the database in which to create the link.
catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ strTargetDB;
//Name the new Table and set its ParentCatalog property to the open
Catalog to allow access to the Properties collection.
tblLink.Name = strLinkTblName;
tblLink.ParentCatalog = catDB;
// Set the properties to create the link.
tblLink.Properties("Jet OLEDB:Create Link") = true;
tblLink.Properties("Jet OLEDB:Link Provider String") = strProviderString;
tblLink.Properties("Jet OLEDB:Remote Table Name") = strSourceTbl;

// Append the table to the Tables collection.
catDB.Tables.Append(tblLink);
catDB = null;
}

 

[Cor Ligthert]

dChillman,

You know that your code is not ADONET

Although Bill an Paul answer here as well is your question probably more at
its place in the newsgroup.

microsoft.public.data.ado

Cor

 

[Paul Clement]

¤ I'm trying to convert a procedure from an old VB6 app that creates an access
¤ linked table and am having syntax problems with setting the properties. The
¤ code is listed below. Anyone have ideas on how to do this?
¤
¤
¤
¤ private static void CreateLinkedExternalTable(string strTargetDB, string
¤ strProviderString, string strSourceTbl, string strLinkTblName)
¤ {
¤ ADOX.CatalogClass catDB = new ADOX.CatalogClass();
¤ ADOX.Table tblLink = new ADOX.Table();
¤ // Open a Catalog on the database in which to create the link.
¤ catDB.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
¤ + strTargetDB;
¤ //Name the new Table and set its ParentCatalog property to the open
¤ Catalog to allow access to the Properties collection.
¤ tblLink.Name = strLinkTblName;
¤ tblLink.ParentCatalog = catDB;
¤ // Set the properties to create the link.
¤ tblLink.Properties("Jet OLEDB:Create Link") = true;
¤ tblLink.Properties("Jet OLEDB:Link Provider String") = strProviderString;
¤ tblLink.Properties("Jet OLEDB:Remote Table Name") = strSourceTbl;
¤
¤ // Append the table to the Tables collection.
¤ catDB.Tables.Append(tblLink);
¤ catDB = null;
¤ }


Where are you having problems?


Paul
~~~~
Microsoft MVP (Visual Basic)

 

[Guest]

Thanks for replying. Originally I was having problems with setting the
property values, then I figured out the correct syntax;

tblLink.Properties("Jet OLEDB:Link Provider String").Value =
strProviderString;


The code worked when I linked a dbf file, but when I try to link an access
2000 table it fails with something like "couldn't find installable ISAM". I
can create the linked tables manually, but I need to do it in code. I figure
it has something to do with the provider string, but the correct solution
isn't hitting me in the head yet.

Here is the string I am using:

"Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" + sMainMDB +
";";

where sMainMDB is the full path to the access 2003 database in which I need
the links.

 

[Paul Clement]

¤ Thanks for replying. Originally I was having problems with setting the
¤ property values, then I figured out the correct syntax;
¤
¤ tblLink.Properties("Jet OLEDB:Link Provider String").Value =
¤ strProviderString;
¤
¤
¤ The code worked when I linked a dbf file, but when I try to link an access
¤ 2000 table it fails with something like "couldn't find installable ISAM". I
¤ can create the linked tables manually, but I need to do it in code. I figure
¤ it has something to do with the provider string, but the correct solution
¤ isn't hitting me in the head yet.
¤
¤ Here is the string I am using:
¤
¤ "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Data Source=" + sMainMDB +
¤ ";";
¤
¤ where sMainMDB is the full path to the access 2003 database in which I need
¤ the links.

Yes, the installable ISAM error usually indicates there is a syntax problem with the connection
string. For an Access database the Link Provider String should look something like the following:

MS Access;DATABASE=E:\My Documents\AccessDB.mdb


Paul
~~~~
Microsoft MVP (Visual Basic)