VBA SQL - Run-time error 3134 - Syntax Error in INSERT INTO statement 2

[kjv1611]

VBA SQL - Run-time error 3134 - Syntax Error in INSERT INTO statement

This is running MS Access 2003

I thought it was a fairly strait forward SQL statment, but apparently not?

Here is the code - a lot of different things going on here, but I'll highlight the portion with the error:

Option Compare Database
Option Explicit

Private Sub CombineTdfs()
    Dim db As DAO.Database
    Dim strSqlDel As String
    Dim strSqlApnd As String
    Dim strSqlFieldsTo As String 'Fields to append to
    Dim strSqlFieldsFrom As String 'Fields to append from
    Set db = CurrentDb
    Dim tdf As TableDef
    Dim fld As Field
    Dim bolDropCol As Boolean
    Dim bolSSN As Boolean 'test to see if table contains ssn field "SS#"
    
    
    bolDropCol = False
    
    For Each tdf In db.TableDefs
        bolSSN = False
        strSqlFieldsTo = "Date, [Case Number], [Plaintiff Last Name]," & _
                              " MI, [Plaintiff First Name]," & _
                              " County, State, [Plaintiff Counsel #1]," & _
                              " [Plaintiff Counsel #2], Defendant," & _
                              " [House #], [Street Name], City, Zip"
        strSqlFieldsFrom = "t.Date, t.[Case Number], t.[Plaintiff Last Name]," & _
                                 "t.MI, t.[Plaintiff First Name], " & _
                                 "t.County, t.State, t.[Plaintiff Counsel #1]," & _
                                 "t.[Plaintiff Counsel #2], t.Defendant, " & _
                                 "t.[House #], t.[Street Name], t.City, t.Zip"
        strSqlDel = "ALTER TABLE [" & tdf.Name & "]"
        strSqlApnd = "INSERT INTO [tblALL] "
        
        For Each fld In tdf.Fields
            If Len(fld.Name) = 3 Then
                If rgxValidate(fld.Name, "\w\d\d") Then
                    bolDropCol = True
                    strSqlDel = strSqlDel & " DROP COLUMN [" & fld.Name & "]"
                End If
                If fld.Name = "SS#" Then
                    bolSSN = True
                End If
            End If
        Next fld
        If bolDropCol = True Then
            db.Execute strSqlDel
            bolDropCol = False
        End If
        If bolSSN = True Then
            strSqlFieldsTo = strSqlFieldsTo & ", [SS#], [Court District]"
            strSqlFieldsFrom = strSqlFieldsTo & ", t.[SS#], '" & tdf.Name & "'"
        Else
            strSqlFieldsTo = strSqlFieldsTo & ", [Court District]"
            strSqlFieldsFrom = strSqlFieldsFrom & ", '" & tdf.Name & "'"
        End If
        strSqlApnd = strSqlApnd & "(" & strSqlFieldsTo & ") SELECT " & strSqlFieldsFrom & _
                          " FROM [" & tdf.Name & "] t"

        [HIGHLIGHT]db.Execute strSqlApnd[/HIGHLIGHT]
    Next tdf
    
End Sub

This is the actual SQL that is being produced which is giving the syntax error:

