Unanswered: Writing to SQL Server using existing DAO code
I am trying to port over a number of access db's to SQL Server, and I want to avoid rewriting the data access code.
I am currently testing on SQL 2008 Express on my local machine, and am using the SQL Native 10.0 driver.
My table has a primary key, and I can open and write to the table manually no problem.
I have had a look at a few threads on here and have taken a few tips, but I am unable to get the following code working..
Private Function UserLoggedIn(UserID As Integer) As Boolean
On Error GoTo PROC_ERR
Dim db As DAO.Database
Dim rs As DAO.Recordset ' tblLogins
Dim SQL As String
Set db = CurrentDb
SQL = "SELECT UserID, UserPC, LogInDate FROM tblLogIns WHERE LogOutDate Is Null and UserID=" & UserID
Set rs = db.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
If Not rs.EOF And Not rs.BOF Then
MsgBox "This machine is already logged in", vbExclamation, "Machine Logged In"
UserLoggedIn = True
rs!UserID = UserID
rs!UserPC = Scripts_basSysNames.fOSMachineName
rs!LogInDate = Now
On Error Resume Next
Set rs = Nothing
Set db = Nothing
MsgBox "Error occurred in " & Me.Name & ".UserLoggedIn " & vbCrLf & Err.Number & " : " & Err.Description