Unanswered: Need help with recordset within a split database
I am attempting to split my database into a front/back end application. There is now a demand on the system to allow multiple users and I feel that this setup is the best. However, I am having a hard time with some of my code using linked tables. Here is the first instance I am having trouble with. Once I can solve this, the rest will be modeled like this (with the addition of a few .AddNew's and .Edit's).
Dim rst As Recordset
Dim strsql As String
Dim Answer As String
strsql = "Select * from tblSecurity where EmployeeID = "
strsql = strsql & "'" & TxtEmployeeID & "'"
If (IsNull(TxtEmployeeID) Or TxtEmployeeID = "") Then
msgbox "You must enter in your Employee ID number"
ElseIf (IsNull(TxtPassword) Or TxtPassword = "") Then
msgbox "you must enter your password"
Set rst = CurrentDb.OpenRecordset("tblSecurity")
rst.Index = "primarykey"
rst.Seek "=", TxtEmployeeID
If rst.NoMatch Then
msgbox "Your ID is not found in the system. Please contact the Shift Supervisor for access to the system or use 'GST1111A' and 'Password' as your login."
TxtEmployeeID = Null
TxtPassword = Null
Set rst = CurrentDb.OpenRecordset(strsql, dbopensnapshot)
If rst(3) <> TxtPassword Then
msgbox "The password you entered is not correct. Please try again."
SecLevel = rst(4)
UserName = rst(0)
If rst(7) = True Then
DoCmd.OpenForm "frmchangepassword", , , , , acDialog
As you can see, I only have 2 text controls and a command button on this form. This code is behind the click event of the button. When I run this code, I get the error 'Type mismatch' at the line - Set rst = CurrentDb.OpenRecordset("tblSecurity"). I originally had Microsoft DAO 3.6 Object Library and Microsoft ActiveX Data Objects Recordset 2.5 Library as references, but attempted to change only to the ADO version.
Does any one have any suggestions, or other ways to accomplish this? I use this style recordset when adding records from a form, or when editing records. I do not have any bound forms in my database, since I feel it is harder to "Idiot proof" my user's input. Any help with this will be greatly appreciated.
I have made the switches from DAO to ADO regarding the references, but what I need help with is the proper set statement. All my recordsets use currentdb.openrecordset. Id this my problem? If it is, how do I use dbEngine(0)? To reiterate, I am attempting to connect to a remote database to retrieve my data.