Error: can not execute a select query

[coyote]

In Access 2003, this worked even though "Medal_Assign_Collections" is a query
(not a table). Any other way to run this Make Table Query using the results
of a query? TIA

Dim db As DAO.Database
Dim rsBasic As DAO.Recordset
Set db = CurrentDb()
Set rsBasic = db.OpenRecordset("Medal_Assign_Collections", DB_OPEN_DYNASET)

strSQL = "SELECT Medal_Assign_Collections.Division,
Medal_Assign_Collections.GroupID, Medal_Assign_Collections.GroupName,
Medal_Assign_Collections.ConID, Medal_Assign_Collections.FirstName,
Medal_Assign_Collections.LastName, Medal_Assign_Collections.Medal,
Medal_Assign_Collections.Collection, Medal_Assign_Collections.EntryID,
Medal_Assign_Collections.EntryName, Medal_Assign_Collections.Score INTO
Final_Medals_Assigned "
strSQL = strSQL & "FROM Medal_Assign_Collections "
strSQL = strSQL & "ORDER BY
Medal_Assign_Collections.Division, Medal_Assign_Collections.GroupID;"

CurrentDb.Execute (strSQL), dbFailOnError

 

[Duane Hookom]

What is the reason for opening the recordset?

You can use a select query as the source of a make table query. Whether you
do this in code or design view shouldn't make much difference.

Your strSQL line of code is quite long. Consider changing it to:
strSQL = "SELECT Division, GroupID, GroupName, ConID, FirstName, LastName, "
& _
"Medal, Collection, EntryID, EntryName, Score INTO Final_Medals_Assigned "
strSQL = strSQL & "FROM Medal_Assign_Collections "
CurrentDb.Execute (strSQL), dbFailOnError

 

[coyote]

I thought I had to open the DB before querying it... poor form?

 

[Duane Hookom]

There is no reason to open the recordset prior to executing strSQL.
You have never stated an issue, result, error or whatever when you attempt
to execute the SQL. Is there an issue/question?