Must you INSERT INTO PrimaryKey before UPDATING Fields?

[biocentrism]

Hello

I am new to SQL programming.

My question is when populating a new row in a database, I have found that you must first INSERT INTO PrimaryKey(s) first and then you can UPDATE the other Fields. I have found you must do this in 2 steps. Is this true?

'this works
SQL = "INSERT INTO myTable (thePrimaryKeyField) VALUES (value1)"
Call UpdateDatabaseData(SQL)
SQL = "UPDATE myTable SET theNonPrimaryKeyField = value2" 
Call UpdateDatabaseData(SQL)

'this generates an error
SQL = "INSERT INTO myTable (thePrimaryKeyField, theNonPrimaryKeyField) VALUES (value1,value2)"
Call UpdateDatabaseData(SQL)

I was hoping you could create a new row by populating the PrimaryKey and other Fields at the same time within the same SQL statement?

When I try to do it at the same time I get an error so unless there is some other error in my code that I cannot find, this is the only reason I can think of.

Thank you.

[emihir0]

You should be able to this in 1 step without problem, at least I never had a problem doing it so.

Is your PK an auto-number field? If so, you do not need to mention it in an INSERT statement as it will increment automatically.

Other than that, you can go to Create Query > SQL view and then you can test your SQL statements here (like INSERT...) whether they work or not.
Sometimes when I make complicated SQL statements in my VBA I simply put Debug.Print instead of CurrrentDb.Execute in front of the SQL statement and then copy copy & paste it into the query in SQL view to see if it works as intended.

[NTC]

rather than reply with sql syntax - typically it is easier to discuss this in terms of the Access query objects; for which fundamentally one has 3 kinds: select, append, update. When you run an append query that adds new records to a table - then the table's properties dictate what must occur. If the table has no key field and no field with a 'required' mandatory property - this will liberally accept a new record. A table with a key field that is set up to be an autonumber - will automatically insert the key field for you - so your append query needs only other non key fields. While a table with a key field (which is mandatory) but not set to autonumber - then of course you must include a valid key value.

The append query allows the entire record (all fields) to be included - it is not a 2 step process, so one does not first have to just append the key field and then update blank fields to values - which seems to be your basic question.

If you are struggling with the vba implementation due to sql syntax - I recommend you implement via query objects instead. It is much easier to test. Then you can option the query design view to be SQL view and inspect the sql syntax.