Linking to SAGE from Access programatically

I am struggling to create a linked table in Access.  Here is the code:

 

    Sub LinkTable(ByVal strTable As String, ByVal strUID As String, ByVal strPWD As String)        Dim cat As Object, tbl As Object        Set cat = CreateObject("ADOX.Catalog")        cat.activeconnection = CurrentProject.Connection        Set tbl = CreateObject("ADOX.Table")        With tbl            Set .parentcatalog = cat            .Name = strTable            .Properties("Jet OLEDB:Remote Table Name").Value = strTable            .Properties("Jet OLEDB:Create Link").Value = True            .Properties("Jet OLEDB:Link Provider String").Value = "ODBC;DSN=SAGE;UID=" & strUID & ";PWD=" & strPWD & ";"            .Properties("Jet OLEDB:Cache Link Name/Password").Value = True        End With        cat.tables.Append tbl        Set idx = Nothing        Set tbl = Nothing        Set cat = Nothing    End Sub I get a "Access database engine could not find object ''.  Make sure the object exists" error - any ideas,    Have I missed something out of the connection string - I do not think so as I have opened the connection successfully elsewhere. Regards, Jason

 

Comments

Linking to SAGE from Access programatically

jason.raikes | | Permalink

I see I am also having a problem with posting to the site.  The post should have read like this:

 

I am struggling to create a linked table in Access.  Here is the code:       Sub LinkTable(ByVal strTable As String, ByVal strUID As String, ByVal strPWD As String)        Dim cat As Object, tbl As Object        Set cat = CreateObject("ADOX.Catalog")        cat.activeconnection = CurrentProject.Connection        Set tbl = CreateObject("ADOX.Table")        With tbl                        Set .parentcatalog = cat            .Name = strTable            .Properties("Jet OLEDB:Remote Table Name").Value = strTable            .Properties("Jet OLEDB:Create Link").Value = True            .Properties("Jet OLEDB:Link Provider String").Value = "ODBC;DSN=SAGE;UID=" & strUID & ";PWD=" & strPWD & ";"            .Properties("Jet OLEDB:Cache Link Name/Password").Value = True        End With        cat.tables.Append tbl        Set tbl = Nothing        Set cat = Nothing    End Sub  I get a "Access database engine could not find object ''.  Make sure the object exists" error - any ideas,    Have I missed something out of the connection string - I do not think so as I have opened the connection successfully elsewhere. Regards, Jason

Sage & Access

johndon68 | | Permalink

I've taken your code as is just changing your variables for hard coded values and the DSN as below and it works fine for me with Access 2007 & Sage 50 v2012:

 

Dim cat As Object, tbl As Object
Set cat = CreateObject("ADOX.Catalog")
cat.activeconnection = CurrentProject.Connection
Set tbl = CreateObject("ADOX.Table")
With tbl
Set .parentcatalog = cat
.Name = "SALES_LEDGER"
.Properties("Jet OLEDB:Remote Table Name").Value = "SALES_LEDGER"
.Properties("Jet OLEDB:Create Link").Value = True
.Properties("Jet OLEDB:Link Provider String").Value = "ODBC;DSN=SageLine50v18;UID=" & "MANAGER" & ";PWD=" & "" & ";"
.Properties("Jet OLEDB:Cache Link Name/Password").Value = True
End With
cat.tables.Append tbl
Set idx = Nothing
Set tbl = Nothing
Set cat = Nothing

 

John

Third time lucky?

jason.raikes | | Permalink

Third time lucky?

I am struggling to create a linked table in Access. Here is the code:

Sub LinkTable(ByVal strTable As String, ByVal strUID As String, ByVal strPWD As String)
Dim cat As Object, tbl As Object
Set cat = CreateObject("ADOX.Catalog")
cat.activeconnection = CurrentProject.Connection
Set tbl = CreateObject("ADOX.Table")
With tbl
Set .parentcatalog = cat
.Name = strTable
.Properties("Jet OLEDB:Remote Table Name").Value = strTable
.Properties("Jet OLEDB:Create Link").Value = True
.Properties("Jet OLEDB:Link Provider String").Value = "ODBC;DSN=SAGE;UID=" & strUID & ";PWD=" & strPWD & ";"
.Properties("Jet OLEDB:Cache Link Name/Password").Value = True
End With
cat.tables.Append tbl
Set tbl = Nothing
Set cat = Nothing
End Sub

I get a "Access database engine could not find object ''. Make sure the object exists" error - any ideas, Have I missed something out of the connection string - I do not think so as I have opened the connection successfully elsewhere. Regards, Jason

Sage & Access

johndon68 | | Permalink

Jason - not sure if you saw my reply in amongst your attempts to post :)

John

Add comment
Log in or register to post comments