Coverting SQL to DoCmd.RunSQL

dem1an · June 25th, 2013, 02:14 PM

This query works in Access:

Code:

SELECT a.siteid, a.value, a.timestamp, a.ParamID INTO Table1
FROM DeviceTypeData AS a
WHERE (((a.siteid)="105471") AND ((a.ParamID) In (Select Top 3 ParamID from [DeviceTypeData] where [siteid]=a.[siteid] Order By [ParamID] desc)));

I think I'm having trouble getting the quotes around the the site id. This is what I have that is NOT working, but is close since it worked without the site id filter added. SQL basically shows up like a.siteid=105471 instead of a.siteid="105471". I don't know, but this may be the problem with the following, but I'm not sure how to code that.

Code:

SQL = "SELECT a.siteid, a.value, a.timestamp, a.ParamID INTO " & CookedTable & _
" FROM " & SourceTable & " AS a" & _
" WHERE (((a.siteid=" & EquipID & ") AND ((a.ParamID) In (Select Top " & Value & " ParamID FROM " & "[" & SourceTable & "]" & _
" WHERE [siteid]=a.[siteid] Order By [ParamID] desc)));"
           
DoCmd.RunSQL SQL

So basically I get this, notice there is no " " around the 105471 and I get not table created. Any help?

Code:

SELECT a.siteid, a.value, a.timestamp, a.ParamID INTO Table1 FROM DeviceTypeData AS a WHERE (((a.siteid)=105471) AND ((a.ParamID) In (Select Top 3 ParamID from [DeviceTypeData] where [siteid]=a.[siteid] Order By [ParamID] desc)));

HiTechCoach · June 25th, 2013, 03:36 PM

The Docmd.RunSQL is for action queries. You are trying to run a SELECT query. The DoCmd.RunSQL does not return a recordset or show any reords.

See this MSDN article: DoCmd.RunSQL Method (Access) - click here

Quote:

A string expression that's a valid SQL statement for an action query or a data-definition query. It uses an INSERT INTO, DELETE, SELECT...INTO, UPDATE, CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, or DROP INDEX statement. Include an IN clause if you want to access another database.

If siteid is a text data type then you will need to add the Text Delimiter in your code

Try something like this:

Code:

.. WHERE (((a.siteid=" & Chr(34)  & EquipID & Chr(34)  & ") AND ...

dem1an · June 26th, 2013, 10:30 AM

Man, I was optimistic; that looked like a clever way to get the syntax i was looking for. The siteid is a text field. The syntax reported in msgbox SQL looked perfect. It didn't work though unfortunately. I get no table created.

I think there is something else wrong with my code. I got a more direct query to work. BRB....I think you are on to something.

I must have missed something. I reconstructed it and it works.

New code:

Code:

SQL = "SELECT a.siteid, a.value, a.timestamp, a.ParamID INTO " & CookedTable & " 
FROM " & SourceTable & " AS a WHERE (((a.siteid)=" & Chr(34) & EquipID & Chr(34) & ") AND ((a.ParamID) In (Select Top " & Value & " ParamID from [" & SourceTable & "] 
where [siteid]=a.[siteid] Order By [ParamID] desc)));"

Solved! Thank you!