SQL in a module

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I'm trying to create a table via some SQL in a module. I'm not very good at
this, so need some help.

myservice = "Resources Service Area"

strSQL = "SELECT * FROM tblSicknessInAPeriod INTO tblSicknessInAPeriodtemp
WHERE [LastOfService] = '" & myservice & "'"
Set rst = dbs.OpenRecordset(strSQL)

It's not working (ie. not creating the table). I've checked that the strSQL
line is returning records by removing the INTO tblSicknessInAPeriodTemp from
the statement and records are returned. How do I get this code to create the
table?

Any help appreciated

Yours

Paul

I
 
Try, changing the order in the SQL, and because it's an action query and not
select query you should use RunSQL rather then OpenRecordSet

strSQL = "SELECT tblSicknessInAPeriod.* INTO tblSicknessInAPeriodtemp FROM
tblSicknessInAPeriod WHERE [LastOfService] = '" & myservice & "'"
Docmd.RunSQL strSQL
 
To run an action query like this, use the Execute method of either the DAO
Database object or the ADODB Connection object ...

CurrentDb.Execute strSQL

.... or ...

CurrentProject.Connection.Execute strSQL
 
Back
Top