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<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
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