Several executes with currentDB possible?

[Kingz]

Hi guys,

I was wondering, I have an SQL statement which was dynamically created and which I have stored as such:
Set Qds = DBs.querydefs("PT_query")
Qds.SQL= strSQLQuery
Qds.returnsrecords = True
Qds.Close

That works fine.. Then I delete the contents of a table:
currentDB.Execute "DELETE FROM TableA"

That also works, but straight after that I want to insert into the deleted table:

currentDB.Execute "SELECT pt_query.* INTO TableA from pt_,query"

That doesn't work, it complains that TableA already exists. I'm just thinking, of course it exists, you just deleted the content and it needs to exist to be able to fill it. What am I doing wrong?

 

[theDBguy]

Your SQL statement is a make-table query. If you want an append query, you need something like: INSERT INTO..., not SELECT INTO.

 

[plog]

Moving data is a huge red flag. Why are you doing this? What's the ultimate goal? Why can't data just live where it is and you use SELECT queries to obtain the necessary results?

Further, none of that SQL is dynamic. It's literally 2 literal strings of SQL. Why not make 2 query objects to perform those tasks (DELETE and APPEND) and then use DoCmd.OpenQuery to execute them consecutively?

DoCmd.OpenQuery method (Access)

Office VBA reference topic

learn.microsoft.com

Again, though, what's the big picture because this probably isn't the best way to achieve the ultimate goal.

 

[Kingz]

Your SQL statement is a make-table query. If you want an append query, you need something like: INSERT INTO..., not SELECT INTO.

Moving data is a huge red flag. Why are you doing this? What's the ultimate goal? Why can't data just live where it is and you use SELECT queries to obtain the necessary results?

Further, none of that SQL is dynamic. It's literally 2 literal strings of SQL. Why not make 2 query objects to perform those tasks (DELETE and APPEND) and then use DoCmd.OpenQuery to execute them consecutively?

DoCmd.OpenQuery method (Access)

Office VBA reference topic

learn.microsoft.com

Again, though, what's the big picture because this probably isn't the best way to achieve the ultimate goal.

Ok, to give you whole the picture, I'm using pass through queries to access a database, because the query is complicated, consisting of functions like "lag", which are difficult to implement. It is also a cte query, which is more legible in is form. Now this query is dynamically created with two dates which are entered via a form. Start_Date , end date... Now I have to grab the content of my Passthrough query into a table, in order to use it in a join with other tables, which will then be the result I need to export as an excel file.

 

[GPGeorge]

Ok, to give you whole the picture, I'm using pass through queries to access a database, because the query is complicated, consisting of functions like "lag", which are difficult to implement. It is also a cte query, which is more legible in is form. Now this query is dynamically created with two dates which are entered via a form. Start_Date , end date... Now I have to grab the content of my Passthrough query into a table, in order to use it in a join with other tables, which will then be the result I need to export as an excel file.

All of that sounds reasonable with the exception of the need to repeatedly create new tables using the "SELECT INTO..." syntax.

If, as you seem to intend, you want to replace the existing data in "TableA", you could do that with the append syntax instead, "INSERT INTO...."

Actually, given the fact that this is a SQL Server side passthru, and the table exists on the server, it's hard to see how creating a new table would be very useful since you'd end up having to manage it entirely on the server, in a view that joins it with other tables. Perhaps that's the plan?

Rather than asking about a generic task out of context, maybe explaining the requirement will help someone figure out a more applicable suggestion.

 

[plog]

Here's the part I wanted:

which will then be the result I need to export as an excel file.

So the goal is to export data to Excel. That can be done without a Rube Goldberg machine. You can get the data you need in a query without VBA or making tables or deleting data. You just need to build the correct query, once that query is correct you can export it to Excel.

If you can provide sample starting data from all the tables involved and the expected results of that data, we can help you build that query to get you from the start to the end in the most efficient manner.

 

[ebs17]

then use DoCmd.OpenQuery to execute them

I don't use OpenQuery.
db.Execute handles stored queries very well and provides reasonable error handling.

 

[DickyP]

This thread seems to be meandering given that the cause of your problem has been explained in that you need to use the INSERT INTO .....

In passing, if you really did want a new table then by all means use your syntax, but don't remove the data - delete the table itself first. using the SQL DDL command DROP TABLE tablename.

 

[Kingz]

All of that sounds reasonable with the exception of the need to repeatedly create new tables using the "SELECT INTO..." syntax.

If, as you seem to intend, you want to replace the existing data in "TableA", you could do that with the append syntax instead, "INSERT INTO...."

Actually, given the fact that this is a SQL Server side passthru, and the table exists on the server, it's hard to see how creating a new table would be very useful since you'd end up having to manage it entirely on the server, in a view that joins it with other tables. Perhaps that's the plan?

Rather than asking about a generic task out of context, maybe explaining the requirement will help someone figure out a more applicable suggestion.

Yeah, sorry for replying in words. I liked what you wrote, and made the appropriate changes to delete from, rather than drop the table
And yeah, I used insert into select thanks for that. So I guess this thread can be closed.. Or do I close it?