Linking an External Table through ADO - Not working!

[sportsguy]

code Fails at appending new table to catalog,
i am missing something, don'tknow what though,

thanks in advance,
sportsguy

Private Const strProvider As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source="

Private Sub ADOLinkProjMaster_DblClick(Cancel As Integer)

Dim cnn As ADODB.Connection
Set cnn = New ADODB.Connection

    cnn.Open strProvider & "F:\PROJMASTER.mdb"
    Debug.Print cnn.ConnectionString
  
Dim cat As ADOX.Catalog
Set cat = New ADOX.Catalog

Dim tblLink As ADOX.Table
Set tblLink = New ADOX.Table
   
   ' Open the catalog.
   cat.ActiveConnection = cnn

   
   With tblLink
      ' Name the new Table and set its ParentCatalog property to the
      ' open Catalog to allow access to the Properties collection.
      .Name = "PROJMSTR"
    Set .ParentCatalog = cat
      ' Set the properties to create the link.
      .Properties("Jet OLEDB:Create Link") = True
      .Properties("Jet OLEDB:Link Datasource") = strProvider & "F:\PROJMASTER.mdb"
      .Properties("Jet OLEDB:Remote Table Name") = "PROJMASTER"
   End With

   ' Append the table to the Tables collection.
   cat.Tables.Append tblLink  


   Set cat = Nothing
 
  End Sub

what am i missing??

oh, and i shouldn't see the table in the db container either, correct?

 

[Pat Hartman]

oh, and i shouldn't see the table in the db container either, correct?

Why wouldn't you? The tables container shows linked tables as well as local tabkes.

I don't know what is wrong with the code. I would use ONE line of code IF I wanted to do this. Look up the TransaferDatabase Method/Action.

Linking tables on-the-fly is less efficient than leaving them linked permanently. Each time the table is linked, Jet needs to have a "conversation" with the datasource to determine the structure of the table and what features the data provider supports.

 

[sportsguy]

But there is a reason to my madness

i realize this, except that i don't want the tables linked permanently at all, instead of having 60 sixisgma black belts from all over the country link into one database for period end reporting at the same time, i have them load into their own identical, local tables, and they can examine the output in several ways, then if they are satisfied with the answer, i have coded the append sequence so that the backend loads all at once over a frame relay network, which isn't the fastest network around.

I want to do it in ADO as a learning step. I can certainly do it using DoCmd, but I also read in F. Scott Barker's book that an ADO linked table will not show up in the database container, and if that is correct, then a curious blackbelt won't do something stupid.

sportsguy

 

[sportsguy]

Thanks PAT

The answer is in my software version, I uploaded the Jet 4.0 latest patches, and the latest MDAC 2.8, and now the code works, not sure about the poster, but the code works.

thanks Pat

sportsguy