Increment the Primary Key from Excel

[Philosophaie]

This is VBA from Excel. I want to know how to increment the [ID] for each value of the range.

I have tried "NULL".

I have tried adding "n=n+1" after the "for each" and "thisSQL = "INSERT INTO... VALUES (" & n & ",..."

For Each value In Range("D5:D" & Cells(Rows.Count, "D").End(xlUp).Row)
        accDate = Cells(value.Row, 1)
        accDesc = Cells(value.Row, 2)
        accData = Cells(value.Row, 3)
        thisSQL = "INSERT INTO [Table1] ([ID], [Date], [Desc], [Data]) VALUES ("What goes here? ", #" & Format(CDate(accDate), "MM/DD/YY") & "#, '" & accDesc & "', " & accData & ");"
        conn.Execute CommandText:=thisSQL
Next

[JoeM]

Where is the initial starting ID value coming from?
If this ID number is just an AutoNumber in your Access table, you don't need to account for it in your INSERT statement, Access will take of it automatically. Just take it out of your INSERT INTO and VALUES clauses altogether.

[Philosophaie]

I took out the [ID] in fields and in VALUES - Error code:

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

If some please could tell me what I can put in "What goes here? " (above).

The [ID] field must have some property that I may be overlooking.

[JoeM]

We can't tell you that until you tell us where the ID value is coming from (see my previous post)!

Is it an Autonumber, or is the initial/starting value being pulled from somewhere?
If it is being pulled from somewhere, then where?
Or do you just want it to start at 1?

[Philosophaie]

[ID] is the primary key it is first of all the fields which I pre-programmed in Table1. I can not get it to autonumber:

thisSQL = "INSERT INTO [Table1] ([Date], [Desc], [Data]) VALUES (#" & Format(CDate(accDate), "MM/DD/YY") & "#, '" & accDesc & "', " & accData & ");"

This give this error: "Number of query values and destination fields are not the same."

n=n+1
thisSQL = "INSERT INTO [Table1] ([ID], [Date], [Desc], [Data]) VALUES (" & n & ", #" & Format(CDate(accDate), "MM/DD/YY") & "#, '" & accDesc & "', " & accData & ");"

This give this error: "Syntax error in INSERT INTO statement."

I deduce that " & n & " is the source of the error.

[JoeM]

If it is an Autonumber, leave it out completely. Access will default it and handle it on its own, no need to include it in the INSERT statement, i.e.

thisSQL = "INSERT INTO [Table1] ([Date], [Desc], [Data]) VALUES ("#" & Format(CDate(accDate), "MM/DD/YY") & "#, '" & accDesc & "', " & accData & ");"

[Philosophaie]

Error in your autonumber:


thisSQL = "INSERT INTO [Table1] ([Date], [Desc], [Data]) VALUES (#" & Format(CDate(accDate), "MM/DD/YY") & "#, '" & accDesc & "', " & accData & ");"


This give this error: "Number of query values and destination fields are not the same."

I had already pre-programmed the fields into Access. Tried without pre-programming, got error.

[JoeM]

After you set the thisSQL variable, if you run:

MsgBox thisSQL

what exactly does it return?
If you post it here, we can analyze your code to see if you are building valid syntax.

[Philosophaie]

Thank you. I got it to work.