TYPE MISMATCH err on Recordset in Access

[Guest]

Hi There,
When I run this code with Office 2003/Access 2003 it works but with Office
PRO 2000/Access 2000 I get a TYPE MISMATCH errors in this line:
Set rs = CurrentDb.OpenRecordset(query)
would you please look at my code and let me know what I'm missing.

Many tanks,
Leila
-----------------------------------------
Private Sub Command_Click()

Dim query As String
query = "SELECT ABRQ.Code, ABRQ.DT, FNQ.FileName FROM FNQ INNER JOIN ABRQ ON
FNQ.DT=ABRQ.DT"
Open "c:\test\script.dat" For Output As #1 ' Open file for output.

'open the recordset...

Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset(query)
Dim curDT As String ' contains the current DT
Dim prevDT As String ' contains the last DT
prevDT = ""

'loop thru the recordset...
While Not rs.EOF
curDT = rs.Fields(1)

If StrComp(curDT, prevDT) = 0 Then

If (Len(ABRCode) > 0) Then

ABRCode = ABRCode + ";"

End If

ABRCode = ABRCode + rs.Fields(0)

Else

If (Len(prevDT) > 0) Then

Print #1, Chr(34) & Chr(34) & Chr(44) & Chr(34) &
Trim(rs.Fields(0)) & Chr(34) & Chr(44) & Chr(34) & ABRCode & Chr(34)

ABRCode = "" ' reset the ABRCode
End If
prevDT = curDT
End If
rs.MoveNext
Wend
rs.Close
Close #1
End Sub

 

[John Vinson]

Hi There,
When I run this code with Office 2003/Access 2003 it works but with Office
PRO 2000/Access 2000 I get a TYPE MISMATCH errors in this line:
Set rs = CurrentDb.OpenRecordset(query)
would you please look at my code and let me know what I'm missing.

Check your References (Tools... References in the Menu in the VBA
editor). I suspect that you had a reference to Microsoft DAO 3.51 set
in your A2000 database, and that your 2003 database does not have
Microsoft DAO 3.6 checked, or that it comes after Microsoft ActiveX
Data Objects; both of these have Recordset objects but they are
DIFFERENT recordset objects.

CurrentDb.OpenRecordset is DAO code so you should be able to

Dim rs As DAO.Recordset

if DAO 3.6 is checked.

John W. Vinson[MVP]

 

[Guest]

Thank you so much it worked!

Check your References (Tools... References in the Menu in the VBA
editor). I suspect that you had a reference to Microsoft DAO 3.51 set
in your A2000 database, and that your 2003 database does not have
Microsoft DAO 3.6 checked, or that it comes after Microsoft ActiveX
Data Objects; both of these have Recordset objects but they are
DIFFERENT recordset objects.

CurrentDb.OpenRecordset is DAO code so you should be able to

Dim rs As DAO.Recordset

if DAO 3.6 is checked.

John W. Vinson[MVP]