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
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