Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
524 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
[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
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 800 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 200 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

Fill in the form and get your FREE NFR key NOW!

Veeam® is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

604 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