Combining SQL DELETE Statements Into One SQL String

[RunTime91]

I have the following and it works perfectly and lightning fast - so no complaints there



But I can't help but to wonder if there is a 'cleaner' way to write this - Perhaps a way to combine the DELETE Queries into one StrSQL

Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.ConnectionString = strConnection
conn.Open
StrSQL1 = "DELETE FROM dbo.ThisTable"
StrSQL2 = "DELETE FROM dbo.ThatTable"
StrSQL3 = "DELETE FROM dbo.HisTable"
StrSQL4 = "DELETE FROM dbo.HerTable"

With conn
  .Execute StrSQL1
    .Execute StrSQL2
    .Execute StrSQL3
  .Execute StrSQL4
End With

conn.Close
Set conn = Nothing

As Always - Thank You...

[June7]

Multiple SQL actions cannot be combined into a single executable string.

Deleting data should be a rare occurrence.

[RunTime91]

Thanks June

Just thought I'd ask...

I agree with your deleting sentiment - However these are TempTables

Thus these will get deleted often..

[June7]

Yes, temp tables would be an exception. So these temp tables each hold very different data? Your db is split and these tables are in frontend?

[RunTime91]

Hey June ~

Yes on the table differences - As for the location of the tables - these TmpTbls use to be FE - I have now moved all the tables to the SQL server
and I'm using PassThrough's to manage & manipulate the data.

[June7]

I never used SQLServer and not familiar with PassThroughs. This enables multiple users to utilize the temp tables without interfering with each other?

[RunTime91]

As usual, you pose a good question, June - At the moment there will be only one user for this system at a time - How ever I am trying to avoid any conflict by creating a Qdf - Which is now the source of my next question for the group...