Issue with temp table and insert into statement in VBA (ADODB query)

Question

I use VBA and the ADODB to query against a SQL Server server and having trouble with INSERT INTO statement in the following string:

StrQuery = "INSERT INTO #temp_ImprtSampVol Values ('RBM','Care: Admin',28)," & _
" ('RRA','Care: Quality',29)"

The error message is "Incorrect syntax near ','. I'm sure something is wrong with the above statement but I'm struggling to figure our what might be wrong.

I would like to insert the following values into the table (it does work perfectly in SQL):

INSERT INTO #temp_ImprtSampVol 
VALUES 
('RBM','Care: Admin',28),
('RBM','Care: Exit',31),
('RBM','Care: Other',22),
('RRA','Care: Quality',29),
('RRA','Care: Finance',37),

My code is as follows:

Sub VBAFromSQL()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String

ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=HIDDEN_Outputs;Data Source=HIDDEN;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False"

cnn.Open ConnectionString
cnn.CommandTimeout = 900

StrQuery = "IF OBJECT_ID('tempdb..#temp_MthToDt') IS NOT NULL drop table 
#temp_MthToDt" & _
" IF OBJECT_ID('tempdb..#temp_ImprtSampVol') IS NOT NULL drop table 
#temp_ImprtSampVol" & _
" IF OBJECT_ID('tempdb..#temp_T1') IS NOT NULL drop table #temp_T2AuditData"

StrQuery = "DECLARE @Last_Day_Loaded VARCHAR(30) = '2018-10-03';" & _
" DECLARE @Start_of_Sample VARCHAR(30) = '2018-10-03';" & _
" DECLARE @End_of_Sample VARCHAR(30) = '2018-10-03';" & _
" DECLARE @Days_Data_so_far INT = 03;" & _
" DECLARE @First_Day_Month VARCHAR(30) = '2018-10-01';" & _
" DECLARE @Working_Days_in_Month INT = 23;" & _
" DECLARE @Days_in_Month INT = 31;" & _
" DECLARE @BBE_Sample_Minimum INT = 10;" & _
" DECLARE @BBE_Sample_adjustment_multiplier decimal(5,2) = 1.0;"

StrQuery = "CREATE TABLE #temp_ImprtSampVol (" & _
" CIS VARCHAR(10) NOT NULL," & _
" RD VARCHAR(40) NOT NULL," & _
" Sample_to_send INT NOT NULL );"

StrQuery = "INSERT INTO #temp_ImprtSampVol Values ('RBM','Care: Admin',28)," & _
" ('RRA','Care: Quality',29)"

rst.Open StrQuery, cnn
Sheets(1).Range("A2").CopyFromRecordset rst
End Sub

Can you please advise how to rectify this? Thanks in advance.

By far the best way to deal with this is to stop adding all this code in your application. Create a stored procedure and put all the database logic in the database. — Sean Lange
– Sean Lange, Commented Oct 22, 2018 at 15:01
Thanks for your comment Sean. The problem is, I have no permission to create a new view in SQL, therefore trying to use this code in VBA. I also tried to create an OLE DB Query Connection but was struggling to get this working with temp tables. — SeT
– SeT, Commented Oct 22, 2018 at 15:06
I also tried to simplify my query but had an error message 'too many line continuations' — SeT
– SeT, Commented Oct 22, 2018 at 15:09
Well....your query does not return anything. It declares a bunch of variables (that don't get used), then it creates a temp table and populates it. To get that data in a result set you need a select statement at the end of your code. — Sean Lange
– Sean Lange, Commented Oct 22, 2018 at 16:11
Thanks for getting back to me Sean. There is a select statement after this but the whole code is huge so I just copy and paste it in some parts. I managed to rectify the above issue, however I'm now having problems with the select statement. I will provide the details below shortly. — SeT
– SeT, Commented Oct 23, 2018 at 11:04

dusfor63 · Accepted Answer · 2018-10-22 17:35:51Z

As Sean Lange commented an insert statement doesn't return anything usefull but a confirmation of the query result state, so it errors or not.

According to the sql-code, I assume you are querying against a SQL-Server and having no access to a DB with necessary access rights to it you are using the tempdb :-) fair enough.

But, the problem in your code is that only the last part starting from the last StrQry, namely the one with your "Insert into..." until "Sheets(1).Range...." has any effect. The other previous StrQry assignments are simply overwritten by the last one.

Your problem with these many continuations can be worked around by simply removing them where possible according to the T-SQL-syntax and make long lines as the SQL-Server handles the codelines on its own.

So, the code may look like this

Sub VBAFromSQL()
Dim cnn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim ConnectionString As String
Dim StrQuery As String

ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=True;Initial Catalog=HIDDEN_Outputs;Data Source=HIDDEN;Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;Use Encryption for Data=False;Tag with column collation when possible=False"

cnn.Open ConnectionString
cnn.CommandTimeout = 900

StrQuery = "IF OBJECT_ID('tempdb..#temp_MthToDt') IS NOT NULL drop table #temp_MthToDt; IF OBJECT_ID('tempdb..#temp_ImprtSampVol') IS NOT NULL drop table #temp_ImprtSampVol; IF OBJECT_ID('tempdb..#temp_T1') IS NOT NULL drop table #temp_T2AuditData; DECLARE @Last_Day_Loaded VARCHAR(30) = '2018-10-03', @Start_of_Sample VARCHAR(30) = '2018-10-03',@End_of_Sample VARCHAR(30) = '2018-10-03', @Days_Data_so_far INT = 03,@First_Day_Month VARCHAR(30) = '2018-10-01',@Working_Days_in_Month INT = 23,@Days_in_Month INT = 31,@BBE_Sample_Minimum INT = 10,@BBE_Sample_adjustment_multiplier decimal(5,2) = 1.0; CREATE TABLE #temp_ImprtSampVol (CIS VARCHAR(10) NOT NULL,RD VARCHAR(40) NOT NULL,Sample_to_send INT NOT NULL );INSERT INTO #temp_ImprtSampVol Values ('RBM','Care: Admin',28),('RRA','Care: Quality',29);"

rst.Open StrQuery, cnn
Sheets(1).Range("A2").CopyFromRecordset rst
End Sub

What is not clear to me is what you expected from the insert statement to be delivered and shown in A2 of Sheet1. If you wanted to show the the inserted lines you have to append either

;select * from #temp_ImprtSampVol;

or

output inserted.*;

at the end of the StrQry. Mind the missing ";" in front of "output" as it belongs to the insert statement.

Thanks for looking into this dusfor63. I have made the necessary changes as per your advise and everything seems to be fine. However, now I'm having challenges with the SELECT statement. I will provide the details below shortly. Thanks again

dusfor63 · Accepted Answer · 2018-10-23 19:58:54Z

Excuse me. I had to be more precise with this. Before attaching the last mentioned parts you have to remove the ";" at the end of the statement in your StrQry = ... . So it the looks like this:

StrQry = "... ;INSERT INTO #temp_ImprtSampVol Values ('RBM','Care: Admin',28),('RRA','Care: Quality',29) output inserted.*;"

or

StrQry = "... ;INSERT INTO #temp_ImprtSampVol Values ('RBM','Care: Admin',28),('RRA','Care: Quality',29);select * from #temp_ImprtSampVol;"

I'm wondering why you are dropping these other tables that have never been used anyhow and why there are declared all this variables, but I think you want to extend the query. Be advised, even this way has it's limitations and you might better ask your DBA for some space with let's say, a small database where you can create SPs on your own. :-)

Collectives™ on Stack Overflow

Issue with temp table and insert into statement in VBA (ADODB query)

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related