SQL Code Help (1 Viewer)

[BlueJacket]

I'm trying to write some SQL code from VBA to populate a table. This is what I have so far:

Private Sub butTest_Click()

    Dim strSQL As String
    Dim strTable As String
    
'clear tblSEoSMergeList
    strTable = "tblSEoSMergeList"
    strSQL = "DELETE tblSEoSMergeList.* FROM tblSEoSMergeList;"
    DoCmd.SetWarnings False
    DoCmd.RunSQL strSQL

    strSQL = ""
    strSQL = strSQL & "INSERT INTO tblSEoSMergeList "
    strSQL = strSQL & "SELECT [tblPropertyDetails]![StreetNumber] & "" "" & [tblPropertyDetails]![StreetName] AS Property, "
    strSQL = strSQL & "tblClients.ClientName AS Client, "
    strSQL = strSQL & "tblQuietTitle.CaseNumber, "
    strSQL = strSQL & "tblPropertyDetails.CountyID AS County, "
    strSQL = strSQL & "tblPropertyDetails.ParcelID AS Parcel, "
    strSQL = strSQL & "DefList([Forms]![frmServiceChart]![CaseID]) AS DefList "
    strSQL = strSQL & "FROM tblPropertyDetails "
    strSQL = strSQL & "LEFT JOIN tblClients "
    strSQL = strSQL & "ON tblPropertyDetails.ClientID = tblClients.ClientID "
    strSQL = strSQL & "LEFT JOIN tblQuietTitle_PropertDetails "
    strSQL = strSQL & "ON tblPropertyDetails.PropertyID = tblQuietTitle_PropertDetails.PropertyID "
    strSQL = strSQL & "LEFT JOIN tblQuietTitle "
    strSQL = strSQL & "ON tblQuietTitle_PropertDetails.CaseID = tblQuietTitle.CaseID "
    strSQL = strSQL & "LEFT JOIN tblDefendantCaseManagement "
    strSQL = strSQL & "ON tblQuietTitle.CaseID = tblDefendantCaseManagement.CaseID "
    strSQL = strSQL & "WHERE tblQuietTitle.CaseID = [Forms]![frmServiceChart]![CaseID]"
    strSQL = strSQL & ";"

    Debug.Print strSQL
    
    DoCmd.RunSQL strSQL
        
End Sub

It keeps giving me a syntax error. I've commented out sections of it to test it line by line and the main holdup seems to be happening around the joining table tblQuietTitle_PropertDetails between tblPropertyDetails and tblQuietTitle. What am I missing?

Side note, while doing this, I noticed there's a "y" missing in tblQuietTitle_PropertDetails. If I rename that table, will it mess up other queries/relationships? It doesn't appear in other VBA code.

Thanks in advance.

 

[MarkK]

The command should be...

INSERT INTO <table> ( <field1>, <field2> )
SELECT <field1>, <field2>
FROM <someothertable>

https://msdn.microsoft.com/en-us/library/bb208861(v=office.12).aspx
hth
Mark

 

[BlueJacket]

The command should be...

INSERT INTO <table> ( <field1>, <field2> )
SELECT <field1>, <field2>
FROM <someothertable>

https://msdn.microsoft.com/en-us/library/bb208861(v=office.12).aspx
hth
Mark

OR, at least in Access, if the field names in the query exactly match the field name in the table that they're going to. If the above is best practice, I can certainly start doing that, but I don't think that's causing the issues here. I tested this part of the query:

    strSQL = ""
    strSQL = strSQL & "INSERT INTO tblSEoSMergeList "
    strSQL = strSQL & "SELECT [tblPropertyDetails]![StreetNumber] & "" "" & [tblPropertyDetails]![StreetName] AS Property, "
    strSQL = strSQL & "tblClients.ClientName AS Client "
    strSQL = strSQL & "FROM tblPropertyDetails "
    strSQL = strSQL & "LEFT JOIN tblClients "
    strSQL = strSQL & "ON tblPropertyDetails.ClientID = tblClients.ClientID; "

and that wrote to the table just fine.

 

[MarkK]

Then try comment out this line...

    strSQL = strSQL & "INSERT INTO tblSEoSMergeList "

... and just construct the SQL as a SELECT query. Then, at this line...

    Debug.Print strSQL

... the SELECT query is printed to the immediate pane. Copy that SQL and paste it into a new blank query in SQL view and try and run it. That will show you in greater detail where your error is in your SQL text.

hth

 

[BlueJacket]

Sadly, it just gave me the exact same error message that it gave me in VBA.

When Access writes SQL based off queries, it's a little wonky and uses a lot of parenthesis when there are many joins. Could this be why it's not accepting my SQL in VBA?

 

[The_Doc_Man]

MarkK gave you the answer but somehow it keeps on getting skipped.

...

    strSQL = "INSERT INTO tblSEoSMergeList "  & _ '[COLOR="Red"]{the error is right here....}[/COLOR]
    [COLOR="Red"]strSQL = strSQL & "(StreeNumber, StreetName, Client, CaseNumber, ... ) "[/COLOR] & _
    strSQL = strSQL & "SELECT [tblPropertyDetails]![StreetNumber] & "" "" & 
