ADOX Linking table

[hwkranger]

I don't know why this doesn't work... I'm getting the error: "ODBC--Connection to '{SQL Server}ServerName' Failed"

I'm trying to add tables to a MSACCESS2k3 database from a remote server, Using ODBC without mapping System DSNs. Any help is appreciated.

Public Function ConnectSQLTable(strServer As String, strDatabase As String, StrServerTable As String, Optional strLocalTableName As String, Optional blnHideTable As Boolean = True)


    Dim tb As New ADOX.Table
    Dim cat As New ADOX.Catalog
    
    Dim t As TableDef
'Make sure the table doesn't exist
If Not FindTable(strLocalTableName) Then
    cat.ActiveConnection = CurrentProject.Connection
    tb.ParentCatalog = cat


    With tb
        .name = strLocalTableName
        .Properties("Temporary Table") = False
        .Properties("Jet OLEDB:Table Hidden In Access") = blnHideTable
        .Properties("Jet OLEDB:Table Hidden In Access") = True
        .Properties("Jet OLEDB:Create Link") = True
        '.Properties("Jet OLEDB:Remote Table Name") = StrServerTable
        .Properties("Jet OLEDB:Link Provider String") = "odbc;driver={SQL Server};server=" & strServer & ";database= " & strDatabase & "Table = " & StrServerTable & ";Trusted_Connection=Yes;Network Library=DBMSSOCN;"
        .Properties("Jet OLEDB:Create Link") = True
    End With

    cat.Tables.Append tb
    cat.Tables.Refresh
    Set cat = Nothing
    Set tb = Nothing
Else
    MsgBox ("This table already exists exist.")
End If
        

End Function

Randall Vollen
Merrill Lynch

 

[hwkranger]

I fixed it... not sure what the problem was, but here it is fixed..

Public Function ConnectSQLTable(strServer As String, strDatabase As String, StrServerTable As String, Optional strLocalTableName As String, Optional blnHideTable As Boolean = True)

'Make sure the table doesn't exist
If Not FindTable(strLocalTableName) Then
    Dim cat As ADOX.Catalog
    Dim tbl As ADOX.Table
    Set cat = New ADOX.Catalog
    Set tbl = New ADOX.Table
    cat.ActiveConnection = CurrentProject.Connection
    
    tbl.ParentCatalog = cat
    tbl.name = strLocalTableName
    tbl.Properties("Temporary Table") = False
    tbl.Properties("Jet OLEDB:Create Link") = True
    'to link a SQL Server and Database use this line
    tbl.Properties("Jet OLEDB:Link Provider String") = "odbc;driver=SQL Server;server=" & strServer & ";database=" & strDatabase & ";Trusted_Connection=Yes;Network Library=DBMSSOCN;"
    
    'To link to another access mdb use this line
    tbl.Properties("Jet OLEDB:Remote Table Name") = "dbo.Master1"
    tbl.Properties("Jet OLEDB:Create Link") = True
    tbl.Properties("Jet OLEDB:Table Hidden In Access") = True
    
    cat.Tables.Append tbl
    
    Set tbl = Nothing
    Set cat = Nothing
Else
    MsgBox ("This table already exists exist.")
End If
        

End Function

Randall Vollen
Merrill Lynch