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
473 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
 

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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

920 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now