Troubleshoot a INSERT INTO statement in Excel

Philosophaie · Aug 21st 2012

Everything is functioning correctly except to "INSERT INTO" statement. Could someone help troubleshoot it with me.

Code

Dim conn As New ADODB.Connection
    conn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Users\Philosophaie\Documents\a  High Priority\a SQL\a Chase.accdb;"
    Dim value As Range
    For Each value In Range("E5:E" & Cells(Rows.Count, "E").End(xlUp).Row)
        n = n + 1
            accDateSub = Cells(value.Row, 2)
            accDescSub = Cells(value.Row, 3)
            accSub = -Cells(value.Row, 4)
            accRunTotal = Cells(value.Row, 5)
            accAdd = Cells(value.Row, 6)
            accDescAdd = Cells(value.Row, 7)
            accDateAdd = Cells(value.Row, 8)
        thisSQL = "INSERT INTO [a Chase Table] VALUES ([" & n & "], [#" & Format(accDateSub, "yyyy/mm/dd") & "#], ['" & accDescSub & "'], [" & accSub & "], [" & accRunTotal & "]), [" & accAdd & "], ['" & accDescAdd & "'], [#" & Format(accDateAdd, "yyyy/mm/dd") & "#]);"
        conn.Execute CommandText:=thisSQL
    Next

Display More

**norie** · Aug 21st 2012

Re: Troubleshoot a INSERT INTO statement in Excel

Have you examined thisSQL when all the variable values have been added?

It's hard for us to do that because we don't know what those values are.

Just looking at the code and the SQL one thing I do notice is that you appear to have square brackets round each value you are inserting.

Square brackets are normally used for field/table etc names not values.

dclark0699 · Aug 21st 2012

Re: Troubleshoot a INSERT INTO statement in Excel

Whatis the error message you are receiving?

Also, run it with a debug.print in it - as below- and post what is shown in the Immeadiate Window.

As norie said on first glance the brackets are at the very least unnecesary, may even be causing the problem. I also want to say ADODB has balked at me for having a semicolon at the end of my query

Code

[COLOR=#0000ff]Dim[/COLOR] conn [COLOR=#0000ff]As[/COLOR] [COLOR=#0000ff]New[/COLOR] ADODB.Connection 
conn.Open ConnectionString:="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\Users\Philosophaie\Documents\a  High Priority\a SQL\a Chase.accdb;" 
[COLOR=#0000ff]Dim[/COLOR] value [COLOR=#0000ff]As[/COLOR] Range 
[COLOR=#0000ff]For Each[/COLOR] value [COLOR=#0000ff]In[/COLOR] Range("E5:E" & Cells(Rows.Count, "E").End(xlUp).Row) 
    n = n + 1 
    accDateSub = Cells(value.Row, 2) 
    accDescSub = Cells(value.Row, 3) 
    accSub = -Cells(value.Row, 4) 
    accRunTotal = Cells(value.Row, 5) 
    accAdd = Cells(value.Row, 6) 
    accDescAdd = Cells(value.Row, 7) 
    accDateAdd = Cells(value.Row, 8) 
    thisSQL = "INSERT INTO [a Chase Table] VALUES ([" & n & "], [#" & Format(accDateSub, "yyyy/mm/dd") & "#], ['" & accDescSub & "'], [" & accSub & "], [" & accRunTotal & "]), [" & accAdd & "], ['" & accDescAdd & "'], [#" & Format(accDateAdd, "yyyy/mm/dd") & "#]);" 
debug.print thissql    
Exit For
conn.Execute CommandText:=thisSQL 
[COLOR=#0000ff]Next[/COLOR]

Display More

Philosophaie · Aug 21st 2012

Re: Troubleshoot a INSERT INTO statement in Excel

I ran it with Debug.Print thisSQL. Got a "Object Required" error

Here are my Fields:
ID, DateSub, DescSub, Sub, RunningTotal, Add, DescAdd, and DateAdd.
Eight in total.
Could someone show me where my syntax is Wrong.

Code

thisSQL = "INSERT INTO [a Chase Table] ([ID], [DateSub], [DescSub], [Sub], [RunningTotal], [Add], [DescAdd], [DateAdd]) VALUES (" & n & ", #" & Format(accDateSub, "yyyy/mm/dd") & "#, '" & accDescSub & "', " & accSub & ", " & accRunTotal & ", " & accAdd & ", '" & accDescAdd & "', #" & Format(accDateAdd, "yyyy/mm/dd") & "#);"

Is it possible the syntax for the Dates are wrong?

**norie** · Aug 21st 2012

Re: Troubleshoot a INSERT INTO statement in Excel

It might not be the syntax, especially since you seem to have got rid of the square brackets, it could be the values.

Hard to tell.

What might help is knowing the error message you get when you try and execute the query.

Philosophaie · Aug 21st 2012

Re: Troubleshoot a INSERT INTO statement in Excel

The error code is"

Quote

Run-time error'-2147217913(80040e07)':
Syntax error in date in query expression'#'.

**norie** · Aug 22nd 2012

Re: Troubleshoot a INSERT INTO statement in Excel

Try changing the date format to mm/dd/yy.

Troubleshoot a INSERT INTO statement in Excel

Participate now!

Share

Users Viewing This Thread