Access 2000: Type mismatch

[Matt.]

Hi all!

Can somebody please tell me why I'm getting a "type Mismatch" error on the
"Set rs" line?

I'm using Access 2000, and would prefer and ADO solution, but I'm unsure of
the connection string for ADO into a Curentdb(). Anyway, I thought this
would work simply enough. I was wrong. Any help much appreciated.

cheers,
Matt.
---------------------------------------------------

Function GetStartDate(intYear As Integer, intMonth As Integer) As Date

Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim intRecords As Integer

strSQL = "Select datStart From tblFiscal " _
& "Where intYear = " & Year(Date) & " AND intFiscalMonth = " &
intMonth
MsgBox "strSQL: " & strSQL
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

'db.Execute strSQL, dbFailOnError

GetStartDate = CDate(rs("datStart"))


End Function

 

[Matt.]

Hi all!

Can somebody please tell me why I'm getting a "type Mismatch" error on the
"Set rs" line?

I'm using Access 2000, and would prefer and ADO solution, but I'm unsure of
the connection string for ADO into a Curentdb(). Anyway, I thought this
would work simply enough. I was wrong. Any help much appreciated.

cheers,
Matt.
---------------------------------------------------

Function GetStartDate(intYear As Integer, intMonth As Integer) As Date

Dim db As Database
Dim rs As Recordset
Dim strSQL As String
Dim intRecords As Integer

strSQL = "Select datStart From tblFiscal " _
& "Where intYear = " & Year(Date) & " AND intFiscalMonth = " &
intMonth
MsgBox "strSQL: " & strSQL
Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

'db.Execute strSQL, dbFailOnError

GetStartDate = CDate(rs("datStart"))


End Function

Ah.

I see I need to declare specifically the type of Recordset, as my
preferences are ADO, and Currentdb is a DAO construct. OK, so it's working
now, but what is the ADO way to do this?

cheers,
Matt.

 

[Allen Browne]

Why prefer ADO? If your data is stored in Access tables, use DAO - the
library that was specifically designed for Access, the library that Access
itself uses under the surface to manage its own objects. In fact, there are
some things that can only be done with the DAO library, such as setting the
DisplayControl of a yes/no field in a table to a checkbox.

If you want to use ADO anyway:

Function AdoRecordsetExample()
Dim rs As New ADODB.Recordset
Dim strSql As String

strSql = "SELECT MyField FROM MyTable;"
rs.Open strSql, CurrentProject.Connection

Do While Not rs.EOF
Debug.Print rs!MyField
rs.MoveNext
Loop

rs.Close
Set rs = Nothing
End Function

 

[Van T. Dinh]

Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset

Set cnn = CurrentProject.Connection
Set rs = New ADODB.Recordset

strSQL = ...

rs.Open strSQL, cnn, ...

....

See ADO Help for different arguments of the Open method.

OTOH, if you are using JET Back-End and you are not thinking of upsize, DAO
code may be more efficient since DAO is designed to work with JET
"natively".

 

[Matt.]

Thanks to both Allen and Van.

I know DAO was designed for Jet, but there's every indication we will be
going to SQL (eventually), and maybe keep the Access front end. Hence my
preference for ADO.

cheers,
Matt.