...

The INSERT INTO clause requires you to list the fields you are going to insert because you can select from totally unrelated tables and put anything in any order. So even if your SELECT claus names the fields correctly in the correct order, it didn't have to. That is why the parenthetical field list is mandatory syntax.

 

[BlueJacket]

@The_Doc_Man
From the page that Markk linked:

When individual columns are not designated, the SELECT table column names must match exactly those in the INSERT INTO table.

There is the option to not list the fields you're going to insert, at least in Access. However, I did add that into my SQL and it gives me the same syntax error (then lists the entire SQL statement).

My SQL currently looks like:

    strSQL = ""
    strSQL = strSQL & "INSERT INTO tblSEoSMergeList ( Property, Client, CaseNumber, County, Parcel, DefList ) "
    strSQL = strSQL & "SELECT [tblPropertyDetails]![StreetNumber] & "" "" & [tblPropertyDetails]![StreetName] AS Property, "
    strSQL = strSQL & "tblClients.ClientName AS Client, "
    strSQL = strSQL & "tblQuietTitle.CaseNumber, "
    strSQL = strSQL & "tblPropertyDetails.CountyID AS County, "
    strSQL = strSQL & "tblPropertyDetails.ParcelID AS Parcel, "
    strSQL = strSQL & "DefList([Forms]![frmServiceChart]![CaseID]) AS DefList "
    strSQL = strSQL & "FROM tblPropertyDetails "
    strSQL = strSQL & "LEFT JOIN tblClients "
    strSQL = strSQL & "ON tblPropertyDetails.ClientID = tblClients.ClientID "
    strSQL = strSQL & "LEFT JOIN tblQuietTitle_PropertDetails "
    strSQL = strSQL & "ON tblPropertyDetails.PropertyID = tblQuietTitle_PropertDetails.PropertyID "
    strSQL = strSQL & "LEFT JOIN tblQuietTitle "
    strSQL = strSQL & "ON tblQuietTitle_PropertDetails.CaseID = tblQuietTitle.CaseID "
    strSQL = strSQL & "LEFT JOIN tblDefendantCaseManagement "
    strSQL = strSQL & "ON tblQuietTitle.CaseID = tblDefendantCaseManagement.CaseID "
    strSQL = strSQL & "WHERE tblQuietTitle.CaseID = [Forms]![frmServiceChart]![CaseID]"
    strSQL = strSQL & ";"

However, at this point, this is all educational. I was trying to use this as an exercise to practice writing out my SQL instead of having Access make it for me, but I have since made a query and pasted the SQL from that into my VBA. Worked like a charm. I would like still like to know what is wrong with that SQL statement though

 

[jdraw]

I did a debug.print of your material, then ran it through an SQL formatter and got this:

INSERT INTO tblSEoSMergeList (
	Property
	,Client
	,CaseNumber
	,County
	,Parcel
	,DefList
	)
SELECT [tblPropertyDetails] ! [StreetNumber] & " " & 
	[tblPropertyDetails] ! [StreetName] AS Property
	,tblClients.ClientName AS Client
	,tblQuietTitle.CaseNumber
	,tblPropertyDetails.CountyID AS County
	,tblPropertyDetails.ParcelID AS Parcel
	,DefList([Forms] ! [frmServiceChart] ! [CaseID]) AS 
	DefList
FROM tblPropertyDetails
LEFT JOIN tblClients ON tblPropertyDetails.ClientID = 
	tblClients.ClientID
LEFT JOIN tblQuietTitle_PropertDetails ON 
	tblPropertyDetails.PropertyID = 
	tblQuietTitle_PropertDetails.PropertyID
LEFT JOIN tblQuietTitle ON 
	tblQuietTitle_PropertDetails.CaseID = 
	tblQuietTitle.CaseID
LEFT JOIN tblDefendantCaseManagement ON tblQuietTitle
	.CaseID = tblDefendantCaseManagement.CaseID
WHERE tblQuietTitle.CaseID = [Forms] ! 
	[frmServiceChart] ! [CaseID];

I'm pretty sure Access wants some bracketing around the Joins.

 

[The_Doc_Man]

I'm with JDraw on this point, too. When you build a query like this from the design grid, you get a lot of parentheses, and some of them might be superfluous - but not all of them. It might be that this is the correct order, but we can't see if you have relationships of the appropriate type among the tables.

The relationships I see here are four one-to-many cases:
1. Client to Property on Client ID
2. Property to QuietTitle on Property ID
3. QuietTitle to QuietTitle_PropertyDetails on Case ID
4. QuietTitle to DefendantCasemanagement on Case ID.

It is the #3 and #4 relationships that I think trigger the need for parentheses because they don't follow the pattern. I.e. #1 --> #2 --> #3 or #1--> #2 --> #4 but this diagram "forks" after #2. Also, the part that makes this oddball is that the logical feed of the relationship is based on case ID as the starting point. You are essentially working backwards here based on the way these things are written. With INNER JOIN for each, there would be no doubt, but with the LEFT JOINS, it looks a little bit "hinkey" to me.

 

[Cronk]

Yes, brackets are needed where there is any more than 2 tables being joined, even if it is 1 -> 2 -> 3