ADO vs DAO not working why???

[MHiemstra]

I have the following code to being back values to a form...If I use DAO all
works well however, I am trying to learn ADO and running into a SNAFU. ADO
works in one example but in another I don't understand the error message and
why is it failing.

Here is the code that works in DAO...

Public Function intCurrent() As Integer

Dim rs As DAO.Recordset
Dim strSQL As String

If Nz(Forms!SHD_REGRESSION_TESTINGS.Regression_ID.Value, 0) <> 0 Then

strSQL = /BIG OLE QUERY/ * didn't want to post the whole thing

Set rs = CurrentDb.OpenRecordset(strSQL)

intCurrent = rs(0)
Else
intCurrent = 0
End If

Set rs = Nothing

End Function

The following produces this error: Method 'Open' of Object '_Recordset'
failed. What I don't get is I have the exact same ADO funtion elsewhere in
another function and it works fine.


Public Function intCurrent() As Integer

Dim rs As New Recordset
Dim strSQL As String

If Nz(Forms!SHD_REGRESSION_TESTINGS.Regression_ID.Value, 0) <> 0 Then

strSQL = BIG OLE QUERY
rs.Open strSQL, CurrentProject.Connection
intCurrent = rs(0)
Else
intCurrent = 0
End If

rs.Close


Please help....

 

[Ken Snell [MVP]]

You're not specifying which type of Recordset object to use for the ADO
version. If your DAO library is higher in priority than the ADO library in
your references, then ACCESS will assume that this statement means to use a
DAO recordset:

Dim rs As New Recordset

Change it to this and see if it works better:

Dim rs As New ADODB.Recordset

 

[MHiemstra]

I am sorry to bother you on this, I just needed a few more moments to
research...

This post helped out...

http://www.developerfusion.co.uk/forums/topic-25918

The problem was with my SQL, I was using the word Current as an alias for a
table, which I have now learned is a reserved word and I had to put
[brackets] around it.