tuncating a table in sql from excel
 

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_lis t_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_StockQuot eAmount)" & _
"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

tuncating a table in sql from excel
 

Nevermind. I think I have found a solution.
.... John


"JohnE" wrote:

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_lis t_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_StockQuot eAmount)" & _
"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