VBA example of dropping temp tables in MS SQL server

[merebag]

One of the processes I have in my old Access ADP project was working with temp tables. How would I do something like this in my VBA code using ODBC to my SQL server?

IF OBJECT_ID('tempdb..#TmpActive" & AuditID & "') IS NOT NULL DROP TABLE #TmpActive" & AuditID

The process looks to see if there is already a temp table called TmpActive(with the user ID appended to it). If it exists, it drops it.

I can't use the access docmd of .runSQL. Nor can I use a DAO recordset. Any clues?

Thanks

[ItsMe]

I have not looked at everything very closely, but the first thing that jumps out at me is that the temp table is something on SQL server itself, as opposed to a table in Access..

Do you know for sure where the temp tables are/created? If they reside on the SQL server, you can connect to the server with the appropriate credentials and DROP via ODBC. So, not via DAO, but using a passthrough query. So whatever SQL works on the server should work in your passthrough.

[merebag]

I have not looked at everything very closely, but the first thing that jumps out at me is that the temp table is something on SQL server itself, as opposed to a table in Access..

Do you know for sure where the temp tables are/created? If they reside on the SQL server, you can connect to the server with the appropriate credentials and DROP via ODBC. So, not via DAO, but using a passthrough query. So whatever SQL works on the server should work in your passthrough.

Yes, the temp tables are on the SQL server. There are no local tables used in my access app. So, passthrough query should be the way to go from what you are saying. I will look at that option. Thanks

[ItsMe]

You can create, save, and name a passthrough query. If you have trouble saving the SQL syntax necessary, you can assign the SQL statement to the SQL property of a saved Passthrough Query. You can access the properties of saved Query Objects in access via VBA, using the DAO QueryDefs collection. I should have some sample code around here somewhere if you get stuck
.