ado odbc connection to SQL server

[simian101]

I have done a number of odbc programs. I am running Access 2003 and Sql Server 2005.

I have a basic connection (below) and I am able to execute stored procedures and run updates but I am not able to return a simple recordset?

Any Ideas.

Simi

Private Sub Command10_Click()
'New as400 test button

Dim cnn As ADODB.Connection
Dim RS1 As ADODB.Recordset

Set cnn = New ADODB.Connection

cnn.ConnectionString = "dsn=tdcets"
cnn.ConnectionTimeout = 30

' Open the connection.
cnn.Open
If cnn.State = adStateOpen Then
Debug.Print "CNN is Open"

sqlstr = "select startdate from payperiod"
Debug.Print sqlstr
Set RS1 = cnn.Execute(sqlstr)

primaryreccount = RS1.RecordCount

If primaryreccount > 0 Then
RS1.MoveFirst
Debug.Print RS1("startdate")
Else
Debug.Print ("No Records Found. Please try again.")
End If
Else
Debug.Print "CNN not Open"
End If


RS1.Close
cnn.Close
End Sub

 

[SkipVought]

Hi,

replace

Set RS1 = cnn.Execute(sqlstr)

with

Set RS1 = New ADODB.Recordset
RS1.Open sqlstr, cnn, adOpenStatic, adLockReadOnly, adCmdText

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[simian101]

Skip as usual, you da man!!!

Thanks

Simi