convert access Application to SQL Backend

[Shift838]

I have converted the data from Access to load into SQL but I have some code that will not work when the form loads. The form checks the 'users' table to determine the type of access. I get the below error:

The Microsoft Office Access database engine cannot find the input table or query 'users'. Make sure it exists and that its name is spelled correctly.

As I said it is now in SQL and I have added the links to the sql database for the tables and can access them directly.

I am sure it is something to do with the conversion.

my code is as follows:

Private Sub Form_Load()
On Error GoTo ErrorHandler


Dim strSQL As String

Dim rst As DAO.Recordset

Dim vuser As String

strSQL = "SELECT * FROM users WHERE users.txtUSERID='" & VBA.Environ("UserName") & "'"

Set rst = CurrentDb.OpenRecordset(strSQL)

vuser = rst.RecordCount ' Verifies the logged on user has a record with the tblUsers table


If Not (rst.BOF And rst.EOF) Then
' records exist

End If


 ' Assign rights
 If vuser > 0 Then
 ' admin
    cmdAdmin.Enabled = rst.Fields("blnadminID") ' Enables Admin Button to execute Admin Form to add users
    cmdAcct.Enabled = rst.Fields("blnacctid")   ' Enables Non-SAP button to allow Non-SAP users to view/edit records for Non-SAP SERVICE/USER accounts
    cmdGroup.Enabled = rst.Fields("blngroupid")     ' Enables SAP button to allow SAP users to view/edit records for SAP SERVICE/USER accounts
    cmdReporter.Enabled = rst.Fields("blnReportID")  'Enables Reporter button for users to execute and run reports
    cmdAcctUser.Enabled = rst.Fields("blnacctUser")     ' Enables Non SAP Users to update all records but cannot view/modify passwords
    cmdGroupUser.Enabled = rst.Fields("blngroupUser")     ' Enables SAP Users to update all records but cannot view/modify passwords
    cmdAcctView.Enabled = rst.Fields("blnacctView")     ' Enbales Non SAP Users with View only role to all records but cannot view passwords
    cmdGroupView.Enabled = rst.Fields("blngroupView")     ' Enbales SAP Users with View only role to all records but cannot view passwords
    cmdAcctMM.Enabled = rst.Fields("blnreportID")     'Enables NonSAP Mail Merge Reporter button for users to execute and run reports
    CMDGroupMM.Enabled = rst.Fields("blnreportID")     'Enables SAP Mail Merge Reporter button for users to execute and run reports
 Else
    MsgBox ("You are not authorized for this system!")
    DoCmd.Quit 'exit from access
 End If

 
ExitProcedure:
    Exit Sub
ErrorHandler:
    MsgBox mstrModule & "-" & "Form_Load" & vbCrLf _
        & Err.DESCRIPTION, Err.Number 'displays error in and gives description and module occured at
    Resume ExitProcedure
         
End Sub

 

[dhookom]

Check the linked names. Did you remove the "dbo_"? Also, USER is not a good name for a table, especially on SQL Server.

Duane
Hook'D on Access
MS Access MVP

 

[Shift838]

I have removed the dbo_ from the linked tables as their names. The table is not "user" but "users"

now I get:

You must use the dbSeeChanges option with OpenRecordset when accessing a SQL Server Table that has an IDENTITY column.

 

[dhookom]

Check the Help on DAO recordsets. If you add the dbSeeChanges, it should work.

Duane
Hook'D on Access
MS Access MVP

 

[Shift838]

looks like I got it figured. out I had to change:

Set rst = CurrentDb.OpenRecordset(strSQL)

to

Set rst = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset, dbSeeChanges)