I am getting errors trying to use INSERT INTO using VBA in Access, and can't fix it

Question

With the following code I get

error 3061 (Too few parameters. Expected 2)

Option Compare Database

Private Sub Command0_Click()
    Dim xls     As Excel.Application
    Dim wkb     As Excel.Workbook
    Dim wks     As Excel.Worksheet





    Set xls = New Excel.Application

    xls.Visible = True
    xls.UserControl = True

    Set wkb = xls.Workbooks.Open("link\Reliability Projects v5.xlsm", ReadOnly:=True, UpdateLinks:=False)
    Set wks = wkb.Worksheets("Projects")




    Dim RWPTitle As String 'there may be a better type to use here
    RWPTitle = wks.Range("E10").Value
    Text1.Value = RWPTitle
    Dim EstimatedCapital As Currency 'there may be a better type to use here
    EstimatedCapital = wks.Range("J10").Value
    CurrentDb.Execute "INSERT INTO Table1 (PlanTitle, EstimatedCapitalCost) Values (RWPTitle, EstimatedCapital)"








    wkb.Close False 'Close workbook.  False is so that it doesn't save


    Set wks = Nothing
    Set wkb = Nothing

    xls.Quit

    Set xls = Nothing
End Sub

*When I try to use the INSERT INTO directly (No CurrentDb.EXECUTE) I get a

Compile Error - Expected:end of statement

and it highlights the Table name used.

Any ideas as to why this is happening to me?

Additionally, I am very new to VBA & Access. Any good references for functions / calls used to deal with databse and with excel files / info?

braX · Accepted Answer · 2017-11-14 23:27:19Z

2

First thing I see is that your INSERT is going to fail because you are using your variables like they are parameters. - It should look like this, with the string in single quotes, and the currency not:

    CurrentDb.Execute "INSERT INTO Table1 (PlanTitle, EstimatedCapitalCost)
                      Values ('" & RWPTitle & "', " & EstimatedCapital & ")"

edited Nov 14, 2017 at 23:27

answered Nov 14, 2017 at 20:18

braX

11.9k5 gold badges24 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

J.Ryan Over a year ago

For the Insert Into do I need to list every field within the first parentheses that exists in the table, followed by the values for each field (or "" to enter nothing), or can I just list the fields for which I have values, followed by those values?

braX Over a year ago

You do not need to include all of the fields, just the ones required by your schema.

Hambone Over a year ago

@J.Ryan -- if this answer meets your needs, please mark it as correct/accepted. This will help others in the future who might have a similar issue because they will see that the question as an accepted solution

Andre · Accepted Answer · 2017-11-16 16:41:00Z

0

Here is how I like to handle inserts in vba. I can then trace any values that are in error.

With rstTable1

  .AddNew
    !PlanTitle,  = "Text Field Here"
    !EstimatedCapitalCost = $value here
  .Update

End With

edited Nov 16, 2017 at 16:41

Andre

27.7k7 gold badges43 silver badges85 bronze badges

answered Nov 16, 2017 at 16:25

Charles_D

114 bronze badges

Collectives™ on Stack Overflow

I am getting errors trying to use INSERT INTO using VBA in Access, and can't fix it

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related