Writing to SQL Server using existing DAO code

[winshent]

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

  If SQL_Server Then
    Set rs = db.OpenRecordset(SQL, dbOpenDynaset, dbSeeChanges)
  Else
    Set rs = db.OpenRecordset(SQL, dbOpenDynaset)
  End If
  
  If Not rs.EOF And Not rs.BOF Then
    MsgBox "This machine is already logged in", vbExclamation, "Machine Logged In"
    UserLoggedIn = True
  Else
    rs.AddNew
      rs!UserID = UserID
      rs!UserPC = Scripts_basSysNames.fOSMachineName
      rs!LogInDate = Now
    rs.Update
  End If

PROC_EXIT:
  On Error Resume Next
  
  Set rs = Nothing
  Set db = Nothing
  
  Exit Function

PROC_ERR:
  MsgBox "Error occurred in " & Me.Name & ".UserLoggedIn " & vbCrLf & Err.Number & " : " & Err.Description
  Resume PROC_EXIT

End Function

It fails at rs.AddNew

Any ideas ?

 

[winshent]

I've now posted this on dbformums..

http://www.dbforums.com/microsoft-access/1681082-writing-sql-server-using-existing-dao-code.html#post6556802

Please respond there if possible..

 

[SQL_Hell]

I have been considering answering this but I don't know how to get DAO working with SQL server (been too long) and I am not sure why you would want to.

Personally I would change it all to use ADO, in your example you would only need to change 2 things (if my memory serves me correctly).

Apologies for the vague answer but it's been a long time since I have used access with SQL server, so I normally just answer the SQL server only questions