Connecting access front end forms to sql server tables

[Db-why-not]

I created some forms and reports that will use 5 related tables. When I designed all the forms I made some dummy tables within access so I could make sure everything worked. Most of the forms are bound forms. I have 2 unbound forms. I also made different querys that some of the forms and reports use instead of pulling data directly from tables. The real tables are going to be stored on sql server. I have someone that created the tables on the sql server and will be managing the sql server for me. I already linked the sql server tables to access, I can view and edit the data in access for the sql server tables. I used odbc to connect them. I just need to connect the tables directly to the forms now. I've never used sql sever as a backend before. I just wanted some advice on how to start connecting the access forms to the sql server tables. Any articles or videos anyone would recommend? Since I have already linked the sql server tables can I just change the tables source within access forms to sql server table and just change tables source within the queries? I haven't tried it yet. Is it ok to have queries running from access or should I have my queries on the sql server? On the sql server the queries would be called a view right? Thanks.

 

[theDBguy]

Hi. Bound forms can be based on tables or queries. It shouldn't matter if it's local or linked. If the structure is the same between your Access and SQL Server tables, then switching the source names should work fine.

 

[Db-why-not]

1 of my forms is unbound. I keep getting an error message. I have a form that adds new users to the database. this is my code for the submit button. dbo_tbl_User is a table that is on the sql server and is linked to my access front end. It was working when I used the dummy access table, when I switched it to the sql server table I started getting the error message.

"Run time error '3622'
You must use the dbseechanges option with openrecordset when accessing a sql server table that has an identity column

Private Sub cmdSubmit_Click()

If (IsNull(Me.txtFName.Value) Or IsNull(Me.txtLName.Value) Or IsNull(Me.txtPassword.Value) Or IsNull(Me.txt2Password.Value)) Then
MsgBox "Please fill in all textboxes!"
ElseIf Me.txtPassword <> Me.txt2Password Then
MsgBox "Passwords do not match!"
ElseIf Me.txtPassword = Me.txt2Password Then


Dim tblUser As DAO.Recordset

Set tblUser = CurrentDb.OpenRecordset("Select * From [dbo_tbl_User]")
tblUser.AddNew
tblUser![szFirstName] = Me.txtFName.Value
tblUser![szLastName] = Me.txtLName.Value
tblUser![szMiddleInitial] = Me.txtMInitial.Value
tblUser![szPassword] = Me.txtPassword.Value
tblUser![szUser] = Me.txtUserName.Value
tblUser.Update
tblUser.Close

MsgBox "User has been added to the database."
'Resets the form
Me.txtFName.Value = Null
Me.txtLName.Value = Null
Me.txtMInitial.Value = Null
Me.txtPassword.Value = Null
Me.txt2Password = Null

End If

End Sub

 

[Minty]

Set tblUser = CurrentDb.OpenRecordset("Select * From [dbo_tbl_User]")

Change this line to

Set tblUser = CurrentDb.OpenRecordset("Select * From [dbo_tbl_User]", dbSeeChanges)

And it should all work

 

[Db-why-not]

Change this line to

Set tblUser = CurrentDb.OpenRecordset("Select * From [dbo_tbl_User]", dbSeeChanges)

And it should all work

I changed it to that
Set tblUser = CurrentDb.OpenRecordset("Select * From [dbo_tbl_User]", dbSeeChanges)

Now it is giving me error message run time error '3001': Invalid argument and its highlighting that line I changed

 

[Db-why-not]

I tried this also, still giving me the error message

Set tblUser = CurrentDb.OpenRecordset("Select * From [dbo_tbl_User]", dbOpenDynaset, dbSeeChanges)

 

[Db-why-not]

now its working.

 

[Db-why-not]

For my other unbound form Where I delete users, I need to change that code also. How would I add dbOpenDynaset, dbSeeChanges to this code. I keep getting errors trying to add it. If I figure it out I will post it also.

Private Sub cmdDelete_Click()

 Dim dbs As DAO.Database, sql As String, rCount As Integer

    Set dbs = CurrentDb
    If txtID = "" Then
        MsgBox "Please select a contact to delete."
    Else

        sql = "DELETE * FROM dbo_tbl_User Where lUserID=" & txtID , dbOpenDynaset, dbSeeChanges
        dbs.Execute sql, dbFailOnError
        rCount = dbs.RecordsAffected
        If rCount > 0 Then
            MsgBox "User has been deleted"
            
            txtFName = ""
            txtLName = " "
            txtMInitial = " "
            lstData.Requery
            
                 End If
                 End If

End Sub

 

[theDBguy]

Hi. Try it this way:

       sql = "DELETE * FROM dbo_tbl_User Where lUserID=" & txtID
        dbs.Execute sql, dbFailOnError + dbSeeChanges

Hope that helps...

 

[Db-why-not]

Hi. Try it this way:

       sql = "DELETE * FROM dbo_tbl_User Where lUserID=" & txtID
        dbs.Execute sql, dbFailOnError + dbSeeChanges

Hope that helps...

It works, Thanks!

 

[Isaac]

For various reasons that tend to mostly reveal themselves in hindsight, it's a very good idea to--instead of actually deleting records (especially user records), add a column or two with a flag that indicates whether a record is active, obsolete, has expired or terminated, etc.

Will users ever come back? If so, one idea for your situation would be to include a StartDate and EndDate of any given user's tenure. They are currently active if a record exists where StartDate is <= today, and EndDate is null or is > today. This gives you a lot of flexibility and insight--even the ability to respond immediately if someone says "this user's last day will be next Friday", you can make that update immediately.

If all that is too much, even just an "Active" flag that indicates either active or inactive.

Generally speaking, in the future it's usually much better to know if someone is/was a pre-existing user...Because you never know what kind of questions people might ask or what that might imply to an audit or an explanation, or being able to pull a report Joined to a users table that still "works" because the user wasn't deleted literally from the db.

 

[sportsguy]

Absolutely correct, there are ways to do when inserting rows, use the data entry = True on the access subform. This prevents downloading the whole table, which can blow up access. Or put IS NULL in any required field in the subform query to prevent downloading the whole table, or some sort of greater than DateNow() - 14 days, or something similar to restrict the amount of data ms access downloads.

Like wise, you can create a view query/table on the server with same underlying query parameters, and link MS Access to the view query without any MS access filters, which I find preferable when using MS access and Access doesn't like the key field combinations for some particular reason

It is NOT recommended to bind your forms/reports to tables. Forms and reports should be bound to queries so that you can restrict the recordset the form uses to be only the needed columns and only a small (preferably 1) set of records. Binding a form directly to a table is what makes DBA's hate Access since the form opens as soon as there is a record to display but the form just sits there in the background with a straw sucking every single row from the table down to your PC for absolutely no reason. One of the major benefits of using an RDBMS as your BE is to gain efficiency with data and the only way to do that is to use queries that select specific records. Access attempts to pass-through ALL queries. You can defeat it but it does try. You want all queries to be executed on the server and have the server return only the selected rows and columns. You will learn that you need to avoid UDF's and VBA functions that do not have SQL Server equivalents in your queries for starters.