Start New Discussion within our Software Development Community

Hi,

I am developing an Inventory Management System using VB 6.0, and MS Access Database. I am getting an error: "data type mismatch in criteria expression" which sounds like a variable type mismatch. But i have checked and double checked my datatypes and they seem to match up. Here is some of the code where the issue is:

Public cnConnection As New ADODB.Connection

Public Sub openDatabase()
    'This function sets up the connection to the database and opens it.
    DataSource = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\" & "SWCERT_IMAS.mdb;Mode=Read|Write"
    cnConnection.ConnectionString = DataSource
    cnConnection.Open
End Sub

Public Sub runOmitVendorQuery()
    SQLomitVendor = "select UserRights from tblUSERS where UserID = '" & CurrentDeviceUserID & "'"
    
    Set rsOmitVendor = New ADODB.Recordset
    rsOmitVendor.Open SQLomitVendor, cnConnection, adOpenDynamic
End Sub

The Datatypes from the data above are:
UserRights = Text (in Access)
UserID = AutoNumber (in Access, Primary Key of tblUSERS)
CurrentDeviceUserID = public Integer

When this SQL statement is reached in the flow the Database is open, not sure if that will help. I have a feelig this is something simple right infront fo my face but it is driving me batty. Any help would be greatly appreciated.
Thanks - JR

try using the following

Public Sub runOmitVendorQuery()
SQLomitVendor = "select UserRights from tblUSERS where UserID = " & CurrentDeviceUserID

Set rsOmitVendor = New ADODB.Recordset
rsOmitVendor.Open SQLomitVendor, cnConnection, adOpenDynamic
End Sub
This article has been dead for over six months. Start a new discussion instead.