SQL connect to DSN

[D Willett]

Hi

Using an ODBC "auto"the following code ( two versions ) will not pull the data from the table.
If I create a "test.udl" I can connect and if I link the table through a test Access.mdb I can connect and pull the data.
But, both versions of code below error out as it tries to read into the table:

Code: Select all

Set Rst = New ADODB.Recordset

conn.Open "DSN=auto"
Rst.Open "SELECT UserID, VehID, JobID FROM DBA_MAN_PAR", conn

'MsgBox Rst.Fields(0)
'MsgBox Rst.Fields(1)
'MsgBox Rst.Fields(2)


While Not Rst.EOF
List1.AddItem Rst.Fields(1)
Rst.MoveNext
Wend
Rst.Close
conn.Close
Set Rst = Nothing
Set conn = Nothing
End Sub

Private Sub Command3_Click()
Dim conn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rs As ADODB.Recordset
Dim connStr As String
Dim sqlText As String

Set conn = New ADODB.Connection
connStr = "DSN=auto"
conn.Open (connStr)

sqlText = "SELECT UserID, VehID, JobID FROM DBA_MAN_PAR"
                                             
Set cmd = New ADODB.Command
cmd.CommandText = sqlText
cmd.ActiveConnection = conn
Set rs = New ADODB.Recordset
Set rs = cmd.Execute()

rs.MoveFirst
Do While Not rs.EOF
   MsgBox (rs.Fields(0).Value)
   rs.MoveNext
Loop

End Sub

The error returned is:

[Sybase][ODBC Driver][Adaptive Server Anywhere] Table or view not found: Table'DBA_MAN_PAR' Not found.

I'm stumped ???

[rory]

I'm not familiar with Sybase - do you need to specify a default database in the DSN?

[HansV]

Can you tell us which version of Sybase you are using? The error message mentions Adaptive Server Anywhere but not a version number.

[D Willett]

Hi Hans
I think it's 7.0.1 ?

[HansV]

You could try something like this:

Code: Select all

Set conn = New ADODB.Connection
conn.Open "ODBC; Driver=Sybase SQL Anywhere 5.0;" & _
           "DefaultDir=c:\sqlany701\;" & _
           "Dbf=c:\sqlany701\mydb.db;" & _
           "Uid=myUsername;" & _
           "Pwd=myPassword;" & _
           "Dsn="""""

Substitute the correct path and name of the database, username and password.