?strSqlApnd [GREEN]'Command in Immediate Window to show what the variable is currently storing[/GREEN]
INSERT INTO [tblALL] (Date, [Case Number], [Plaintiff Last Name], MI, [Plaintiff First Name], County, State, [Plaintiff Counsel #1], [Plaintiff Counsel #2], Defendant, [House #], [Street Name], City, Zip, [Court District]) SELECT t.Date, t.[Case Number], t.[Plaintiff Last Name], t.MI, t.[Plaintiff First Name], t.County, t.State, t.[Plaintiff Counsel #1], t.[Plaintiff Counsel #2], t.Defendant, t.[House #], t.[Street Name], t.City, t.Zip, [COLOR=WHITE][HIGHLIGHT BLUE]'Alabama Middle'[/HIGHLIGHT][/COLOR WHITE] FROM [Alabama Middle] t

My suspicion is that I'm doing something wrong with the part where I am wanting to add text. Basically, what I am wanting to do is include the table name for the table being appended FROM in the last column.

I tried testing this on SQL Server, and got no errors in just checking it. Of course, it won't run, b/c it is based on Access local tables, not SQL Server tables.

Any thoughts/suggestions?

Oh, and the rgxValidate is using a Function from here:

http://www.mvps.org/access/modules/mdl0063.htm

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Actually, I'm purty daw-gone sure that it's the thing in quotes... must be a special way to refer to that which I'm forgetting...

--

"If to err is human, then I must be some kind of human!" -Me

 

[Remou]

Date is a reserved word. Try [Date].

 

[kjv1611]

Thanks for that catch, but it didn't correct the error. Here is the current produced SQL:

INSERT INTO [tblALL] (Date, [Case Number], [Plaintiff Last Name], MI, [Plaintiff First Name], County, State, [Plaintiff Counsel #1], [Plaintiff Counsel #2], Defendant, [House #], [Street Name], City, Zip, [Court District]) SELECT t.Date, t.[Case Number], t.[Plaintiff Last Name],t.MI, t.[Plaintiff First Name], t.County, t.State, t.[Plaintiff Counsel #1],t.[Plaintiff Counsel #2], t.Defendant, t.[House #], t.[Street Name], t.City, t.Zip, 'Alabama Middle' FROM [Alabama Middle] t

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Errr, this when I wish I could delete a post - somehow I did not replace that yet... hold one..

--

"If to err is human, then I must be some kind of human!" -Me

 

[Remou]

That does not have [date]. I meant:

INSERT INTO [tblALL] ([red][Date][/red], [Case Number], [Plaintiff Last Name], MI, [Plaintiff First Name], County, State, [Plaintiff Counsel #1], [Plaintiff Counsel #2], Defendant, [House #], [Street Name], City, Zip, [Court District]) SELECT t.[red][Date][/red], t.[Case Number], t.[Plaintiff Last Name],t.MI, t.[Plaintiff First Name], t.County, t.State, t.[Plaintiff Counsel #1],t.[Plaintiff Counsel #2], t.Defendant, t.[House #], t.[Street Name], t.City, t.Zip, 'Alabama Middle' FROM [Alabama Middle] t

 

[kjv1611]

OK, well, I did update it, but it just didn't show in the immediate window when I tried earlier. So I gave another go, now I get a different error.

The SQL:

?strSqlApnd
INSERT INTO [tblALL] ([Date], [Case Number], [Plaintiff Last Name], MI, [Plaintiff First Name], County, State, [Plaintiff Counsel #1], [Plaintiff Counsel #2], Defendant, [House #], [Street Name], City, Zip, [Court District]) SELECT t.[Date], t.[Case Number], t.[Plaintiff Last Name],t.MI, t.[Plaintiff First Name], t.County, t.State, t.[Plaintiff Counsel #1],t.[Plaintiff Counsel #2], t.Defendant, t.[House #], t.[Street Name], t.City, t.Zip, 'Alabama Northern' FROM [Alabama Northern] t

The error:

Run-time error '3061':

Too few parameters. Expected 1.

Again, that error is on the line:

db.Execute strSqlApnd

--

"If to err is human, then I must be some kind of human!" -Me

 

[Remou]

Are you confindant of the field names? The error you show normally indicates a missing parameter or field.

 

[kjv1611]

Another interesting thing. It appears to complete one table, but then give the error on the next...

As for the field names, best I know they are all the same, except some have the SS# field while some do not, and some have additional fields that are just blank all the way down, and were imported as "F15, F16" etc - that's where I used the regular expressions filter to get rid of those..

I'll look again to be sure. I would be shocked if the error was from an incorrect field name..

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Wow, you've GOT to be kidding. Talk about NOT normalized tables at all!

Apparently, some have the field, Defendant, and some have the field Defendant(s)

Hopefully that'll be the last little hitch, but who knows.

I'll see if I can fix that part, and post back. Thanks for the thought, Remou!

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

...
        For Each fld In tdf.Fields
            If fld.Name Like "Defendant*" Then
                strSqlFieldsFrom = Replace(strSqlFieldsFrom, "t.Defandant", "[" & fld.Name & "]")
            ElseIf Len(fld.Name) = 3 Then
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Thanks, PHV, for that bit... I'll take a look at that as well.. seems I keep uncovering lots of itty bitty differences as I go with this thing.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

To update my current progress:
[OL][LI]I programmatically found, and manually updated the few variances in the SS# field (some were SS#, and I think 7 were SS#)[/LI]
[LI]Still getting an error in the SQL part:

[GREEN]'Line in code with error[/GREEN]
db.Execute strSqlApnd

[GREEN]'Generated SQL - made it look purtier for the post - easier to read[/GREEN]
INSERT INTO [tblALL]
   ([Date], [Case Number], [Plaintiff Last Name], [MI],
   [Plaintiff First Name], [County], [State], [Plaintiff Counsel #1],
   [Plaintiff Counsel #2], [Defendant], [House #], [Street Name],
   [City], [Zip], [Court District])
SELECT
   t.[Date], t.[Case Number], t.[Plaintiff Last Name], t.[MI],
   t.[Plaintiff First Name], t.[County], t.[State], t.[Plaintiff Counsel #1],
   t.[Plaintiff Counsel #2], t.[Defendant(s)],  t.[House #], t.[Street Name],
   t.[City], t.[Zip], 'Colorado'
FROM [Colorado] t

[GREEN]'Error Received[/GREEN]
Run-time error '3061':
Too few parameters. Expected 1.
[GREEN]'This error appears to only be occurring on some tables.[/GREEN]

[/LI]
[LI]Haven't tried PHV's bit yet.[/LI]
[/OL]

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

What happens if you execute the following code from the SQL view pane of the brand new query ?
SELECT
t.[Date], t.[Case Number], t.[Plaintiff Last Name], t.[MI],
t.[Plaintiff First Name], t.[County], t.[State], t.[Plaintiff Counsel #1],
t.[Plaintiff Counsel #2], t.[Defendant(s)], t.[House #], t.[Street Name],
t.[City], t.[Zip], 'Colorado'
FROM [Colorado] t

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[kjv1611]

Well, PHV, in doing that, I found that one of the fields was misspelled. There is a typo misspelling on one of the fields names. Again, obviously not normalized data before we got it! Goodness!

So, I'll see if I can fix all of those as well...

The field with the error is t.Plaintiff Counsel # 1

It's spelled "Plaintif..." in that one instead of Plaintiff..."

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Based on the way things keep progressing, I'm thinking I should have done it a little differently. Use "Like" commands to verify that the field is at least close each time, and then build the field list for the SQL statement as I loop through each field. That way, I should get the correct names, no matter what they are. I'll just try to be careful about how I do the "Like" part.

--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Okay, just as a follow-up, I finally worked through all the issues, and got it corrected. I had to manually fix a couple of things, thinking it wasn't worth doing it programmatically at this point, and then I had to add in a couple more filters to prevent the circular reference back to the "To:" table, "tblAll", and to prevent importing the system tables.

Here is the final code that worked:

Option Compare Database
Option Explicit

Private Sub CombineTdfs()
    Dim db As DAO.Database
    Dim strSqlDel As String
    Dim strSqlApnd As String
    Dim strSqlFieldsTo As String 'Fields to append to
    Dim strSqlFieldsFrom As String 'Fields to append from
    Set db = CurrentDb
    Dim tdf As TableDef
    Dim fld As Field
    Dim bolSSN As Boolean 'test to see if table contains ssn field "SS#"
    Dim x As Integer
    
    For Each tdf In db.TableDefs
        If tdf.Name Like "MSys*" Or tdf.Name = "tblAll" Then
            GoTo NextTdf
        End If
            
        strSqlFieldsFrom = vbNullString
        bolSSN = False
        strSqlApnd = "INSERT INTO [tblALL] "
        For Each fld In tdf.Fields
            strSqlDel = "ALTER TABLE [" & tdf.Name & "]"
            If Len(fld.Name) = 3 Then
                If rgxValidate(fld.Name, "\w\d\d") Then
                    strSqlDel = strSqlDel & " DROP COLUMN [" & fld.Name & "]"
                    db.Execute strSqlDel
                    GoTo NextFldAftDel
                End If
                If fld.Name = "SS#" Then
                    bolSSN = True
                End If
            ElseIf Len(fld.Name) = 7 Then
                If fld.Name Like "Field*" Then
                    strSqlDel = strSqlDel & " DROP COLUMN [" & fld.Name & "]"
                    db.Execute strSqlDel
                End If
            End If
            If strSqlFieldsFrom = vbNullString Then
                strSqlFieldsFrom = "[" & fld.Name & "]"
            Else
                strSqlFieldsFrom = strSqlFieldsFrom & " ,[" & fld.Name & "]"
            End If
NextFldAftDel:
        Next fld
        strSqlFieldsTo = "[Date], [Case Number], [Plaintiff Last Name]," & _
                              " [MI], [Plaintiff First Name]," & _
                              " [County], [State], [Plaintiff Counsel #1]," & _
                              " [Plaintiff Counsel #2], [Defendant]," & _
                              " [House #], [Street Name], [City], [Zip]"
        If bolSSN = True Then
            strSqlFieldsTo = strSqlFieldsTo & ", [SS#], [Court District]"
        Else
            strSqlFieldsTo = strSqlFieldsTo & ", [Court District]"
        End If
        strSqlApnd = strSqlApnd & "(" & strSqlFieldsTo & ") SELECT " & strSqlFieldsFrom & _
                          " ,'" & tdf.Name & "' FROM [" & tdf.Name & "] t"

        db.Execute strSqlApnd
NextTdf:
    Next tdf
    Set tdf = Nothing
    db.Close
    Set db = Nothing
    MsgBox "All tables appended", vbInformation, "All Done"
    
End Sub

Then after all that was done, I ran a manual query on the tblAll table to delete all the blank records, as there were lots of 'em. Not even sure how they got in there, as there was no data in any of those that I checked. I removed any that did not have a "Case #."


--

"If to err is human, then I must be some kind of human!" -Me