Getting "Invalid Syntax" in statement...

[James Schoemaker]

If anyone can tell me what is incorrect or what I should be doing, it
would be most appreciated.

Here is what I am trying to do:

1. Create an Indexed Table of Unique ID Numbers.
2. Load the table with data from four separate Queries,
based upon specific criteria, filtering out duplicate
ID Numbers.

The code works fine up to the point where the records are inserted into
the newly created table. The four queries work fine if run by
themselves. But, it fails with a "Invalid Syntax" error on the SQL
statement. Here is the code:


Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Dim idx As ADOX.index
Dim col As ADOX.Column
Dim rst As ADODB.Recordset
' delete the old table and create a new one
Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table
cat.ActiveConnection = CurrentProject.Connection
On Error Resume Next
cat.Tables.Delete "tbl_PersonID"
tbl.Name = "tbl_PersonID"
tbl.Columns.Append "IDno", adInteger
cat.Tables.Append tbl
cat.Tables.Refresh
' build an index without duplicates
Set idx = New ADOX.index
Set col = New ADOX.Column
col.Name = "IDno"
idx.Name = "IDno"
idx.PrimaryKey = False
idx.Unique = True
idx.Columns.Append col
tbl.Indexes.Append idx
Set cat = Nothing
Set tbl = Nothing
Set idx = Nothing
Set col = Nothing
' append in any of the selected records
Set rst1 = New ADODB.Recordset
rst1.Open "INSERT INTO tbl_PersonID " & _
"TABLE qry_Records_1 " & _
"UNION " & _
"TABLE qry_Records_2 " & _
"UNION " & _
"TABLE qry_Records_3 " & _
"UNION " & _
"TABLE qry_Records_4", _
CurrentProject.Connection, adOpenDynamic, _
adLockOptimistic, adCmdText
rst1.Close
Set rst1 = Nothing


I hope someone can spot what is wrong, or what I should be doing
instead. Thanks....

 

[Douglas J. Steele]

--
Doug Steele, Microsoft Access MVP

 

[Douglas J. Steele]

There are a couple of things that don't seem right to me.

First, an INSERT INTO statement isn't going to create a recordset: no action
query does. You might consider using the ADO Command object to execute the
SQL for you.

Second, I don't believe you can use TABLE in SQL: I think that's an ADOX
object only. Try something like

"INSERT INTO tbl_PersonID (IDno) " & _
"SELECT IDno FROM qry_Records_1 " & _
"UNION " & _
"SELECT IDno FROM qry_Records_2 " & _
"UNION " & _
"SELECT IDno FROM qry_Records_3 " & _
"UNION " & _
"SELECT IDno FROM qry_Records_4", _

 

[James Schoemaker]

There are a couple of things that don't
seem right to me.

First, an INSERT INTO statement isn't
going to create a recordset: no action
query does. You might consider using
the ADO Command object to execute the
SQL for you.

Second, I don't believe you can use
TABLE in SQL: I think that's an ADOX
object only. Try something like

"INSERT INTO tbl_PersonID (IDno) " & _
"SELECT IDno FROM qry_Records_1 " & _
"UNION " & _
"SELECT IDno FROM qry_Records_2 " & _
"UNION " & _
"SELECT IDno FROM qry_Records_3 " & _
"UNION " & _
"SELECT IDno FROM qry_Records_4", _

Thank you for the suggextion, but when I use the above the following
error message shows up:

Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters.


Maybe it is my mistake in not explaining it too well. I am using
MS-Access 2000 with the code in a front-end MDB (at the workstation PC),
and the data linked from a back-end MDE (sitting on a file server). The
new table that is being created is going into the front-end MDB.

This new table will only have a single field and nothing more.

The four queries are Select Queries using several tables and fields for
the selection, but only the "IDno" is being pulled and built.

Hope this explains thing a little more.

 

[James Schoemaker]

Try something like

"INSERT INTO tbl_PersonID (IDno) " & _
"SELECT IDno FROM qry_Records_1 " & _
"UNION " & _
"SELECT IDno FROM qry_Records_2 " & _
"UNION " & _
"SELECT IDno FROM qry_Records_3 " & _
"UNION " & _
"SELECT IDno FROM qry_Records_4", _

Even tried the above with a single SELECT statement:

"INSERT INTO tbl_PersonID (IDno) SELECT IDno FROM qry_Records_1"

And still get the following error message:

Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters.

Any suggestions would be most helpful. Thanks.

 

[Jeff Williams]

It looks to me like one or more of the 'qry_Records_%' queries requires some
type of parameter. If any of these queries needs a parameter to be entered
by the users (or pulled from a form) - this will fail and you will get the
type of message you are getting.

If this is the case you can use something like the following from
http://mvps.org/access

