Execute query - too few parameters

Hi,

Getting error 3061 - "Too few parameters. Expected 10".

The query should roll back on error, and appears too, but when I check my
database, the query has been executed. There are 11 fields in the table, but
the first one is an auto_generated number, so I wasn't including that in the
query.

Would this have anything to do with the fact that I am using linked tables
(linking to a MySQL database)? If so, could someone point me in the right
direction as to how to set up my queries and rollback transactions?

Thanks,

J Gold

Private Sub addPublication(pubTitle As String, publisher As String, urlLINK
As String, _
pubType As String, pubStatus As String, pubAuthor As
String, _
pubPages As String, pubVolume As String, publishDate
As Date, _
pubPresentedAt As String)

Dim wrk As DAO.Workspace: Set wrk = DBEngine(0)
Dim dbP As DAO.Database: Set dbP = wrk(0)
Dim dbPB As DAO.Database: Set dbPB = wrk(0)
Dim rs As DAO.Recordset

Debug.Assert Not (wrk is Nothing)
wrk.BeginTrans
On Error GoTo trans_Err

dbP.Execute "INSERT INTO Publications (pubTitle, publisher, urlLINK," _
& "pubType, pubStatus, pubAuthor, pubPages, pubVolume,
publishDate, pubPresentedAt) " _
& "VALUES (pubTitle, publisher, urlLINK, pubType, pubStatus,
pubAuthor, " _
& "pubPages, pubVolume, publishDate, pubPresentedAt);",
dbFailOnError

'Commit the transaction
wrk.CommitTrans dbForceOSFlush

trans_Exit:
'Clean up
wrk.Close
Set dbP = Nothing

Exit Sub

trans_Err:
'Roll back the transaction
wrk.Rollback
Resume trans_Exit

End Sub

The references to the variables need to be outside of quotes.

dbP.Execute "INSERT INTO Publications (pubTitle, publisher, urlLINK," _
& "pubType, pubStatus, pubAuthor, pubPages, pubVolume, "_
& "publishDate, pubPresentedAt) " _
& "VALUES (" & pubTitle & ", " & publisher & ", " & urlLINK & ", " _
& pubType & ", " & pubStatus & ", " & pubAuthor & ", " _
& pubPages & ", " & pubVolume & ", " & publishDate & ", " &
pubPresentedAt);", _
dbFailOnError

That assumes that everything is numeric. From the names, I assume pubTitle
is text, and publishDate is date. You need to use quote delimiters for text
values and # delimiters for dates:

dbP.Execute "INSERT INTO Publications (pubTitle, publisher, urlLINK," _
& "pubType, pubStatus, pubAuthor, pubPages, pubVolume, "_
& "publishDate, pubPresentedAt) " _
& "VALUES ('" & Replace(pubTitle, "'", "''") & "', " _
& publisher & ", " & urlLINK & ", " _
& pubType & ", " & pubStatus & ", " & pubAuthor & ", " _
& pubPages & ", " & pubVolume & ", " _
& Format(publishDate, "\#yyyy\-mm\-dd\#") & ", " & pubPresentedAt);",
_
dbFailOnError

Exagerated for clarity, that VALUES line is

& "VALUES ( ' " & Replace(pubTitle, " ' ", " ' ' ") & " ' , " _

The reason for the Replace function there is to handle the case where the
title has apostrophes in it.


--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


"j_gold" <jgold.TakeThisOut@discussions.microsoft.com> wrote in message
news:C9BA7593-FBD4-479D-9A21-9F54CB1FA7EC@microsoft.com...
> Hi,
>
> Getting error 3061 - "Too few parameters. Expected 10".
>
> The query should roll back on error, and appears too, but when I check my
> database, the query has been executed. There are 11 fields in the table,
> but
> the first one is an auto_generated number, so I wasn't including that in
> the
> query.
>
> Would this have anything to do with the fact that I am using linked tables
> (linking to a MySQL database)? If so, could someone point me in the right
> direction as to how to set up my queries and rollback transactions?
>
> Thanks,
>
> J Gold
>
> Private Sub addPublication(pubTitle As String, publisher As String,
> urlLINK
> As String, _
> pubType As String, pubStatus As String, pubAuthor
> As
> String, _
> pubPages As String, pubVolume As String,
> publishDate
> As Date, _
> pubPresentedAt As String)
>
> Dim wrk As DAO.Workspace: Set wrk = DBEngine(0)
> Dim dbP As DAO.Database: Set dbP = wrk(0)
> Dim dbPB As DAO.Database: Set dbPB = wrk(0)
> Dim rs As DAO.Recordset
>
> Debug.Assert Not (wrk is Nothing)
> wrk.BeginTrans
> On Error GoTo trans_Err
>
> dbP.Execute "INSERT INTO Publications (pubTitle, publisher, urlLINK," _
> & "pubType, pubStatus, pubAuthor, pubPages, pubVolume,
> publishDate, pubPresentedAt) " _
> & "VALUES (pubTitle, publisher, urlLINK, pubType, pubStatus,
> pubAuthor, " _
> & "pubPages, pubVolume, publishDate, pubPresentedAt);",
> dbFailOnError
>
> 'Commit the transaction
> wrk.CommitTrans dbForceOSFlush
>
> trans_Exit:
> 'Clean up
> wrk.Close
> Set dbP = Nothing
>
> Exit Sub
>
> trans_Err:
> 'Roll back the transaction
> wrk.Rollback
> Resume trans_Exit
>
> End Sub