How to add ODBC linked table in an Access database

[chilling]

MS Access, VB6, ODBC

Is it possible, under program control, to dynamically add a ODBC Linked table into access?

ie I want to create a tempory table in Access which is looking at another database using the ODBC interface

[Hack]

Try this

[chilling]

I am trying to use ADOX to dynamically create a "LINKED" ODBC table in an Access database back to DSN table but am not sure what the ODBC syntax is for the connections string. This is the code ....

cnnToLocalAccessMDB = = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\.....\LocalMDB.mdb"
strLocalTableName = "tblStock"
strRemotePathFileMDB = "Provider=MSDASQL.1;Password=tx;Persist Security Info=True;User ID=txdemo;Extended Properties='DSN=SageLine50 Demo;UID=txdemo;PWD=tx;'"
strRemoteTableName = "STOCK"

Dim catLocal As ADOX.Catalog
Set catLocal = New ADOX.Catalog
catLocal.ActiveConnection = cnnToLocalAccessMDB

Set tblRemote = New ADOX.Table
With tblRemote
Set .ParentCatalog = catLocal
.Name = strLocalTableName
.Properties.Item("Jet OLEDB:Create Link").Value = True
.Properties.Item("Jet OLEDB:Link Datasource").Value = strRemotePathFileMDB
.Properties.Item("Jet OLEDB:Remote Table Name").Value = strRemoteTableName
End With

catLocal.Tables.Append tblRemote

This is the Error that occures on the line catLocal.Tables.Append tblRemote :

Could not find file
'C:\...\Provider=MSDASQL.1;Password=tx;Persist Security Info=True;User ID=txdemo;Extended Properties='DSN=SageLine50 Demo;UID=txdemo;PWD=tx;'

It appears as though it is not finding the correct Sage Database from the DSN although if I test the DSN it all appears to be working correctly.

Anyone with any thoughts on this one?

[chilling]

For those who might need this functionality .... my two errors were

• I should have used Jet OLEDB:Link Provider String rather than Jet OLEDB:Link Datasource
• The connection string only needs to "ODBC;DSN=SageLine50 Demo;UID=txdemo;PWD=tx;Persist Security Info=True;"

... but I still have not managed to stop the ODBC link binging up its own UI requesting a username and password even though I am passing one ops: ... if anyone has any ideas on that one ...?

[chilling]

I have now managed (with lots of help from you guys )to get all that working with a new LINKED table being created in Access but I now need to flag one of the fields as the Primary Key. This is the code (it creates the table but not the index) ...

Function CreateACCLinkTableInQW(ByVal lcnnQW As String, ByVal lstrQWTable As String, ByVal lcnnACC As String, ByVal lstrACCTable As String) As Boolean
 
Dim catLocal As ADOX.Catalog
Dim tblLinked As ADOX.Table
Dim inxLinked As New ADOX.Index
Dim lintTable As Integer
 
' Create a connection to QuoteWerks
Set catLocal = New ADOX.Catalog
catLocal.ActiveConnection = lcnnQW
 
 
For lintTable = 0 To catLocal.Tables.Count - 1
    If CStr(catLocal.Tables.Item(lintTable).Name) = lstrQWTable Then
        lblnTable = True
        CreateACCLinkTableInQW = True
        Exit For
    End If
Next ' Table
'If the table does not exist create it
If Not lblnTable Then
    
    Set tblLinked = New ADOX.Table
    With tblLinked
        Set .ParentCatalog = catLocal
        .Name = lstrQWTable
        .Properties.Item("Jet OLEDB:Create Link").Value = True
        .Properties.Item("Jet OLEDB:Link Provider String").Value = lcnnACC
        .Properties.Item("Jet OLEDB:Remote Table Name").Value = lstrACCTable
    End With
    
    ' Add the primary key index
    With inxLinked
        .Name = "inxStock_Code"
        .Columns.Append "STOCK_CODE"
        .Columns("STOCK_CODE").SortOrder = adSortAscending
        .PrimaryKey = True
        .Unique = True
    End With
    tblLinked.Indexes.Append inxLinked
    Set inxLinked = Nothing
 
    catLocal.Tables.Append tblLinked
    Set tblLinked = Nothing
    CreateACCLinkTableInQW = True
End If
 
End Function

The following error appears on the line catLocal.Tables.Append tblLinked:

Invalid arguement

any ideas ?

[chilling]

Also there must be a simplier way to replace this bit of ugly code :

For lintTable = 0 To catLocal.Tables.Count - 1
    If CStr(catLocal.Tables.Item(lintTable).Name) = lstrQWTable Then
        lblnTable = True
        CreateACCLinkTableInQW = True
        Exit For
    End If
Next ' Table

ie test for the existance of a table without having to loop arround all the tables.

[chilling]

I am confused about Keys and Indexes in MS Access and especially LINKED tables.

I understand to use a LINKED table in Access in JOIN to update another table the table has to have a Primary Key (KP). Does that mean that I have to use the tblLinked.Indexes.Append inxLinked or the tblLinked.Keys.Append keyLinked command?

In either case I am confused with the syntax...

Dim keyRemote As New ADOX.Key
    With keyLinked
        .Name = "keySTOCK_CODE"
        .Type = adKeyPrimary
        .Columns.Append "STOCK_CODE"
    End With
    tblLinked.Keys.Append keyLinked
    tblLinked.Keys.Refresh
    catLocal.Tables("SageStock").Keys.Append keyLinked
    Set keyRemote = Nothing

What I am confused about is should I be creating the table first and then adding the Keys and/or Indexes or should the Keys and Indexes be added to the tblLinked object before the table is Appended to the Catalog. It does not appear to matter with a normal Access table but I can not get it to work either way for a LINKED table.

I orginally made the assumption that it had to be before add ing the tblLinked object to the Catalog, as this appears to be the case within Access. (ie once you have linked a table you can not add the Primary Key you have to do that at the time of adding the table).

Anyone know or have any view on any of the above?