INSERT INTO Execute VBA

Question

I want this SQL statement to execute. But with the CurrentDb.Execute I get error 3061 (Too few parameters. Expected 9). The values given in the variables are legit.

This is my table:

This is my Access form:

And here is my code:

updStr = "INSERT INTO Movie(movie_id, title, duration, description, publication_year, cover_image, previous_part, price, URL)" & _
            " VALUES(movie_id = " & Me.TxtMovId & ", title = '" & Me.TxtTitle & "'," & _
            " duration = " & Me.TxtDur & ", description = '" & Me.TxtDescr & "'," & _
            " publication_year = " & Me.TxtPubl & ", cover_image = " & Me.TxtImage & "," & _
            " previous_part = " & Me.TxtPrev & ", price = " & Me.TxtPrice & ", URL = '" & Me.TxtUrl & "')"

CurrentDb.Execute (updStr)

The DoCmd.RunSQL does execute the string, but it has no effect, that's the reason for using CurrentDb.Execute.

There may be more than one issue involved here, but check out this one first. Good Access syntax: INSERT INTO Movie(movie_id) VALUES (27); Bad Access syntax: INSERT INTO Movie(movie_id) VALUES (movie_id=27); After you get that issue sorted, consider a parameter query. — HansUp
– HansUp, Commented Dec 29, 2015 at 16:43

Community · Accepted Answer · 2017-05-23 11:59:05Z

@Hansup 's comment explains the issue: don't include the field names in the VALUES clause of an Access INSERT.

Also, something I learned on Stack Overflow that I like to share is that if you're going to have a query in your code, you should use parameterized queries to help prevent someone from accidentally messing up your databases or some kid from trying to see if he can drop your tables by inserting commands in text fields. Here's a Stack Overflow link on it Parameterized Queries .

And here is a brief example using a few of your fields:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strInsert
strInsert = "INSERT INTO Movie(movie_id, title, duration)" & vbCrLf & _
    "VALUES(pMovie_id, pTitle, pDuration)"
Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, strInsert)
qdf.Parameters("pMovie_id").Value = Me.TxtMovId.Value
qdf.Parameters("pTitle").Value = Me.TxtTitle.Value
qdf.Parameters("pDuration").Value = Me.TxtDur.Value
qdf.Execute dbFailOnError

I upvoted his answer, don't know how to mark his answer as question (yet). Thanks for bringing that point to my attention. I'll try to fix that, hadn't thought about that! Thanks!

Collectives™ on Stack Overflow

INSERT INTO Execute VBA

1 Answer 1

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Linked

Related