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
504 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

690 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