Solved

How to CREATE TEMP TABLE in VBA

Posted on 2015-01-23
37
1,942 Views
Last Modified: 2015-01-27
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=Excel Files;DBQ=" & DBPath & ";HDR=Yes';"

'****************
Conn.Open sconnect
'*****************


abc = "CREATE TABLE #test(tmpa int, tmpb int)"

'abc = "SELECT [Table_Exposures_Input].[IdxName], [Table_Exposures_Input].[Price] " & _
"INTO #TempPrices"
'
'****************
xyz.Open abc, Conn

Worksheets("Test").Cells(2, 2).CopyFromRecordset xyz

xyz.Close

'****************


Conn.Close
'******************

End Sub
0
Comment
Question by:jfz2004
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 21
  • 9
  • 5
  • +1
37 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40566273
Possibly could be:

abc = "INSERT INTO #TempPrices " & _
"SELECT [Table_Exposures_Input].[IdxName], [Table_Exposures_Input].[Price] "

or

abc = "INSERT INTO #TempPrices(tmpa, tmpb) " & _
"SELECT [Table_Exposures_Input].[IdxName], [Table_Exposures_Input].[Price] "

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]"
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40566304
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

Open in new window

0
 

Author Comment

by:jfz2004
ID: 40566402
James,
Thanks. tested your code, it gives an syntax error at
Conn.Execute abc

Jennifer
0
Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

 

Author Comment

by:jfz2004
ID: 40566412
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
0
 
LVL 12

Expert Comment

by:James Elliott
ID: 40566442
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?
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40566443
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.
0
 

Author Comment

by:jfz2004
ID: 40566477
James,

Thanks. I am not trying to access to a SQL Server. The Query is against a Excel file.

Jennifer
0
 

Author Comment

by:jfz2004
ID: 40566490
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=Excel 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
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40566511
Try adding

SET NOCOUNT ON

to the beginning of your SQL.
0
 

Author Comment

by:jfz2004
ID: 40566566
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=Excel 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
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40566594
Try SET NOCOUNT ON; with a semicolon.
0
 

Author Comment

by:jfz2004
ID: 40566659
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=Excel 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
0
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40566664
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]"
0
 

Author Comment

by:jfz2004
ID: 40566686
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=Excel 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
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 40566978
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.
0
 

Author Comment

by:jfz2004
ID: 40566991
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.
0
 

Author Comment

by:jfz2004
ID: 40566992
Thanks, leonstryker.

How to create a global temp table? I am not very familiar with VBA.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 40567075
Use ## in the table name instead of #.
0
 

Author Comment

by:jfz2004
ID: 40567092
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
0
 

Author Comment

by:jfz2004
ID: 40567220
So? Is there no solution?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 40567396
Have you tried to create a regular table?
0
 

Author Comment

by:jfz2004
ID: 40570340
What do you mean "regular table"?
0
 

Author Comment

by:jfz2004
ID: 40570359
Any other suggestions? Anyone, please?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 40570652
Normal everyday SQL table. Not a temporary one.
0
 

Author Comment

by:jfz2004
ID: 40570692
For normal tables, I can create in SQL Server manually.
This is trying to create a temp  table in Excel.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 40570705
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.
0
 

Author Comment

by:jfz2004
ID: 40570798
So, is this kind of operation doable -- creating temp table in Excel?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 40570808
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?
0
 

Author Comment

by:jfz2004
ID: 40570906
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?
0
 

Author Comment

by:jfz2004
ID: 40571116
Do you think I should ask this question in the database section or is there a VBA SQL section?
0
 

Author Comment

by:jfz2004
ID: 40571346
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 40571367
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.
0
 

Author Comment

by:jfz2004
ID: 40571372
Yes, I am not working with SQLserver. Why is it not possible?
Thanks.
0
 
LVL 29

Accepted Solution

by:
leonstryker earned 500 total points
ID: 40571390
Temp tables are a database concept. Some databases, like Access, do not even have them. With VBA you can setup arrays and its possible to access various data sources, even text files and Excel spreadsheet with ADO and query them with SQL syntax, but there is no functionality in VBA (or VB) to create an in memory table and query them with SQL syntax.
0
 

Author Comment

by:jfz2004
ID: 40571394
Oh, is that so?

but...but...no one mentioned this in books or anywhere.
0
 

Author Comment

by:jfz2004
ID: 40571395
How did you know of it?
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 40571406
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.
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
populate  some cells after data verification 45 33
Calculate waiting times that spans two days 10 52
Excel 2010 Vlookup 4 32
If condition and Date Ranges 13 63
If you haven’t already, I encourage you to read the first article (http://www.experts-exchange.com/articles/18680/An-Introduction-to-R-Programming-and-R-Studio.html) in my series to gain a basic foundation of R and R Studio.  You will also find the …
Whether you’re a college noob or a soon-to-be pro, these tips are sure to help you in your journey to becoming a programming ninja and stand out from the crowd.
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question