Union select query to make a table

[mjdemaris]

Hey all!

I got this code from a video, trying to take data from one table, merge it with data on another without any duplicates.

But it gives me the attached error (3141).

Private Sub cmdCreateTable_Click()
    Dim sql As String, sTable As String, oTD As TableDef
    
    sql = "SELECT * FROM [T-SupplierPartNums] UNION SELECT * FROM [PartNumTemp]"
    
    If MsgBox("Would you like to rename the table?", vbYesNo) = vbYes Then
        sTable = InputBox("Enter Table Name: ", , "[SupplierPartNum]")
    End If
    
    For Each oTD In CurrentDb.TableDefs
        If oTD.Name = sTable Then sTable = sTable & "1"
    Next oTD
    
    sql = "SELECT * INTO " & sTable & " FROM (" & sql & ")"
    DoCmd.RunSQL sql
    
End Sub

Thoughts?

 

[Galaxiom]

How about:

sql = "SELECT * INTO " & sTable & " FROM (" & sql & ") AS X"

 

[jdraw]

My guess is that you can not use the * with union query. And that is just a guess.

I would expect that you would have to give individual field names, and the number of fields in each table in the Union would have to be the same.

Note: If the number of fields differ then I would expect this error.
3307 The number of columns in the two selected tables or queries of a union query do not match.

 

[Galaxiom]

My guess is that you can not use the * with union query. And that is just a guess.

I would expect that you would have to give individual field names,

No problem using the * in a Union.

The first section of the query defines the field names the same way as it does in an ordinary query.

Maybe PartNumTemp has reserved words in its fieldnames.

 

[MarkK]

I don't think this code . . .

    For Each oTD In CurrentDb.TableDefs
        If oTD.Name = sTable Then sTable = sTable & "1"
    Next oTD

. . . will guarantee a unique table name.

 

[mjdemaris]

There are no reserved words in the temp table.

Wouldn't the For Each code generate a new table temp1 if temp was already created?

Perhaps the use of double quotes causes a problem, but not sure where. I tried modifying the

 sql = "SELECT * INTO " & sTable & " FROM (" & sql & ")"

statement with some single quotes, but no dice.

 

[MarkK]

You don't know what order the table names will appear in the TableDefs collection. Suppose existing table names appear in this order . . .

tblTest1
tblTest2
tblTest

. . . and then your user supplies the name "tblTest", so the For..Each..Next loop tries . . .

"tblTest1": OK, 
"tblTest2": OK,
"tblTest": fails, and names the new table "tblTest1", which is not unique

In this way your loop does not guarantee a unique table name. I'm not saying this is THE problem you are encountering, but it appears to be a vulnerability in the code.

For a unique name, check the MSysObjects table, which stores the name of every object in your database. To find out if a named object exists there, you can run a DCount(), like . .

Function ObjectExists(Name as string) as Boolean
   ObjectExists = DCount("*", "MSysObjects", "Name = '" & name & "'")
End Function

. . . which you could use in a loop, like . . .

    If MsgBox("Would you like to rename the table?", vbYesNo) = vbYes Then
        sTable = InputBox("Enter Table Name: ", , "[SupplierPartNum]")
    End If

[COLOR="Blue"]    Do While ObjectExists(sTable)
        sTable = sTable & "1"
    Loop
[/COLOR]    
    sql = "SELECT * INTO " & sTable & " FROM (" & sql & ")"
    DoCmd.RunSQL sql

 

[plog]

Why does everyone insist on guessing about what's in constructred SQL statements? Spit them out, look at what they actually say with your own eyes, then if it looks good, throw it in a query object and run them.

Does

SELECT * FROM [T-SupplierPartNums] UNION SELECT * FROM [PartNumTemp]

even produce valid results? What happens when you paste it into a query?

Once you confirm that works, spit out what's actually in sql after its fully built. Paste that into a query and see what you get.

Quit guessing/assuming about what's in your SQL statements.

 

[MarkK]

insist

IMO, it's not insistence, it's ignorance, so let's understand and educate.

 

[mjdemaris]

plog,
The sql works fine when split, as you suggested, and the whole statement works fine in a query. The problem seems to be with using the variable and other syntax in the VBA code.

Mark, I haven't tested your suggestion yet...

 

[mjdemaris]

I figured out that the Input box does not set the variable sTable to the default value, so the code is trying to create a table with no name!

I don't know why, yet...

 

[mjdemaris]

Oh, it's the if then statement...duh.

 

[mjdemaris]

Mark,
I do find that the ObjectExists function does not function! The first few times, the table was not listed in the sysobjects table, so I compacted and repaired and checked for that table. Now it is there, but the function still returns false.

I notice that this table does not have an "LvProp" value set. I wonder if that could cause a problem with returning a false everytime...

 

[jdraw]

If the user selects No for the rename, sTable has no value
and the sql for execution is

SELECT * INTO  FROM (SELECT * FROM [T-SupplierPartNums] UNION SELECT * FROM [PartNumTemp])

which will fail on a syntax --name or reserved word sort of thing (missing the target table name)

You could give sTable a default value.

If user answers Yes, the rendered sql is

SELECT * INTO [SupplierPartNum] FROM (SELECT * FROM [T-SupplierPartNums] UNION SELECT * FROM [PartNumTemp])

But, to emphasize against using * in your query.
If the table records have a different number of fields, or if the table fields are in a different order, then the union query will fail.

Good luck.

 

[mjdemaris]

jdraw, quite correct, thanks.

Mark, I think the function needs work:

Function ObjectExists(tblName As String) As Boolean
    ObjectExists = False
    If DCount("[Name]", "MSysObjects", "Name = '" & tblName & "'") = 1 Then
        ObjectExists = True
        MsgBox "True!"
    Else
        MsgBox "False!"
    End If
    
End Function

 

[MarkK]

Your function fails if there are two objects with the same name. You are only checking for the existence of one (1), but there might be a form and a query and a table with the same name.

How does this function fail? Under what circumstances?

Function ObjectExists(Name as string) as Boolean
[COLOR="Green"]   'returns False if DCount() returns zero, otherwise returns True[/COLOR]
   ObjectExists = DCount("*", "MSysObjects", "Name = '" & name & "'")
End Function