SQL in a module

[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

 

[Guest]

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

 

[Brendan Reynolds]

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