Error: can not execute a select query

  • Thread starter Thread starter coyote
  • Start date Start date
C

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
 
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
 
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?
 
Back
Top