tuncating a table in sql from excel

  • Thread starter Thread starter JohnE
  • Start date Start date
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
 
Back
Top