How to insert data to a database with Access AutoNumber Field using VBA

SQL Command to Insert Data to a Table with AutoNumber Field

Access creates its own Primary Key, which is an AutoNumber field – no trouble if you are inserting record directly. On the other hand, if you insert through SQL Query we need to be bit careful. We cannot insert data to AutoNumber field to preserve its sanctity and an insert with only values will throw the “Number of query values and destination fields are not the same. “ error. To avoid this use the Insert with Field Name – Value Combination

Insert Statement without Field Name

oCm.CommandText = "Insert Into SampleTable Values ('" & sName & "','" & sLocation & "')"

Insert Statement with Field Names and Values

oCm.CommandText = "Insert Into SampleTable (UserName, Location) Values ('" & sName & "','" & sLocation & "')"

Here is the design of our Sample Table




“Number of query values and destination fields are not the same. “ error.