Help connecting to a SyBase database

[PRMiller]

I'm having some problems connecting to a Sybase database. I lifted a connection string from

http://www.connectionstrings.com

but have been having problems getting it to work. I've searched through the posts on the site and checked out issues other users have been facing, but none of the solutions seem to have helped.

Here's my code:

Public Function BLMWitnessLink()

    Dim intCounter As Integer
    Dim oCat As ADOX.Catalog
    Dim oTable As ADOX.Table
    Dim strConnString As String
    Dim strLinkTable As String 'DB Table Name
    Dim strSQLTable As String  'SQL Server Table Name

    ' Set SQL Server connection string used in linked table.
    strConnString = "ODBC; Driver=Sybase SQL Anywhere 5.0;" & _
                    "DefaultDir=\\Blmms036\Database\;" & _
                    "Dbf=\\Blmms036\Database\WITNESS.DB;" & _
                    "Uid=userid;" & _
                    "Pwd=password;" & _
                    "Dsn="""""
                    
    For intCounter = 1 To 5
        If intCounter = 1 Then
            strLinkTable = "BLM_Agent"
            strSQLTable = "DBA.agent"
        ElseIf intCounter = 2 Then
            strLinkTable = "BLM_AgntDept"
            strSQLTable = "DBA.agntdept"
        ElseIf intCounter = 3 Then
            strLinkTable = "BLM_Depts"
            strSQLTable = "DBA.depts"
        ElseIf intCounter = 4 Then
            strLinkTable = "BLM_EvalCommentData"
            strSQLTable = "DBA.EvalCommentData116"
        ElseIf intCounter = 5 Then
            strLinkTable = "BLM_EvalHeadSummData"
            strSQLTable = "DBA.EvalHeadSummData116"
        End If
         
    If DCount("Name", "MSysObjects", "[Type]=4 AND [Name]='" & strLinkTable & "'") < 1 Then
        ' Create and open an ADOX connection to Access database
        Set oCat = New ADOX.Catalog
        oCat.ActiveConnection = CurrentProject.Connection

        ' Create a new Table object
        Set oTable = New ADOX.Table

        With oTable
            .name = strLinkTable
             Set .ParentCatalog = oCat
            .Properties("Jet OLEDB:Create Link") = True
            .Properties("Jet OLEDB:Remote Table Name") = strSQLTable
            .Properties("Jet OLEDB:Cache Link Name/Password") = True
            .Properties("Jet OLEDB:Link Provider String") = strConnString
        End With

        ' Add Table object to database
        oCat.Tables.Append oTable
        oCat.Tables.Refresh
        Set oCat = Nothing
    End If

    Next intCounter

End Function

The function errors out at the oCat.Tables.Append oTable line, with this error: ODBC--connection to """ failed.

Any help would be appreciated.

Thanks,
Paul

 

[PHV]

Are you sure you need an empty DSN in your connection string ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PRMiller]

Actually, I'm not sure, and that's one of the areas in which I could use some guidance. Removing that line so that the code reads:

    strConnString = "ODBC; Driver=Sybase SQL Anywhere 5.0;" & _
                    "DefaultDir=\\Blmms036\Database\;" & _
                    "Dbf=\\Blmms036\Database\WITNESS.DB;" & _
                    "Uid=userid;" & _
                    "Pwd=password;"

Gives me an "ODBC--Call failed" error, so I'm operating under the assumption -- perhaps erronously -- that I do need that part of the string.

 

[PRMiller]

I hate to post simply for the sake of bumping a thread, but I remain at an impasse. Any suggestions, anyone?