jfz2004
asked on
How to CREATE TEMP TABLE in VBA
I have a simple Excel VBA that I use ADODB to query against an Excel sheet. In the VBA, I also create a temp table. But it seems that one line has problem.
Here is the code:
Sub Test_Data()
'
Dim abc As String
Dim Conn As New ADODB.Connection
Dim xyz As New ADODB.Recordset
Dim DBPath As String, sconnect As String
DBPath = ThisWorkbook.FullName
sconnect = "Provider=MSDASQL.1;DSN=Ex cel Files;DBQ=" & DBPath & ";HDR=Yes';"
'****************
Conn.Open sconnect
'*****************
abc = "CREATE TABLE #test(tmpa int, tmpb int)"
'abc = "SELECT [Table_Exposures_Input].[I dxName], [Table_Exposures_Input].[P rice] " & _
"INTO #TempPrices"
'
'****************
xyz.Open abc, Conn
Worksheets("Test").Cells(2 , 2).CopyFromRecordset xyz
xyz.Close
'****************
Conn.Close
'******************
End Sub
Here is the code:
Sub Test_Data()
'
Dim abc As String
Dim Conn As New ADODB.Connection
Dim xyz As New ADODB.Recordset
Dim DBPath As String, sconnect As String
DBPath = ThisWorkbook.FullName
sconnect = "Provider=MSDASQL.1;DSN=Ex
'****************
Conn.Open sconnect
'*****************
abc = "CREATE TABLE #test(tmpa int, tmpb int)"
'abc = "SELECT [Table_Exposures_Input].[I
"INTO #TempPrices"
'
'****************
xyz.Open abc, Conn
Worksheets("Test").Cells(2
xyz.Close
'****************
Conn.Close
'******************
End Sub
Something like this?
Sub Test_Data()
'
Dim abc As String
Dim Conn As New ADODB.Connection
Dim xyz As New ADODB.Recordset
Dim DBPath As String, sconnect As String
DBPath = ThisWorkbook.FullName
sconnect = "Provider=MSDASQL.1;DSN=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"
'****************
Conn.Open sconnect
'*****************
abc = "CREATE TABLE #test(tmpa int, tmpb int) INSERT INTO #test VALUES (1,2)"
Conn.Execute abc
abc = "SELECT * FROM #test"
xyz.Open abc, Conn
Worksheets("Test").Cells(2, 2).CopyFromRecordset xyz
xyz.Close
'****************
Conn.Close
'******************
End Sub
ASKER
James,
Thanks. tested your code, it gives an syntax error at
Conn.Execute abc
Jennifer
Thanks. tested your code, it gives an syntax error at
Conn.Execute abc
Jennifer
ASKER
philip,
Thanks for the help. But TempPrices is a table I would like to create.
After creating it, I would like to select from items from another table and insert into the tempprice table.
Thanks a lot.
Jennifer
Thanks for the help. But TempPrices is a table I would like to create.
After creating it, I would like to select from items from another table and insert into the tempprice table.
Thanks a lot.
Jennifer
Anything more specific in the error?
I've tested that the SQL is valid when opening an ADO connection to one of my SQL server instances. Perhaps the OLE DB provider does not allow the creation of temporary tables when connecting to Excel as a data source?
I've tested that the SQL is valid when opening an ADO connection to one of my SQL server instances. Perhaps the OLE DB provider does not allow the creation of temporary tables when connecting to Excel as a data source?
Yes, so you create the table:
abc = "CREATE TABLE #test(tmpa int, tmpb int)"
and insert it into the tempprice table
abc = "INSERT INTO #TempPrices(tmpa, tmpb) " & _
"SELECT [IdxName], [Price] From [Table_Exposures_Input]"
You need to use INSERT INTO / SELECT instead of SELECT / INTO, as the later inserts data into a NEW table, whereas you have already created the table, and want in INSERT it into the existing table.
abc = "CREATE TABLE #test(tmpa int, tmpb int)"
and insert it into the tempprice table
abc = "INSERT INTO #TempPrices(tmpa, tmpb) " & _
"SELECT [IdxName], [Price] From [Table_Exposures_Input]"
You need to use INSERT INTO / SELECT instead of SELECT / INTO, as the later inserts data into a NEW table, whereas you have already created the table, and want in INSERT it into the existing table.
ASKER
James,
Thanks. I am not trying to access to a SQL Server. The Query is against a Excel file.
Jennifer
Thanks. I am not trying to access to a SQL Server. The Query is against a Excel file.
Jennifer
ASKER
Philip,
Thanks. Now my code looks like this but it still has the same error at the line xyz.Open abc, Conn (highlighted yellow) with
"Syntax error in INSERT INTO statement."
Sub Test_Data()
'
Dim abc As String
Dim Conn As New ADODB.Connection
Dim xyz As New ADODB.Recordset
Dim DBPath As String, sconnect As String
DBPath = ThisWorkbook.FullName
sconnect = "Provider=MSDASQL.1;DSN=Ex cel Files;DBQ=" & DBPath & ";HDR=Yes';"
'****************
Conn.Open sconnect
'*****************
abc = "CREATE TABLE #TempPrices(tmpa int, tmpb int)"
abc = "INSERT INTO #TempPrices(tmpa, tmpb) " & _
"SELECT [IdxName], [Price] From [Table_Exposures_Input]"
'****************
xyz.Open abc, Conn
Worksheets("Test").Cells(2 , 2).CopyFromRecordset xyz
xyz.Close
'****************
Conn.Close
'******************
End Sub
Thanks. Now my code looks like this but it still has the same error at the line xyz.Open abc, Conn (highlighted yellow) with
"Syntax error in INSERT INTO statement."
Sub Test_Data()
'
Dim abc As String
Dim Conn As New ADODB.Connection
Dim xyz As New ADODB.Recordset
Dim DBPath As String, sconnect As String
DBPath = ThisWorkbook.FullName
sconnect = "Provider=MSDASQL.1;DSN=Ex
'****************
Conn.Open sconnect
'*****************
abc = "CREATE TABLE #TempPrices(tmpa int, tmpb int)"
abc = "INSERT INTO #TempPrices(tmpa, tmpb) " & _
"SELECT [IdxName], [Price] From [Table_Exposures_Input]"
'****************
xyz.Open abc, Conn
Worksheets("Test").Cells(2
xyz.Close
'****************
Conn.Close
'******************
End Sub
Try adding
SET NOCOUNT ON
to the beginning of your SQL.
SET NOCOUNT ON
to the beginning of your SQL.
ASKER
Thanks, Phillip,
now my code looks like this but still the same error:
Sub Test_Data()
'
Dim abc As String
Dim Conn As New ADODB.Connection
Dim xyz As New ADODB.Recordset
Dim DBPath As String, sconnect As String
DBPath = ThisWorkbook.FullName
sconnect = "Provider=MSDASQL.1;DSN=Ex cel Files;DBQ=" & DBPath & ";HDR=Yes';"
'****************
Conn.Open sconnect
'*****************
abc = "SET NOCOUNT ON CREATE TABLE #TempPrices(tmpa int, tmpb int)"
abc = "INSERT INTO #TempPrices(tmpa, tmpb) " & _
"SELECT [IdxName], [Price] From [Table_Exposures_Input]"
'
'****************
xyz.Open abc, Conn
Worksheets("Test").Cells(2 , 2).CopyFromRecordset xyz
xyz.Close
'****************
Conn.Close
'******************
End Sub
now my code looks like this but still the same error:
Sub Test_Data()
'
Dim abc As String
Dim Conn As New ADODB.Connection
Dim xyz As New ADODB.Recordset
Dim DBPath As String, sconnect As String
DBPath = ThisWorkbook.FullName
sconnect = "Provider=MSDASQL.1;DSN=Ex
'****************
Conn.Open sconnect
'*****************
abc = "SET NOCOUNT ON CREATE TABLE #TempPrices(tmpa int, tmpb int)"
abc = "INSERT INTO #TempPrices(tmpa, tmpb) " & _
"SELECT [IdxName], [Price] From [Table_Exposures_Input]"
'
'****************
xyz.Open abc, Conn
Worksheets("Test").Cells(2
xyz.Close
'****************
Conn.Close
'******************
End Sub
Try SET NOCOUNT ON; with a semicolon.
ASKER
Thanks. Just added the ";" but still get the same syntax error:
Sub Test_Data()
'
Dim abc As String
Dim Conn As New ADODB.Connection
Dim xyz As New ADODB.Recordset
Dim DBPath As String, sconnect As String
DBPath = ThisWorkbook.FullName
sconnect = "Provider=MSDASQL.1;DSN=Ex cel Files;DBQ=" & DBPath & ";HDR=Yes';"
'****************
Conn.Open sconnect
'*****************
abc = "SET NOCOUNT ON; CREATE TABLE #TempPrices(tmpa int, tmpb int); "
abc = "INSERT INTO #TempPrices(tmpa, tmpb) " & _
"SELECT [IdxName], [Price] From [Table_Exposures_Input]"
'
'****************
xyz.Open abc, Conn
Worksheets("Test").Cells(2 , 2).CopyFromRecordset xyz
xyz.Close
'****************
Conn.Close
'******************
End Sub
Sub Test_Data()
'
Dim abc As String
Dim Conn As New ADODB.Connection
Dim xyz As New ADODB.Recordset
Dim DBPath As String, sconnect As String
DBPath = ThisWorkbook.FullName
sconnect = "Provider=MSDASQL.1;DSN=Ex
'****************
Conn.Open sconnect
'*****************
abc = "SET NOCOUNT ON; CREATE TABLE #TempPrices(tmpa int, tmpb int); "
abc = "INSERT INTO #TempPrices(tmpa, tmpb) " & _
"SELECT [IdxName], [Price] From [Table_Exposures_Input]"
'
'****************
xyz.Open abc, Conn
Worksheets("Test").Cells(2
xyz.Close
'****************
Conn.Close
'******************
End Sub
Change
abc = "INSERT INTO #TempPrices(tmpa, tmpb) " & _
"SELECT [IdxName], [Price] From [Table_Exposures_Input]"
to
abc = abc & "INSERT INTO #TempPrices(tmpa, tmpb) " & _
"SELECT [IdxName], [Price] From [Table_Exposures_Input]"
abc = "INSERT INTO #TempPrices(tmpa, tmpb) " & _
"SELECT [IdxName], [Price] From [Table_Exposures_Input]"
to
abc = abc & "INSERT INTO #TempPrices(tmpa, tmpb) " & _
"SELECT [IdxName], [Price] From [Table_Exposures_Input]"
ASKER
Thanks! I changed according to your suggestion. The error message changed to:
[ODBC Excel Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
here is the code:
Sub Test_Data()
'
Dim abc As String
Dim Conn As New ADODB.Connection
Dim xyz As New ADODB.Recordset
Dim DBPath As String, sconnect As String
DBPath = ThisWorkbook.FullName
sconnect = "Provider=MSDASQL.1;DSN=Ex cel Files;DBQ=" & DBPath & ";HDR=Yes';"
'****************
Conn.Open sconnect
'*****************
abc = "SET NOCOUNT ON; CREATE TABLE #TempPrices(tmpa int, tmpb int); "
abc = abc & "INSERT INTO #TempPrices(tmpa, tmpb) " & _
"SELECT [IdxName], [Price] From [Table_Exposures_Input]"
'
'****************
xyz.Open abc, Conn
Worksheets("Test").Cells(2 , 2).CopyFromRecordset xyz
xyz.Close
'****************
Conn.Close
'******************
End Sub
[ODBC Excel Driver] Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.
here is the code:
Sub Test_Data()
'
Dim abc As String
Dim Conn As New ADODB.Connection
Dim xyz As New ADODB.Recordset
Dim DBPath As String, sconnect As String
DBPath = ThisWorkbook.FullName
sconnect = "Provider=MSDASQL.1;DSN=Ex
'****************
Conn.Open sconnect
'*****************
abc = "SET NOCOUNT ON; CREATE TABLE #TempPrices(tmpa int, tmpb int); "
abc = abc & "INSERT INTO #TempPrices(tmpa, tmpb) " & _
"SELECT [IdxName], [Price] From [Table_Exposures_Input]"
'
'****************
xyz.Open abc, Conn
Worksheets("Test").Cells(2
xyz.Close
'****************
Conn.Close
'******************
End Sub
You may want to try creating a Global temp table (use ## in the name). You can also try debugging your code by creating a regular table and then go to a temp table.
ASKER
I just checked my references and found that it is using 6.1 library. So I switched it to ADO 2.8. Then I got compile error at Dim Conn As New ADODB.Connection.
ASKER
Thanks, leonstryker.
How to create a global temp table? I am not very familiar with VBA.
How to create a global temp table? I am not very familiar with VBA.
Use ## in the table name instead of #.
ASKER
Do you mean like these?
abc = "SET NOCOUNT ON; CREATE TABLE ##TempPrices(tmpa int, tmpb int); "
abc = abc & "INSERT INTO ##TempPrices(tmpa, tmpb) " & _
"SELECT [IdxName], [Price] From [Table_Exposures_Input]"
Just tried the above, got the same syntax error.
Thanks.
Jennifer
abc = "SET NOCOUNT ON; CREATE TABLE ##TempPrices(tmpa int, tmpb int); "
abc = abc & "INSERT INTO ##TempPrices(tmpa, tmpb) " & _
"SELECT [IdxName], [Price] From [Table_Exposures_Input]"
Just tried the above, got the same syntax error.
Thanks.
Jennifer
ASKER
So? Is there no solution?
Have you tried to create a regular table?
ASKER
What do you mean "regular table"?
ASKER
Any other suggestions? Anyone, please?
Normal everyday SQL table. Not a temporary one.
ASKER
For normal tables, I can create in SQL Server manually.
This is trying to create a temp table in Excel.
This is trying to create a temp table in Excel.
Yes, but the SQL works similarly for temp tables and regular tables. At the moment I am thinking you may not have permissions or some such thing to create tables at all.
ASKER
So, is this kind of operation doable -- creating temp table in Excel?
Doable, yes. But, let me ask you a different question, why do you need to create it? Why are you not using a store procedure or using the spreadsheet itself? What is it you are trying to achieve with this Temp table?
ASKER
Thanks, leonstryker.
The reason for creating a temp table is that I need to put data from different tables into this temp table for later use.
Why can't I have permission to create a table in a Excel that I created myself?
How to check the permission?
The reason for creating a temp table is that I need to put data from different tables into this temp table for later use.
Why can't I have permission to create a table in a Excel that I created myself?
How to check the permission?
ASKER
Do you think I should ask this question in the database section or is there a VBA SQL section?
ASKER
Sorry, I thought you were working with a SQL Server as your backend and you are not. It seems you are just trying to create a temp table in memory, that is not going to happen. Not possible.
ASKER
Yes, I am not working with SQLserver. Why is it not possible?
Thanks.
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Oh, is that so?
but...but...no one mentioned this in books or anywhere.
but...but...no one mentioned this in books or anywhere.
ASKER
How did you know of it?
The syntax you are using is not part of VBA. Its part of ADO. Before ADO there was RDO and DAO and before that there was ODBC. What you are looking to do was never part of VBA. How do I know it? 15+ years of coding VBA tells me so.
abc = "INSERT INTO #TempPrices " & _
"SELECT [Table_Exposures_Input].[I
or
abc = "INSERT INTO #TempPrices(tmpa, tmpb) " & _
"SELECT [Table_Exposures_Input].[I
But if [Table_Exposures_Input] is a table, then it should be
abc = "INSERT INTO #TempPrices " & _
"SELECT [IdxName], [Price] From [Table_Exposures_Input]"
or
abc = "INSERT INTO #TempPrices(tmpa, tmpb) " & _
"SELECT [IdxName], [Price] From [Table_Exposures_Input]"