I am looking for a way to link data from a Sybase database on a UNIX platform to MS Access 2003 on Windows XP. Has anyone done this successfully?

Experts, I need to make some data available for a mail-merge process that will utilize MS-Access and Word. I have installed Sybase's SQL Anywhere which does include an ODBC Driver, but have not had success in achieving a connection. Has anyone out there come up with a way to do this; something similar?
dataman2004Asked:
Who is Participating?
 
answer_dudeCommented:
Here's the full set of code that should attach a table for you.  Just call the ConnectSybaseTable function
Function ConnectSybaseTable(strTblName As String, _
                sServer As String, sPort As String, _
                sDB As String, sUID As String, sPWD As String) As Boolean

On Error GoTo Connect_Err

    Dim strConn As String
    Dim db As Database
    Dim tbl As TableDef
    
    Set db = CurrentDb()
        
    strConn = "ODBC;"
    strConn = strConn & "DRIVER={Sybase ASE ODBC Driver};"
    strConn = strConn & "NA=" & sServer & "," & sPort & ";"
    strConn = strConn & "DB=" & sDB & ";"
    strConn = strConn & "UID=" & sUID & ";"
    strConn = strConn & "PWD=" & sPWD & ";"
    strConn = strConn & "DSN=" & sServer & ";"
    strConn = strConn & "WA2=8192;"

    If (DoesTblExist(strTblName) = False) Then
        Set tbl = db.CreateTableDef(strTblName, _
                      dbAttachSavePWD, strTblName, _
                      strConn)
        db.TableDefs.Append tbl
    Else
        Set tbl = db.TableDefs(strTblName)
        tbl.Connect = strConn
        tbl.RefreshLink
    End If
    ConnectSybaseTable = True

Connect_Exit:
    Set tbl = Nothing
    Set db = Nothing
    Exit Function

Connect_Err:
    ConnectSybaseTable = False
    MsgBox Err & " - " & Error & vbCrLf & "Table attach failed."
    Resume Connect_Exit
    
End Function

'***************************************************************
'The DoesTblExist function validates the existence of a TableDef
'object in the current database. The result determines if an
'object should be appended or its Connect property refreshed.
'***************************************************************

Function DoesTblExist(strTblName As String) As Boolean
   On Error Resume Next
   Dim db As Database, tbl As TableDef
   Set db = CurrentDb
   Set tbl = db.TableDefs(strTblName)
   If Err.Number = 3265 Then   ' Item not found.
      DoesTblExist = False
      Exit Function
   End If
   DoesTblExist = True
   Set tbl = Nothing
   Set db = Nothing
End Function

Open in new window

0
 
answer_dudeCommented:
You need to attach an ODBC table and be sure you're using the correct ODBC driver and connection string.  

http://www.connectionstrings.com/

This worked for me (this is code to generate a connection string...  the key is "WA2=8192;" line if you're tyring to do it DNS-less:  http://database.itags.org/sybase/68091/


                Case "Sybase ASE ODBC Driver"
                    strConn = "ODBC;"
                    strConn = strConn & "DRIVER={" & rs("Driver") & "};"
                    strConn = strConn & "NA=" & rs("Server") & "," & rs("Port") & ";"
                    strConn = strConn & "DB=" & rs(sDB) & ";"
                    strConn = strConn & "UID=" & rs("UID") & ";"
                    strConn = strConn & "PWD=" & rs("PWD") & ";"
                    strConn = strConn & "DSN=" & rs("Server") & ";"
                    strConn = strConn & "WA2=8192;"
0
 
dataman2004Author Commented:
Thanks! Actually, your first statement got me thinking. "Get the correct ODBC driver". I got the right one and was able to connect. Thank You!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.