INSERT INTO a table with an autonumber field

[Petrol]

I am having trouble with the following VBA statement:

     strSQL = "INSERT INTO Walk_participation_history " _
             & "(PersonID, WalkNumber, WalkDate, Posn_ID) " _
             & "SELECT (Me!Person_ID, Me!cboPilgrimWalk, Walk_Date, 0);"
    Set dbs = CurrentDb
    dbs.Execute strSQL
    Set dbs = Nothing

The primary key of the target table is an autonumber field, TSH_ID (not PersonID!). When I execute the SQL I get the debug.print

INSERT INTO Walk_participation_history (PersonID, WalkNumber, WalkDate, Posn_ID) VALUES (Me!Person_ID, Me!cboPilgrimWalk, Walk_Date, 0);

and the error message 3061, "Too few parameters. Expected 3".
A couple of earlier posts on this forum seem to suggest that this is the correct way to do it. The Access Help (docs.microsoft .com) says "If you append records to a table with an AutoNumber field and you want to renumber the appended records, do not include the AutoNumber field in your query. Do include the AutoNumber field in the query if you want to retain the original values from the field.". (I'm not sure what that means: There wouldn't be an original number for a record that I'm just adding).

I built a query manually to do this, and it worked when run from the main window. The SQL it generated was

INSERT INTO Walk_participation_history ( PersonID, WalkNumber, WalkDate, Posn_ID )
SELECT 22 AS Expr1, "GC100" AS Expr2, #10/18/2007# AS Expr3, 0 AS Expr4;

. However when I tried to put this into my VBA (with or without the "AS Expr" phrases) it also failed with the Too few parameters message.

[twinnyfo]

You need to remove your control references outside the quotation marks:

    strSQL = _
        "INSERT INTO Walk_participation_history " & _
        "(PersonID, WalkNumber, WalkDate, Posn_ID) " & _
        "SELECT (" & _
            Me!Person_ID & ", " & _
            Me!cboPilgrimWalk & ", " & _
            Walk_Date & ", " & _
            "0);"

I've done a bit of reformatting for you to see the changes more readily.

Hope this hepps!

[Petrol]

Oh my goodness, I don't know how many times I haven't been trapped by that tricky formatting! And usually I've got it sorted in the end, but this time I forgot again :-(.

I still haven't got it quite right, though. The problem is that Person_ID is an integer field, cboPilgrimWalk is a text field, Walk_Date is a date field, and the last parameter is of course just zero. I've tried several ways to form the SQL string but each time I now get
Error No. 3075, Syntax error (comma) in query expression '(2142, "GC100", #18/10/2007#, 0)'.
My latest try was

    strSQL = "INSERT INTO Walk_participation_history " _
             & "(PersonID, WalkNumber, WalkDate, Posn_ID) " _
             & "SELECT (" & Me!Person_ID & ", " _
             & Chr(34) & Me!cboPilgrimWalk & Chr(34) & ", #" _
             & Walk_Date & "#, " & "0);"

The debug.printouts show the various things I've tried:

INSERT INTO Walk_participation_history (PersonID, WalkNumber, WalkDate, Posn_ID) SELECT (2142,"GC100",18/10/2007,0);
INSERT INTO Walk_participation_history (PersonID, WalkNumber, WalkDate, Posn_ID) SELECT (2142, 'GC100', 18/10/2007,0);
INSERT INTO Walk_participation_history (PersonID, WalkNumber, WalkDate, Posn_ID) SELECT (2142, 'GC100', 18/10/2007, 0);
INSERT INTO Walk_participation_history (PersonID, WalkNumber, WalkDate, Posn_ID) SELECT (2142, 'GC100', #18/10/2007#, 0);
INSERT INTO Walk_participation_history (PersonID, WalkNumber, WalkDate, Posn_ID) SELECT (2142, "GC100", #18/10/2007#, 0);
INSERT INTO Walk_participation_history (PersonID, WalkNumber, WalkDate, Posn_ID) SELECT (2142, "GC100", #18/10/2007#, 0);

, but they all end up with Error No. 3075.

[twinnyfo]

Try this:

    strSQL = _
        "INSERT INTO Walk_participation_history " & _
        "(PersonID, WalkNumber, WalkDate, Posn_ID) " & _
        "SELECT (" & _
            Me!Person_ID & ", '" & _
            Me!cboPilgrimWalk & "', #" & _
            Format(Walk_Date, "yyyy-mm-dd") & "#, " & _
            "0);"

Just a thought

[lewish95]

If you do not want to provide values for all columns that exists in your table, you've to specify the columns that you want to insert. (Which is logical, otherwise how should access, or any other DB, know for which columns you're providing a value)?

So, what you have to do is this:

INSERT INTO MyTable ( Column2, Column3, Column4) VALUES ( 1, 2, 3 )
Also , be sure that you omit the Primary Key column (which is the autonumber field). Then, Access will set it to the next value by itself.

You can then retrieve the primary-key value of the newly inserted record by executing a

SELECT @@identity FROM MyTable
statement.
I hope this helps!
Admin

[NeoPa]

Petrol:
The Access Help (docs.microsoft .com) says "If you append records to a table with an AutoNumber field and you want to renumber the appended records, do not include the AutoNumber field in your query. Do include the AutoNumber field in the query if you want to retain the original values from the field.". (I'm not sure what that means: There wouldn't be an original number for a record that I'm just adding).

There are two variations of the syntax for INSERT INTO. The one you should probably be using is with the VALUES() clause. Ths one, using the SELECT caluse, is designed for copying records from one table to another.

Generally speaking, even when copying records, you would still want new records to be assigned an identity automatically. However, consider the situation where you want to back up your data - either to a separate table ( :-( ) or a separate database. It is often critical that the original ID is perpetuated when a record is brought back. This is why the Help System covers that possibility. In your situation I suspect you've left this out already, which would be perfectly correct.

Now on to your main question. This is a great illustration of where following the guidance in How to Debug SQL String really comes into its own. Well done. Seeing the actual SQL string made dealing with the question so much easier for those of us that do have that experience.

In this case I suspect you're all but there with Twinny guiding but I'll continue to monitor so do explain if there's anything you're still stuck with.

I've just checked Twinny's last post and it certainly seems to me to be a workable response.

[NeoPa]

Hi Lewis.
Thank you for your post.

LewisH95:
You can then retrieve the primary-key value of the newly inserted record by executing a

SELECT @@identity FROM MyTable
statement.
I hope this helps!
Admin

Are you sure that this particular part was intended for use within Access. I believe that's SQL Server specific, but that's not to say it isn't available in other RDBMS servers. I'm pretty sure it doesn't work in Access though.

Also, there's no real issue with your signing yourself as Admin (At least I have none & I'm the actual Admin here. Niheel is the owner though & he will have his own opinion.) but do be aware that most will interpret that to mean Admin here at Bytes.com rather than wherever it is you're admin of. Probably worth modifying for posts here.

[NeoPa]

Hi Petrol.
Back to your question. I find it extremely helpful to have a Function procedure that handles formatting date literals (as opposed to references to dates) for me into something recognisable by SQL anywhere. See Literal DateTimes and Their Delimiters (#) for how different country standards can affect how date & time literal data can be interpreted.

I use the following in my projects to make my life much simpler :

'SQLDate takes varDate in Date/Time format and returns it
'formatted for use in SQL.  If blnHash then puts '#'s around it.
'2015-04-26 Updated to support SQL Server format of yyyy-m-d H:m:s.
Public Function SQLDate(ByVal varDate As Variant _
                      , Optional blnHash As Boolean = True) As String
    If IsEmpty(varDate) Or varDate = "" Then Exit Function
    If IsDate(varDate) Then
        varDate = CDate(varDate)
        If TimeValue(varDate) > 0 Then
            SQLDate = Format(varDate, IIf(DateValue(varDate) > 0 _
                                        , "yyyy\-m\-d ", "") & "HH\:nn\:ss")
        Else
            SQLDate = Format(varDate, "yyyy\-m\-d")
        End If
    End If
    If blnHash And SQLDate > "" Then SQLDate = "#" & SQLDate & "#"
End Function

You'll see it handles dates, times & date/times and converts them into literal values recognised by all versions of SQL I know of.

[Petrol]

Yay! It works!
I (and scores of other non-experts) am really blessed to have access to a group of experts who are not only competent but also willing to share their expertise. Thank you.
I should do you the honour of replying to each individually, but first, here's the code that finally worked:

        "INSERT INTO Walk_participation_history " & _
        "(PersonID, WalkNumber, WalkDate, Posn_ID) " & _
        "VALUES (" & _
            Me!Person_ID & ", '" & _
            Me!cboPilgrimWalk & "', #" & _
            Walk_Date & "#, " & _
            "0);"

Surprisingly, it compiled into the correctly formatted date that Twinny had recommended - even without the Format function.
I tried initially Twinny's code exactly as given, and it still yielded the syntax error (3061). Then I switched SELECT to VALUES as suggested by Lewis and NeoPa and it worked.

Actually, I had started our by using VALUES as per the Microsoft documentation, but by the time of my OP had swapped to using SELECT because that's what the Append query I had constructed had compiled into.

So it seems that VALUES re-formats the data and SELECT doesn't. Note that it's not a date literal I am using, but a reference to a variable Walk_Date (which is extracted by DLookup from a separate table with Walk details and dates in it).

Thanks again, everybody.

[Petrol]

Once again, I am trapped by the need to choose only one answer as the "best", when it was a combination of expertise from several answers that finally solved the problem. But it was Twinny's pointing out that I was messing up the parameters, plus his/her suggestion to format the date, that led to the final resolution. But it still didn't quitee work because I had been fooled by SQL (and by a much earlier post on this forum)into swapping the VALUES clause which the Access docco specified to a SELECT clause.

[Petrol]

Thanks, Lewis. Yes, I had originally tried VALUES, and that's what finally worked, but I'd been misled by other errors in my code. I don't actually need to retrieve the auto-numbered ID field, and had originally omitted it.

[Petrol]

Hi NeoPa. Your informative articles on debugging SQL strings and formatting date literals are helpful - thanks for the references. I'm actually using a date reference, so I don't think that one applies here, but I need to spend some time in studying it later.

Thanks, too, for explaining the bit in the docco about "retaining the original numbers". I had always thought that there was no way of changing or specifying any value other than the next sequential number that an auto-numbered field gives.

[NeoPa]

Hi Petrol. You did a good job selecting the Best Answer ;-)

I should point out though, that whether you use the VALUES or SELECT clause won't have any effect on how the SQL is formatted. I'm not sure what convinced you otherwise but I would suggest it was born of a confusion somewhere. The important point is that it works though.

You're correct to conclude the formatting of dates only pertains to literal values embedded within the SQL. References do not need to be formatted. Indeed, references SHOULD not be formatted. This obviously holds true for the SQLDate() function usage too. It's there to make formatting the date literal easier and more reliable. Entirely useless for a reference.

[Petrol]

You're right, of course. The choice of VALUES/SELECT makes no difference to how the SQL is formatted. But the choice of key word in the SQL does make a difference to how it is executed.

strSQL = _
        "INSERT INTO Walk_participation_history " & _
        "(PersonID, WalkNumber, WalkDate, Posn_ID) " & _
        "SELECT (" & _
            Me!Person_ID & ", '" & _
            Me!cboPilgrimWalk & "', #" & _
            Walk_Date & "#, " & _
            "0);"

generated SQL

INSERT INTO Walk_participation_history (PersonID, WalkNumber, WalkDate, Posn_ID) SELECT (2145, 'GC100', #18/10/2007#, 0);

, which on execution failed with Error No. 3075, Syntax error (comma) ...
Whereas

strSQL = _
        "INSERT INTO Walk_participation_history " & _
        "(PersonID, WalkNumber, WalkDate, Posn_ID) " & _
        "VALUES (" & _
            Me!Person_ID & ", '" & _
            Me!cboPilgrimWalk & "', #" & _
            Walk_Date & "#, " & _
            "0);"

generated

INSERT INTO Walk_participation_history (PersonID, WalkNumber, WalkDate, Posn_ID) VALUES (2145, 'GC100', #18/10/2007#, 0);

which worked perfectly.

[NeoPa]

Very interesting, and a surprise to me. That caused me to have to go off and duplicate the situation. That deserves a "Thanks :-)" for the interesting conundrum, as well as a "Thanks!! :-(" (sarcastic) for the extra work it led to :-D

The answer, when I saw it, was obvious.
The syntax for SELECT & VALUES is different. The two that are equivalent are :

VALUES (2145, 'GC100', #18/10/2007#, 0)

SELECT 2145, 'GC100', #18/10/2007#, 0

Not :

SELECT (2145, 'GC100', #18/10/2007#, 0)

IE. The SELECT clause doesn't use parentheses but the VALUES clause does.