tuncating a table in sql from excel

J

JohnE

I have the following code in an excel module. The line that has "Truncate
Table" in it is where it is failing. I am at a loss as to why and have not
been able to fix it.

Sub Worksheet_Change()
Dim conn As Object

Set conn = CreateObject("ADODB.Connection")

conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and
Settings\jevans\Desktop\TestFolder\D_dd_ticker_list_fds.xls;" & _
"Extended Properties='Excel 8.0;HDR=Yes'"

conn.Execute "Truncate Table [ODBC;Driver={SQL
Server};Server=Mobile29;Database=StockQuote;].tbl_StockQuote;"

conn.Execute "Insert Into [ODBC;Driver={SQL Server}; " & _
"Server=Mobile29; " & _

"Database=StockQuote;].tbl_StockQuote(stq_StockQuoteSymbol,stq_StockQuoteAmount)" & _
"Select
stq_StockQuoteSymbol,stq_StockQuoteAmount From [Sheet1$];"

End Sub


If I take out the "conn.execute truncate table" line, the coding works fine.
But it keeps adding to the table when I need to remove what is in the table.
Truncate should work but it isn't.

If anyone reviewing this sees what is wrong, let me know, and thanks for
reviewing and commenting.

.... john
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top