SQL Server Backend

Addendum: Copy Records & Get AutoNumber IDs

S M L XL FS | Slo Reg Fast 2x | Bookmark

In this lesson, we will discuss key differences between using an Access backend and a SQL Server backend, specifically in how AutoNumber or identity fields are assigned when copying records. I will explain and demonstrate how SQL Server assigns identity values only after a record is saved, unlike Access which does so immediately. We will cover the necessary adjustments to your VBA code, such as using the dbSeeChanges option and when to retrieve the new identity value, to ensure compatibility with SQL Server as a backend. The process for handling child records after getting the new ID will also be explained.

Navigation

Prev: Lesson 04
Next: Outline

Keywords

Access Developer, Access backend SQL Server, AutoNumber vs identity, dbSeeChanges, copy record with children, rs.AddNew rs.Update, get new record ID, linked tables, recordset parameters, Access SQL Server backend, save record to assign ID

Subscribe to SQL Server Backend
Get notifications when this page is updated

Intro

Transcript

Okay folks, this is an addendum for Access Developer 24. Two years ago when I released this, I said in lesson three, around seven minutes in, I talk about this code not working with SQL Server as a backend. Paul Brand wants to know if I can point you in the right direction. Well, it has been two years, and you are the first person to bring this up. So yes, I will talk about it.

In Developer 24, I show you how to copy a record. In order to do that, we have to get the order ID for the new record that we are creating. Now, with just Access, when you are dealing with Access, as soon as you put anything into that new record, Access immediately assigns an ID.

Simple standard Access database. Customer table. Come down to the bottom. As soon as I type in a character in here, bam, I get a new AutoNumber.

SQL Server does not behave that way. This is one of my maintenance databases that I use for working on my website. I have got this Access database connected to my SQL Server online.

Here is the notification table when you get little notifications on my website. These are all handled automatically by the web.

If I come into here, if I start typing in a customer ID, for example, notice that the NotificationID, which is an AutoNumber - but in SQL Server they are called identities - this will automatically get set once I finish the record. I think I need to have a comment ID in here. Oh, there it goes. As soon as I click off of it, it assigns that identity.

If I come down here and type that in, then click off of it - yes, I must have clicked off the row before. As soon as you leave that record, there it goes. So the ID is not assigned until the record is saved. You have to save whatever things you have to save. I guess none of these other things are required. I think just the CustomerID is required. All the rest of these fields are optional. I need to leave these.

SQL Server handles when that AutoNumber is assigned differently than Access does by itself. So this code, which is Access only, will not work right if you have a backend table that is linked to SQL Server.

I did make a test here, though. Here is what you have to do. First of all, when you declare your recordset, you have to use two extra parameters here. There is dbOpenDynaset, which makes it editable, and you have to use the dbSeeChanges option. I talk about this in my Access to SQL Server Seminar. I will put a link to that down below in case you want to learn more information about this.

So that is the first thing: you have to have dbSeeChanges. Then add your new record, rs.AddNew, like you normally do. Set whatever fields you are going to set. Do the rs.Update. At this point, but before you close the recordset, now you can get that number and get that new ID. NotificationID is my AutoNumber identity for this particular table. So you can get the ID and then display it.

If you have to get that ID and then do other stuff, you can close this recordset and then open it up again, because now you know the ID. So now you could say "SELECT whatever FROM the table WHERE the ID equals" this new ID that you just got. You will need that for obviously putting in the child records if you are doing what we do in Developer 24, which is copy the record with all of its children.

So that is basically the difference. You have to add the new record, put in whatever fields are mandatory or required so you can save the record, and do the update. Then, after you do the rs.Update, you can get the ID.

Quiz

Q1. What is the primary difference between how Access and SQL Server assign AutoNumbers (or identities)?
A. Access assigns the ID as soon as you enter any data, while SQL Server assigns it only after saving the record.
B. SQL Server assigns the ID immediately, while Access waits until the record is saved.
C. Both assign the ID at the same time, but with different numbers.
D. Access does not assign AutoNumbers; only SQL Server does.

Q2. What additional parameter must be included when declaring a recordset in Access linked to a SQL Server backend to avoid errors?
A. dbOpenSnapshot
B. dbSeeChanges
C. dbReadOnly
D. dbAutoRefresh

Q3. When copying a record in Access linked to SQL Server, when can you retrieve the new record's ID?
A. Immediately after rs.AddNew
B. After rs.Update, before closing the recordset
C. Before setting any field values
D. After closing and reopening the database

Q4. Why might your code from Access-only applications not work with SQL Server as a backend?
A. Because SQL Server uses a different version of VBA
B. Because Access and SQL Server assign identity (AutoNumber) values at different times in the record-editing process
C. Because SQL Server does not support recordsets
D. Because Access tables cannot be linked to SQL Server

Q5. What is the primary function of the dbSeeChanges option when working with SQL Server linked tables in Access?
A. It enables read-only mode for the recordset
B. It allows changes to be tracked and prevents data conflicts
C. It makes sure that updates work correctly with identity fields in SQL Server
D. It automatically refreshes the recordset after saving

Q6. What should you do if you need to insert a parent record and then its child records in a SQL Server backend using Access?
A. Insert the child records first, then the parent
B. Insert the parent record, do rs.Update, then retrieve the new ID before inserting child records
C. Insert all child and parent records simultaneously
D. Only insert the parent record and ignore the children

