Hi<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com
fficeffice" /><o
></o>
<o> </o>
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. <o></o>
<o> </o>
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”.<o></o>
<o> </o>
<o> </o>
<o> </o>
<o> </o>
Sub CustomerExtract()<o></o>
<o> </o>
Dim ws As Worksheet<o></o>
<o> </o>
Set ws = Worksheets("Sheet1")<o></o>
<o> </o>
Worksheets("Sheet").Range("A2:IV65536").ClearContents<o></o>
<o> </o>
<o> </o>
sSQLDB = "CReport"<o></o>
mstrOLEDBConnect = "Provider=SQLOLEDB.1;" & _<o></o>
"Data Source=NNN-XXX-30;" & _<o></o>
"Initial Catalog=" & sSQLDB & ";" & _<o></o>
"Integrated Security=SSPI"<o></o>
<o> </o>
Set conx = New ADODB.Connection<o></o>
conx.ConnectionString = mstrOLEDBConnect<o></o>
conx.ConnectionTimeTime = 0<o></o>
conx.CommandTimeTime = 0<o></o>
conx.Open<o></o>
<o> </o>
<o> </o>
myData = "CREATE Table #AmountCount1 (CustomerID int, CustomerType varchar(15), CustomerName varchar(1000),SName varchar(8), AmountCount int) "<o></o>
myData = "INSERT INTO #AmountCount1 (CustomerID, CustomerType, CustomerName, SName, AmountCount) "<o></o>
myData = "SELECT CustomerID, CustomerType, CustomerName, SName, COUNT(SName) AS AmountCount "<o></o>
myData = myData & "FROM dbo.vw_KPIAmount "<o></o>
myData = myData & "GROUP BY CustomerID, CustomerType, CustomerName, SName "<o></o>
<o> </o>
<o> </o>
myData = "CREATE TABLE #AmountCount2 (CustomerID int, CustomerType varchar(15), CustomerName varchar(1000), Time1 int, Time2 int, Time4 int) "<o></o>
myData = "INSERT INTO #AmountCount2 (CustomerID, CustomerType, CustomerName) "<o></o>
myData = "SELECT DISTINCT CustomerID, CustomerType, CustomerName FROM dbo.vw_KPIAmount "<o></o>
<o> </o>
Set Rst = conx.Execute(myData)<o></o>
<o> </o>
Worksheets("Sheet1").Range("A2").CopyFromRecordset Rst<o></o>
<o> </o>
<o> </o>
myData1 = "UPDATE #AmountCount2 "<o></o>
myData1 = myData1 & " SET Time1 = AmountCount "<o></o>
myData1 = myData1 & " FROM #AmountCount2 a, #AmountCount1 b "<o></o>
myData1 = myData1 & " WHERE a.CustomerID = b.CustomerID "<o></o>
myData1 = myData1 & " AND SName = 'Time1' "<o></o>
<o> </o>
<o> </o>
Set Rst = conx.Execute(myData1)<o></o>
<o> </o>
Worksheets("Sheet1").Range("g2").CopyFromRecordset Rst<o></o>
<o> </o>
<o> </o>
myData1 = "SELECT Time1 "<o></o>
myData1 = myData1 & "FROM #AmountCount2"<o></o>
myData1 = myData1 & "ORDER BY CustomerID"<o></o>
<o> </o>
myData1 = myData1 & "DROP TABLE #AmountCount1"<o></o>
myData1 = myData1 & "DROP TABLE #AmountCount2"<o></o>
<o> </o>
End Sub
fficeffice" /><o></o><o
> </o>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. <o
></o><o
> </o>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”.<o
></o><o
> </o><o
> </o><o
> </o><o
> </o>Sub CustomerExtract()<o
></o><o
> </o>Dim ws As Worksheet<o
></o><o
> </o>Set ws = Worksheets("Sheet1")<o
></o><o
> </o>Worksheets("Sheet").Range("A2:IV65536").ClearContents<o
></o><o
> </o><o
> </o>sSQLDB = "CReport"<o
></o>mstrOLEDBConnect = "Provider=SQLOLEDB.1;" & _<o
></o>"Data Source=NNN-XXX-30;" & _<o
></o>"Initial Catalog=" & sSQLDB & ";" & _<o
></o>"Integrated Security=SSPI"<o
></o><o
> </o>Set conx = New ADODB.Connection<o
></o>conx.ConnectionString = mstrOLEDBConnect<o
></o>conx.ConnectionTimeTime = 0<o
></o>conx.CommandTimeTime = 0<o
></o>conx.Open<o
></o><o
> </o><o
> </o>myData = "CREATE Table #AmountCount1 (CustomerID int, CustomerType varchar(15), CustomerName varchar(1000),SName varchar(8), AmountCount int) "<o
></o>myData = "INSERT INTO #AmountCount1 (CustomerID, CustomerType, CustomerName, SName, AmountCount) "<o
></o>myData = "SELECT CustomerID, CustomerType, CustomerName, SName, COUNT(SName) AS AmountCount "<o
></o>myData = myData & "FROM dbo.vw_KPIAmount "<o
></o>myData = myData & "GROUP BY CustomerID, CustomerType, CustomerName, SName "<o
></o><o
> </o><o
> </o>myData = "CREATE TABLE #AmountCount2 (CustomerID int, CustomerType varchar(15), CustomerName varchar(1000), Time1 int, Time2 int, Time4 int) "<o
></o>myData = "INSERT INTO #AmountCount2 (CustomerID, CustomerType, CustomerName) "<o
></o>myData = "SELECT DISTINCT CustomerID, CustomerType, CustomerName FROM dbo.vw_KPIAmount "<o
></o><o
> </o>Set Rst = conx.Execute(myData)<o
></o><o
> </o>Worksheets("Sheet1").Range("A2").CopyFromRecordset Rst<o
></o><o
> </o><o
> </o>myData1 = "UPDATE #AmountCount2 "<o
></o>myData1 = myData1 & " SET Time1 = AmountCount "<o
></o>myData1 = myData1 & " FROM #AmountCount2 a, #AmountCount1 b "<o
></o>myData1 = myData1 & " WHERE a.CustomerID = b.CustomerID "<o
></o>myData1 = myData1 & " AND SName = 'Time1' "<o
></o><o
> </o><o
> </o>Set Rst = conx.Execute(myData1)<o
></o><o
> </o>Worksheets("Sheet1").Range("g2").CopyFromRecordset Rst<o
></o><o
> </o><o
> </o>myData1 = "SELECT Time1 "<o
></o>myData1 = myData1 & "FROM #AmountCount2"<o
></o>myData1 = myData1 & "ORDER BY CustomerID"<o
></o><o
> </o>myData1 = myData1 & "DROP TABLE #AmountCount1"<o
></o>myData1 = myData1 & "DROP TABLE #AmountCount2"<o
></o><o
> </o>End Sub
