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
498 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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 eBook: Backup on AWS

Everything you need to know about backup and disaster recovery with AWS, for FREE!

Question has a verified solution.

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

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
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 demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

740 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