************ Code Start ****************
Dim db As Database 'current database
Dim rs As Recordset 'holds query resultset
Dim qdfParmQry As QueryDef 'the actual query object
Set db = CurrentDb()
Set qdfParmQry = db.QueryDefs("Qry1")
qdfParmQry("Please Enter Code:") = 3

' or try this alternate method
' to pass the parameter

qdfParmQry![Please Enter City:] = "New York"
Set rs = qdfParmQry.OpenRecordset()
'************** Code End ***************

 

[Douglas J. Steele]

I have to agree: there's nothing in the code shown that should cause the
error he's getting.

There's a slight problem with your solution, though, Jeff. As I stated
earlier, an INSERT INTO statement is an action query, and doesn't create a
recordset. As well, James is trying to use ADO, whereas your solution is
DAO. It's still possible to define the values for parameters using the ADO
Command object, but I can't find a good example.

--
Doug Steele, Microsoft Access MVP

It looks to me like one or more of the 'qry_Records_%' queries requires some
type of parameter. If any of these queries needs a parameter to be entered
by the users (or pulled from a form) - this will fail and you will get the
type of message you are getting.

If this is the case you can use something like the following from
http://mvps.org/access

************ Code Start ****************
Dim db As Database 'current database
Dim rs As Recordset 'holds query resultset
Dim qdfParmQry As QueryDef 'the actual query object
Set db = CurrentDb()
Set qdfParmQry = db.QueryDefs("Qry1")
qdfParmQry("Please Enter Code:") = 3

' or try this alternate method
' to pass the parameter

qdfParmQry![Please Enter City:] = "New York"
Set rs = qdfParmQry.OpenRecordset()
'************** Code End ***************

Even tried the above with a single SELECT statement:

"INSERT INTO tbl_PersonID (IDno) SELECT IDno FROM qry_Records_1"

And still get the following error message:

Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters.

Any suggestions would be most helpful. Thanks.

 

[Jeff Williams]

Sorry - somehow I glanced over the reference to ADO. The only options I see
are:

Create a stored procedure (CREATE PROCEDURE statement) - execute the
stored procedure with the appropriate parameters.

Or, my personal favorite:

Create the full insert statement in code, replacing all parameters at
the time he is creating the SQL statement and executing the statement using
the execute method.

For his particular issue, it would probably be better to construct
individual insert statements - and execute each statement to build the new
table.

For example:

strSQL = "INSERT INTO tbl_PersonID (IDno) <full query from qry_Records_1
only selecting IDno>"
currentproject.Execute strSQL...

strSQL = "INSERT INTO tbl_PersonID (IDno) <full query from qry_Records_2>
and add AND NOT EXISTS(SELECT * FROM tbl_PersonID as t2 WHERE t2.IDno =
t1.IDno) --t1 reference should be built in the qry_Records_2 statement from
above
currentproject.Execute strSQL...

and so on,

Jeff

I have to agree: there's nothing in the code shown that should cause the
error he's getting.

There's a slight problem with your solution, though, Jeff. As I stated
earlier, an INSERT INTO statement is an action query, and doesn't create a
recordset. As well, James is trying to use ADO, whereas your solution is
DAO. It's still possible to define the values for parameters using the ADO
Command object, but I can't find a good example.

--
Doug Steele, Microsoft Access MVP

It looks to me like one or more of the 'qry_Records_%' queries requires some
type of parameter. If any of these queries needs a parameter to be entered
by the users (or pulled from a form) - this will fail and you will get the
type of message you are getting.

If this is the case you can use something like the following from
http://mvps.org/access

************ Code Start ****************
Dim db As Database 'current database
Dim rs As Recordset 'holds query resultset
Dim qdfParmQry As QueryDef 'the actual query object
Set db = CurrentDb()
Set qdfParmQry = db.QueryDefs("Qry1")
qdfParmQry("Please Enter Code:") = 3

' or try this alternate method
' to pass the parameter

qdfParmQry![Please Enter City:] = "New York"
Set rs = qdfParmQry.OpenRecordset()
'************** Code End ***************

Douglas J. Steele wrote:

Try something like

"INSERT INTO tbl_PersonID (IDno) " & _
"SELECT IDno FROM qry_Records_1 " & _
"UNION " & _
"SELECT IDno FROM qry_Records_2 " & _
"UNION " & _
"SELECT IDno FROM qry_Records_3 " & _
"UNION " & _
"SELECT IDno FROM qry_Records_4", _



Even tried the above with a single SELECT statement:

"INSERT INTO tbl_PersonID (IDno) SELECT IDno FROM qry_Records_1"

And still get the following error message:

Run-time error '-2147217904 (80040e10)':
No value given for one or more required parameters.

Any suggestions would be most helpful. Thanks.