Q7. What scenario is being specifically addressed in this video addendum?
A. Exporting Access data to Excel
B. Copying a record in Access with a SQL Server backend and retrieving its new ID
C. Securing Access databases with passwords
D. Sending email notifications from Access

Answers: 1-A; 2-B; 3-B; 4-B; 5-C; 6-B; 7-B

DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.

Summary

Today's video from Access Learning Zone is an addendum to the Developer 24 course. Two years ago, I mentioned in lesson three - about seven minutes in - that the code I provided for copying a record would not work correctly if you use SQL Server as a backend. That question has finally come up, and I want to clarify the steps for everyone working with SQL Server tables linked to Access.

The goal in Developer 24 was to show how to copy a record, which involves grabbing the new order ID as soon as it is created. In a standard Access database, whenever you start entering data into a new record, Access immediately assigns an AutoNumber to that record. For instance, if you have a customer table and type something into the new row, the AutoNumber field is set right away.

However, SQL Server does things differently. I keep an Access database linked to my SQL Server for site maintenance, and this database includes a notification table. In SQL Server, AutoNumbers are known as identities. When you are working in Access linked to SQL Server, entering data into a new record does not immediately assign that identity number. Instead, the number is assigned only after you finish entering required values and save the record - usually when you move off the row. In other words, you must complete the entry and save it before SQL Server assigns the identity value.

This difference means that code designed for Access alone will not behave as expected on a SQL Server backend. When using VBA to add a new record to a SQL Server table, you need a couple of modifications in your code. First, when declaring your recordset, you must include two additional arguments: making it editable and specifying the dbSeeChanges option. I cover this topic in much more detail in my Access to SQL Server seminar, which I will link below.

After setting up your recordset with dbSeeChanges, you can add a new record as usual. You have to fill in any required fields and then execute the update operation. It is only after this update that you can retrieve the new identity value from SQL Server. At this stage, you can store or display that new ID. If you need to perform more operations, such as adding child records like in the Developer 24 example, you can now use that newly obtained ID in any follow-up queries or actions.

In summary, the key difference when working with SQL Server as your backend is that identity values (AutoNumbers) are not assigned until the record is saved. Your code must account for this by saving the record before trying to retrieve the new ID.

You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.

Topic List

Difference between AutoNumber in Access and Identity in SQL Server
Copying records with Access as the backend
How Access assigns AutoNumber IDs immediately
Creating and editing records in a SQL Server linked table
When SQL Server assigns identity values
Required steps to retrieve new identity value in SQL Server
Using dbOpenDynaset and dbSeeChanges options in recordsets
Process of adding and updating records in SQL Server from Access
Retrieving the new identity value after rs.Update
Using the new ID to insert child records

Article

When working with Microsoft Access as your front end and SQL Server as your backend, managing AutoNumber fields, which are called identity columns in SQL Server, can be a bit different than what you might be used to with a pure Access database. If you have been following along with an Access developer tutorial and are trying to copy records and retrieve the new record ID, there are a few important points to understand.

In a standard Access database, when you add a new record to a table with an AutoNumber primary key, as soon as you type anything into that new row, Access immediately assigns a new AutoNumber value. For example, if you have a Customer table, simply typing a character into the first field will instantly generate the new CustomerID value. This makes it easy to grab that ID right away for use elsewhere in your VBA code.

SQL Server works differently. When you link an Access frontend to a SQL Server backend, the identity value is not assigned until the entire record is saved. This means that when you begin to add a new record and enter values, you will not see the new NotificationID (or whatever your identity column is called) until you finish editing the record and move off of it, which causes Access to save the changes to SQL Server. Only at this point does SQL Server assign the new identity value.

This behavior affects the process of copying records and getting the new ID. If you use code designed for Access-only databases, which expects the ID to be available immediately after calling AddNew, it will not work right with SQL Server.

To handle this correctly, your VBA code for working with a recordset must use two extra options when opening the recordset. When using DAO, you need to specify dbOpenDynaset to allow editing, and you must add the dbSeeChanges argument so that Access properly handles the back end. Here is how you can declare your recordset:

Set rs = db.OpenRecordset("YourTable", dbOpenDynaset, dbSeeChanges)

After opening the recordset, you can add a new record as usual with rs.AddNew. Set all required fields for the record so that you can save it. If only CustomerID is required, just set that field. Then, call rs.Update to save the record to the database.

At this point, and only after updating and before closing the recordset, you can retrieve the new identity value by referencing the primary key field of the recordset, such as rs!NotificationID or rs!YourIDField. For example:

Dim newID as Long
Set rs = db.OpenRecordset("NotificationT", dbOpenDynaset, dbSeeChanges)
rs.AddNew
rs!CustomerID = 123
' set other required fields as needed
rs.Update
newID = rs!NotificationID
rs.Close

Once you have the new ID value, you can close the recordset. If you need to do more work, such as inserting child records that reference the new parent record, you can now open another recordset or execute additional queries using the newID you just retrieved.

Remember, in SQL Server, the AutoNumber (identity) is only available after saving the record. You must use dbSeeChanges when dealing with linked SQL Server tables, and always grab the new ID after calling rs.Update. This approach ensures that your Access VBA code will work properly when interacting with SQL Server backends, and allows you to reliably copy records and retrieve their new primary keys for further processing.