Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 20th, 2007, 12:27 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default Stored Procedure to Local Access Table

Hi,

   I am trying to retrieve data from a SQL Server stored precedure, and I want to pass two parameters to it, and then dump the data into a local Access table as needed. I have the following code, and the sp works fine, but how would you suggest getting the data? Is this code otherwise correct? The Access database is not connected to the SQL Server as either an adp or with linked sp.

Thanks in advance.


Dim cn As ADODB.Connection
Dim cmdCommand As ADODB.Command
Dim prmStart As ADODB.Parameter
Dim prmEnd As ADODB.Parameter
Dim dtStart As Date
Dim dtEnd As Date

'There will be code to make sure a selection has been made here:
dtStart = Me.cboStartDate
dtEnd = Me.cboEndDate

Set cn = New ADODB.Connection
'maybe a with block her for client side cursor?
cn.Open "Provider=SQLOLEDB;Data Source=servername;Initial Catalog=AAA;UID=username;PWD=password"

Set cmdCommand = New ADODB.Command
cmdCommand.ActiveConnection = cn

cmdCommand.CommandType = adCmdStoredProc
cmdCommand.CommandText = "sp_ContribRep"

Set prmStart = cmdCommand.CreateParameter("@startdate", adDate, adParamInput)
Set prmEnd = cmdCommand.CreateParameter("@enddate", adDate, adParamInput)

prmStart.Value = dtStart
prmEnd.Value = dtEnd
cmdCommand.Parameters.Append prmStart
cmdCommand.Parameters.Append prmEnd

cmdCommand.Execute
'where to next?


mmcdonal
__________________
mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #2 (permalink)  
Old September 21st, 2007, 08:53 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Oxford, , United Kingdom.
Posts: 120
Thanks: 0
Thanked 0 Times in 0 Posts
Default

mmcdonal

The code looks good to me.

You should be able to assign the results of executing the command object to a recordset, i.e.:

    Set rstResult=cmdCommand.Execute

You can then loop through the recordset in the usual manner and transfer the records to a local table.

Braxis
Reply With Quote
  #3 (permalink)  
Old September 21st, 2007, 11:57 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Thanks, so that would look like this?

Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Dim prmStart As ADODB.Parameter
Dim prmEnd As ADODB.Parameter

Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB;Data Source=saw2k3sr01;Initial Catalog=AAA;UID=ft_test;PWD=test_ft1"

Set cmdCommand = New ADODB.Command
cmdCommand.ActiveConnection = cn

cmdCommand.CommandType = adCmdStoredProc
cmdCommand.CommandText = "sp_StoredProcedureName" 'what is the stored procedure name you are creating?

Set prmStart = cmdCommand.CreateParameter("@startdate", adDate, adParamInput)
Set prmEnd = cmdCommand.CreateParameter("@enddate", adDate, adParamInput)

prmStart.Value = dtStart
prmEnd.Value = dtEnd
cmdCommand.Parameters.Append prmStart
cmdCommand.Parameters.Append prmEnd

Set rs = cmdCommand.Execute

mmcdonal
Reply With Quote
  #4 (permalink)  
Old September 21st, 2007, 12:00 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I also found this answer in the Access 2007 VBA Programmer's Reference just now on wrox.books24x7.com. Dang, I should use that more effectively. Thanks loads for you help.

mmcdonal
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Use Temp Table Variable in Stored Procedure rweide SQL Server ASP 2 April 15th, 2011 03:50 PM
Add new Table to Stored Procedure pallone SQL Server 2000 2 February 14th, 2007 12:58 PM
Stored Procedure That Updates Table GailCG Classic ASP Professional 1 January 22nd, 2006 01:11 PM
Build a table from a Stored Procedure rogue248 SQL Server 2000 2 October 7th, 2004 10:06 PM
passing table as a parameter to stored procedure pankaj_daga SQL Server 2000 7 September 30th, 2003 05:11 AM



All times are GMT -4. The time now is 11:20 AM.


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.