Inserting From Excel Number of Query Values and Destination Fields are not the same (1 Viewer)

[TimJC]

Hello all.

I am just beginning to manipulate Access from Excel.

I am trying to insert a row into a table. I am adding values for every field (8) except an Autonumber.

I have 2 Insert strings which are identical except for the fact that the one that doesn't work, doesn't specify which fields I am entering data into, which I presume shouldn't be necessary in this case.

When I try to use the second version I receive the error message...

Number of Query Values and Destination Fields are not the same.

This is baffling me and I wonder if anyone can see why I'm getting this problem.

The string that works perfectly is:-

strSQL = "INSERT INTO Clubs (ClubNumber,ClubName,ClubGrade,ClubRegion,ClubPosition,ClubHasHistory,clubinleague,cluboriginalposition) VALUES (" & clubCount + 1 & ",'" & lstrNewClubName & "'," & lintNewClubGrade & "," & lintRegion & "," & 0 & "," & vbFalse & ",'" & lstrNo & "'," & 10 & " )"

The one that generates the error message, which is identical except that I have removed the list of fields is:-

strSQL = "INSERT INTO Clubs VALUES (" & clubCount + 1 & ",'" & lstrNewClubName & "'," & lintNewClubGrade & "," & lintRegion & "," & 0 & "," & vbFalse & ",'" & lstrNo & "'," & 10 & " )"

The execute command is:-

gcnConnection.Execute strSQL, , adCmdText + adExecuteNoRecords

Any advice gratefully received.

 

[TimJC]

I've found a very similar query on another forum.

The answer suggested there is that when you have an autonumber field you have no option but to name the fields that you are updating.

That's a bit disappointing but if that's the way it is, that's the way it is (unless you know better!):banghead: