- Joined
- Jun 1, 2011
- Messages
- 2
- Reaction score
- 0
I have an Access frontend to a SQL db and I want to retrieve the results from a stored procedure and put them into a temporary table in access but I keep getting the above error message. I've tried using connection.execute and recordset.open but get the same error with either. I've been fiddling about with this for ages and could really do with some pointers. My code is below
Dim qdf As QueryDef
Dim strComm As String
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
strComm = "Exec st_orgsWithCurrentData " & GroupID & ", '" & MinExtractDate & "'"
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Driver=SQL Server;Server=ServerName;Database=dbName;Trusted_Connection=yes;"
cnn.Open
Set rs = New ADODB.Recordset
'Set rs = cnn.Execute(strComm)
rs.Open strComm, cnn, adOpenDynamic, adLockPessimistic
Debug.Print rs(0), rs(1), rs(2)
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing
Dim qdf As QueryDef
Dim strComm As String
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
strComm = "Exec st_orgsWithCurrentData " & GroupID & ", '" & MinExtractDate & "'"
Set cnn = New ADODB.Connection
cnn.ConnectionString = "Driver=SQL Server;Server=ServerName;Database=dbName;Trusted_Connection=yes;"
cnn.Open
Set rs = New ADODB.Recordset
'Set rs = cnn.Execute(strComm)
rs.Open strComm, cnn, adOpenDynamic, adLockPessimistic
Debug.Print rs(0), rs(1), rs(2)
rs.Close
Set rs = Nothing
cnn.Close
Set cnn = Nothing