Trying DROP TABLE in SQL from Excel

[Bernard Soh]

Hi all

I am trying to do a DROP TABLE in MSSQL from Excel without success
(well, at least with partial success).

Enclosed is the code:
sSql = "if exists (select * from dbo.sysobjects where id =
object_id(N'tmpCst')) DROP TABLE tmpCst"
Selection.QueryTable.Sql = sSql
' for some reason the query will not run with
BackgroundQuery:=False
Selection.QueryTable.Refresh BackgroundQuery:=True
' loop added but never ends
Do While Selection.QueryTable.Refreshing
Sleep (1000)
Loop


What's happening is that if I run the code with BackgroundQuery:=True,
it executes perfectly, table is dropped. However this means that my
next lines of codes will start executing, and that won't do. So I add
a line to check while query is still Refreshing, if not sleep for
1sec, check again, but it never ends. It will work in debug mode,
meaning if I step thru the .Refreshing code it will actually complete.

I have checked that even at the point when it sleeps for the first
time, the SQL command is executed already (table is dropped) but the
..Refreshing remains True. Only if there is a break will the
..Refreshing change to a False.

Any help here is appreciated.

Regards.

Bernard.

 

[Andy Wiggins]

This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table
* insert records
* select records,
* update records,
* delete records,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.


--
Regards
Andy Wiggins
www.BygSoftware.com
Home of "Save and BackUp",
"The Excel Auditor" and "Byg Tools for VBA"

 

[Jamie Collins]

(e-mail address removed) (Bernard Soh) wrote ...

if I run the code with BackgroundQuery:=True,
it executes perfectly, table is dropped. However this means that my
next lines of codes will start executing, and that won't do. So I add
a line to check while query is still Refreshing, if not sleep for
1sec, check again, but it never ends.

You should consider using ADO to execute the SQL asynchronously.
Here's a hint:

' --- In a class module ---
Option Explicit

Dim WithEvents m_Con As ADODB.Connection

Private Sub m_Con_ExecuteComplete( _
ByVal RecordsAffected As Long, _
ByVal pError As ADODB.Error, _
adStatus As ADODB.EventStatusEnum, _
ByVal pCommand As ADODB.Command, _
ByVal pRecordset As ADODB.Recordset, _
ByVal pConnection As ADODB.Connection)

End Sub

You should also consider whether you actually need a temp table. Often
a derived table/sub-query in SQL code will do the same.

Jamie.

--

 

[Bernard Soh]

Thanks for all the replies. I have figured out the problem I was
facing with ADO which I tried unsuccessfully earlier.

Instead of doing a
Dim cnn as New ADODB.Connection
Dim cmd as New ADODB.Command
(which will give me a user-defined type not defined)

I did a
Dim cnn As Object
Dim cmd As Object

Set cnn = CreateObject("ADODB.Connection")
Set cmd = CreateObject("ADODB.Command")

cnn.ConnectionString = "DSN=mydsn;UID=myUID;PWD=myPWD"
cnn.Open
cmd.ActiveConnection = cnn

cmd.CommandText = "blah blah sql"
cmd.Execute


Thanks for the advises which helped me find the solution.

Regards.

Bernard.