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.

 

[Pat Hartman]

What is the point of using an unbound form for adding records? Using the same BOUND for for add and update allows you to use the same validation code and not have to duplicate it in two forms. Although you don't seem to have bothered with validating the data.

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.

Most of my forms have a textbox or combo that selects the record the user wants. The RecordSource query refers to this control.
Select .... From ... Where somefield = forms!yourformname!somefield

When the form opens, itis empty since the somefield control is empty. In the after update event of the control, requery the form to get the requested record.

Me.Requery

With the exception of certain techniques like using forms bound to queries rather than tables, using SQL Server as the BE doesn't force you to make many changes. Slight things like knowing when to use dbSeeChanges (which works even if the BE is ACE) are required but not much else. I almost always build my new apps using ACE but with the intention of switching to SQL Server as soon as I am ready for user testing. That means I am always mindful of how SQL Server works and build my Access apps with that in mind. An Access app built on sound client/server principles works just fine using ACE. However, an Access app built using "Access" principles like binding a form to a table and using filters to navigate will have dismal performance once linked to SQL Server. Most of the Access apps I build can be converted to SQL Server in an afternoon or less depending on how much testing is required.

 

[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.

 

[Pat Hartman]

Access doesn't like the key field combinations for some particular reason

Not sure what you are talking about here but you are posting in an old thread.