Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying DROP TABLE in SQL from Excel
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying DROP TABLE in SQL from Excel
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" "Bernard Soh" wrote in message om... 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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Trying DROP TABLE in SQL from Excel
|
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how do i refresh a pivot table drop down list in excel 2003? | Excel Discussion (Misc queries) | |||
table w/ some columns as drop down | Excel Discussion (Misc queries) | |||
Drop down table | Excel Worksheet Functions | |||
Pivot Table drop down | Excel Discussion (Misc queries) | |||
Pivot Table Drop down | Charts and Charting in Excel |