VBA datatype mismatch error 13

[JingleBEV]

Hi,

Iam coding data access application with database object and recordset.
Here' s my code
'------------------------------------------------------------------
Private Function CreateRecordSet() As Boolean
Dim db As Database
Dim rs As Recordset
Set db = DBEngine.Workspaces(0).OpenDatabase(txtDBpath)
sql = "SELECT * FROM TblMembers"
Set rs = db.OpenRecordset(sql, dbOpenDynaset) 'THE ERROR
APPEARS HERE
MsgBox "show this", vbOKOnly

If Err.Number = 0 Then
CreateRecordSet = True
MsgBox "no error", vbInformation
Else
CreateRecordSet = False
MsgBox Err.Description, vbCritical
End If
End Function
'------------------------------------------------------------------

I got an error 13 at runtime that says "Type mismatch". I look at different
books and the internet but still convince myself that my code is OK. No
errors were found after OpenDatabase method, and the txtDBpath is correct,
TblMembers is one of the tables in the .mdb file
Can someone tell me why I got that error at runtime.

I am running
windows xp
visual basic 6,
mdb file was create in Access 2003 and converted to Access97

Thanks.

[Douglas J. Steele]

It's possible that you have a reference set to both ADO and DAO, and that
the ADO reference is higher in the sequence than DAO.

Try changing your declaration to Dim rs AS DAO.Recordset

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"JingleBEV" <n_****@NOSPAMyahoo.com> wrote in message
news:gP********************@news20.bellglobal.com. ..

Hi,

Iam coding data access application with database object and recordset.
Here' s my code
'------------------------------------------------------------------
Private Function CreateRecordSet() As Boolean
Dim db As Database
Dim rs As Recordset
Set db = DBEngine.Workspaces(0).OpenDatabase(txtDBpath)
sql = "SELECT * FROM TblMembers"
Set rs = db.OpenRecordset(sql, dbOpenDynaset) 'THE ERROR APPEARS HERE
MsgBox "show this", vbOKOnly

If Err.Number = 0 Then
CreateRecordSet = True
MsgBox "no error", vbInformation
Else
CreateRecordSet = False
MsgBox Err.Description, vbCritical
End If
End Function
'------------------------------------------------------------------

I got an error 13 at runtime that says "Type mismatch". I look at different books and the internet but still convince myself that my code is OK. No
errors were found after OpenDatabase method, and the txtDBpath is correct,
TblMembers is one of the tables in the .mdb file
Can someone tell me why I got that error at runtime.

I am running
windows xp
visual basic 6,
mdb file was create in Access 2003 and converted to Access97

Thanks.

[Don Leverton]

Hi.

This is possibly a WAG (Wild-Ass Guess) but is the database defined by
"txtDBPath" also an Access97 .MDB?
If it happens to be a newer version, it will likely be using ADO instead of
DAO by default.

Here are a couple of things to try:
Dim your Database and Recordsets objects explicitly as DAO objects.(Access97
won't mind)
I had also noticed that your code did not declare your "sql" variable as
String data type.
I believe that undeclared variables are treated as Variant, and this may be
where the Error code 13 comes from?

I also renamed "sql" to "MySQL" as SQL may be a reserved word.
Note the semicolon that I added to the end of your SQL string.
Some will say that it's not absolutely required, but I always put it in to
signal the end of the string.

********************************************
Private Function CreateRecordSet() As Boolean

Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = DBEngine.Workspaces(0).OpenDatabase(txtDBpath)

Dim MySQL As String
MySQL = "" 'Clear the string variable (by habit)

MySQL = MySQL & "SELECT * FROM TblMembers;"

Set rs = db.OpenRecordset(MySQL, dbOpenDynaset)
MsgBox "show this", vbOKOnly

If Err.Number = 0 Then
CreateRecordSet = True
MsgBox "no error", vbInformation
Else
CreateRecordSet = False
MsgBox Err.Description, vbCritical
End If

End Function
********************************************

--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================


"JingleBEV" <n_****@NOSPAMyahoo.com> wrote in message
news:gP********************@news20.bellglobal.com. ..

Hi,

Iam coding data access application with database object and recordset.
Here' s my code
'------------------------------------------------------------------
Private Function CreateRecordSet() As Boolean
Dim db As Database
Dim rs As Recordset
Set db = DBEngine.Workspaces(0).OpenDatabase(txtDBpath)
sql = "SELECT * FROM TblMembers"
Set rs = db.OpenRecordset(sql, dbOpenDynaset) 'THE ERROR APPEARS HERE
MsgBox "show this", vbOKOnly

If Err.Number = 0 Then
CreateRecordSet = True
MsgBox "no error", vbInformation
Else
CreateRecordSet = False
MsgBox Err.Description, vbCritical
End If
End Function
'------------------------------------------------------------------

I got an error 13 at runtime that says "Type mismatch". I look at different books and the internet but still convince myself that my code is OK. No
errors were found after OpenDatabase method, and the txtDBpath is correct,
TblMembers is one of the tables in the .mdb file
Can someone tell me why I got that error at runtime.

I am running
windows xp
visual basic 6,
mdb file was create in Access 2003 and converted to Access97

Thanks.