Syntax error with INSERT INTO Access vba

Question

When I run the following code, I receive syntax error with INSERT INTO statement. Prior to executing the DoCMD step, the locals window shows the value of valuestring to be a string with a value of "1/4/2016"

I assume the error has to do with a string being entered into a date field, but not sure how to fix it.

Formdate is formatted as a date and data1 is not declared, though it shows up properly in the locals window as a date, #1/4/2016#

    Public Sub Import2(FileName As Variant)
    Dim wb As Object, ws As Object
    Dim xl As Object
    Set xl = CreateObject("excel.Application")
    Dim qs As String
    Dim ValueString As String


    'opens workbook, populates data1, etc.
    Set wb = xl.Workbooks.Open(FileName)
            Set ws = wb.worksheets("For Export")
            data1 = ws.cells(2, 1)
            Data2 = ws.cells(2, 2)
            Data3 = ws.cells(2, 3)
      ValueString = "(" & data1 & ")"
    qs = "INSERT INTO MAF (FormDate) VALUES & valuestring"
    DoCmd.RunSQL qs
    'CurrentDb.Execute qs

I don't know VBA, but it looks like your `& valuestring` is inside the string, not attaching your variable? Also if VBA is case sensitive in variable names (I don't know whether it is) it would have to be `ValueString` — Pekka, Jan 28 '16 at 13:08
[How to debug dynamic SQL in VBA](http://stackoverflow.com/a/1099570/3820271) -- you will see the problem immediately. — Andre, Jan 28 '16 at 13:14
As @Pekka said (and probably Andre, though haven't followed the link) - your variable is within the string. It's not case sensitive and if entered correctly as `qs = "INSERT INTO MAF (FormDate) VALUES " & valuestring` would have immediately updated to show ValueString. You may run into further problems with date formatting next - depending on your locality settings. — Darren Bartrup-Cook, Jan 28 '16 at 14:09

score 0 · Answer 1 · answered Jan 28 '16 at 14:15

0

Modify like this:

Data1 = ws.cells(2, 1)
ValueString = Format(Data1, "yyyy\/mm\/dd")
qs = "INSERT INTO MAF (FormDate) VALUES (#" & valuestring & "#)"

answered Jan 28 '16 at 14:15

Gustav

53,498
7
29
55

I tried this, and it got rid of the error, but nothing was appended into the MAF table. I got the popup saying "You are about to append 1 record", but nothing else. Is there a problem with my Insert command? – Robert Kendall Jan 28 '16 at 14:42
No, it looks OK. Of course, Excel must know of a table _MAF_ for this to work. – Gustav Jan 28 '16 at 14:47
Maybe that's the problem. The table is an Access, but I'm importing the data from Excel. do I need to specify something different instead of just MAF? – Robert Kendall Jan 28 '16 at 15:06
You do need a connection to the database - the _mdb_ or _accdb_ file. – Gustav Jan 28 '16 at 15:11
Thanks, I'll do some research on that. You've been extremely helpful – Robert Kendall Jan 28 '16 at 15:21

Syntax error with INSERT INTO Access vba

1 Answers1