Update returns "Invalid object name"

Hi

I will appreciate a help with what's missing in this code.

I am trying to extract data from a database using VBA, then create 2 temporary tables (#AmountCount1 and #AmountCount2), after which I want to update one #AmountCount2.

The extraction works fine, I have been able to extract CustomerID, CustomerType, CustomerName and insert into the Excel sheet1, however, I can not update temporary table (#AmountCount2). The Update code returns “invalid object name”.




Sub CustomerExtract()

Dim ws As Worksheet

Set ws = Worksheets("Sheet1")

Worksheets("Sheet").Range("A2:IV65536").ClearContents


sSQLDB = "CReport"
mstrOLEDBConnect = "Provider=SQLOLEDB.1;" & _
"Data Source=NNN-XXX-30;" & _
"Initial Catalog=" & sSQLDB & ";" & _
"Integrated Security=SSPI"

Set conx = New ADODB.Connection
conx.ConnectionString = mstrOLEDBConnect
conx.ConnectionTimeTime = 0
conx.CommandTimeTime = 0
conx.Open


myData = "CREATE Table #AmountCount1 (CustomerID int, CustomerType varchar(15), CustomerName varchar(1000),SName varchar(8), AmountCount int) "
myData = "INSERT INTO #AmountCount1 (CustomerID, CustomerType, CustomerName, SName, AmountCount) "
myData = "SELECT CustomerID, CustomerType, CustomerName, SName, COUNT(SName) AS AmountCount "
myData = myData & "FROM dbo.vw_KPIAmount "
myData = myData & "GROUP BY CustomerID, CustomerType, CustomerName, SName "


myData = "CREATE TABLE #AmountCount2 (CustomerID int, CustomerType varchar(15), CustomerName varchar(1000), Time1 int, Time2 int, Time4 int) "
myData = "INSERT INTO #AmountCount2 (CustomerID, CustomerType, CustomerName) "
myData = "SELECT DISTINCT CustomerID, CustomerType, CustomerName FROM dbo.vw_KPIAmount "

Set Rst = conx.Execute(myData)

Worksheets("Sheet1").Range("A2").CopyFromRecordset Rst


myData1 = "UPDATE #AmountCount2 "
myData1 = myData1 & " SET Time1 = AmountCount "
myData1 = myData1 & " FROM #AmountCount2 a, #AmountCount1 b "
myData1 = myData1 & " WHERE a.CustomerID = b.CustomerID "
myData1 = myData1 & " AND SName = 'Time1' "


Set Rst = conx.Execute(myData1)

Worksheets("Sheet1").Range("g2").CopyFromRecordset Rst


myData1 = "SELECT Time1 "
myData1 = myData1 & "FROM #AmountCount2"
myData1 = myData1 & "ORDER BY CustomerID"

myData1 = myData1 & "DROP TABLE #AmountCount1"
myData1 = myData1 & "DROP TABLE #AmountCount2"

End Sub