Solved

how do I pass multiple ADO recordsets as ADO parameters when calling a SQL server 2008 stored procedure in VBA

Posted on 2011-03-24
11
488 Views
Last Modified: 2012-08-13
how do I pass multiple ADO recordsets as ADO parameters when calling a SQL server 2008 stored procedure in VBA
0
Comment
Question by:Vincent_Monaghan
  • 4
  • 4
  • 3
11 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 35210707
Are you trying to pass an ADO recordset INTO a store procedure? If so then, it can't be done.

You can pass a string list, and such.

Please explain a bit more on what you are trying to do.
0
 

Author Comment

by:Vincent_Monaghan
ID: 35210771
That's exactly what i am trying to do. What I am trying to acheive is load a set of costing data from an app that uses SQL Server 2008 as it's RDBMS into Excel 2007 where it is updated, then take the now processed set and send pass it to a sp tobe written back to the data tables. Surely this is not new and there must be a way to acheive this.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 35210907
You are basically returning a recordset to the spreadsheet. The data is updated and then you want to upload it back. There are three ways to do this:

One is to maintain an open connection to the database with your recordset and the excetute the Update method of the recordset to push the data back the SQL Server. This most likely is not going to work with a store procedure.

The second method is to concatenate a SQL Update (or a Delete and Insert) statement and run it with VBA using ADO. This can be done with a store procedure but you will need to run it one for each record most likely.

The third method is to create a data file (it may be the original spreadsheet where the recordset was first displayed) and upload it to the database. You would most likely have to delete those records first.

How much data are we talking about?
0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:Vincent_Monaghan
ID: 35211650
No more than 3000 rows.

The third method appeals most. The sp handles the insert/delete job I just need to get the data back into somehow.
0
 
LVL 29

Accepted Solution

by:
leonstryker earned 200 total points
ID: 35214671
Ok, here are a few links which show you the BULK INSERT commands from the databse. All you would need is put them into a store procedure and kick that off from your VBA code:

http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
http://msdn.microsoft.com/en-us/library/ms188365.aspx

Leon
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35215233
>>how do I pass multiple ADO recordsets as ADO parameters when calling a SQL server 2008 stored procedure in VBA <<
As leonstryker has indicated you cannot do this with ADO.  For what it is worth, it is however supported using ADO.NET.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 50 total points
ID: 35215256
You can also convert the recordset to an Xml document and pass the Xml to the Stored Procedure as a parameter.  Than it becomes trivial to query the Xml in the Stored Procedure using XQuery.
0
 

Author Comment

by:Vincent_Monaghan
ID: 35215464
Hi Leon

The second option sugests the following statement in SQl Server:

insert INTO tbL_excel
SELECT *
FROM OPENROWSET(‘Microsoft.Jet.OLEDB.4.0',
‘Excel 8.0;Database=\\pc31\C\Testexcel.xls’,
‘SELECT * FROM [Sheet1$]‘)

Modifying this to use the Microsoft.ACE.OLEDB.12.0 Provider and using a calling sp which in turn can be called from Excel should do the trick nicely.

Thank you

0
 

Author Closing Comment

by:Vincent_Monaghan
ID: 35215506
Thanks All
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 35215612
Thanks for the grade.

Hi Anthony. Long time no see :)
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 35217095
Leon,

It has been a while.  I trust all is well with you.

Anthony
0

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

820 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