By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,222 Members | 1,610 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,222 IT Pros & Developers. It's quick & easy.

Passing Parameters to a stored Proc in access

P: 5
I am a new bee to VBA. I have created an app which outputs the data from access to an Excel spread sheet and saves it. I have used the following snippet to perform that action. The following is the code that I have used, and it works fine. However, I have 2 questions

1. A security alert opens up every time I try to write in to the access database. How do I avoid that?
2. The "query1" in the following code is a stored proc in access, which requires a parameter (projnumber). Currently I have to manually type this projecId to pass it to the stored proc. How do I do it programatically?

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> >>>>>>>

Dim strOutputPath As String
Set a = CreateObject("access.application")
a.OpenCurrentDatabase ("W:\Quality Assurance\QA document automation\Database\AutomationDocumentation.mdb")
'a.DoCmd.OpenQuery "Query1"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel3, "query1", "W:\Quality Assurance\QA document automation\Template\TestCaseManagement.xls", False
'a.DoCmd.Close
'a.Quit
Application.Workbooks.Open ("W:\Quality Assurance\QA document automation\Template\TestCaseManagement.xls")
Application.Visible = False
With Workbooks("TestCaseManagement.xls")
.SaveAs ("W:\Quality Assurance\QA document automation\Testcases\" & projnumber & ".xls")
End With

ThisWorkbook.Close (False)
Oct 8 '08 #1
Share this Question
Share on Google+
7 Replies


ADezii
Expert 5K+
P: 8,679
You can use ADO and the Parameters Collection of a Command Objuect to pass Parameters to a Stored Procedure. I'll provide some sample code, involving a Date Parameter, for you to follow:
Expand|Select|Wrap|Line Numbers
  1. Dim cnn As ADODB.Connection
  2. Dim cmd As ADODB.Command
  3. Dim prm As ADODB.Parameter
  4.  
  5. Set cnn = CurrentProject.Connection
  6. Set cmd = New ADODB.Command
  7.  
  8. cmd.ActiveConnection = cnn
  9.  
  10. cmd.CommandText = "<Your Procedure Name>"
  11. cmd.CommandType = adCmdStoredProc
  12.  
  13. Set prm = cmd.CreateParameter("<Parameter Name>", adDBDate, adParamInput, #<Date Value Here>#
  14.  
  15. cmd.Execute
Oct 8 '08 #2

P: 5
Appreciate your reply. I will try this out. Meanwhile can you please help me with Q1 too.

A security alert opens up every time I try to write in to the access database. How do I avoid that?
Oct 8 '08 #3

ADezii
Expert 5K+
P: 8,679
Appreciate your reply. I will try this out. Meanwhile can you please help me with Q1 too.

A security alert opens up every time I try to write in to the access database. How do I avoid that?
  1. What is the specific Security Alert Warning?
  2. What do you mean by 'write in to the Access Database'?
Oct 8 '08 #4

P: 5
Pardon me for sounding naive.

While doing an INSERT in to the database, i get a 'Security Warning' stating -

Opening "<DB path>"
This file may not be safe if it contains code that was intended to harm your computer.
Do you want to open this file or cancel the operation?

when I click on open, the database opens and it performs the INSERT command that is in my code
Oct 9 '08 #5

ADezii
Expert 5K+
P: 8,679
Pardon me for sounding naive.

While doing an INSERT in to the database, i get a 'Security Warning' stating -

Opening "<DB path>"
This file may not be safe if it contains code that was intended to harm your computer.
Do you want to open this file or cancel the operation?

when I click on open, the database opens and it performs the INSERT command that is in my code
Try:
  1. Tools
  2. Macro
  3. Security
  4. Security Level Tab
  5. Set to Low (Read Warning!)
Oct 9 '08 #6

P: 5
Thanks, that worked.

When I am deleting records form a table in access I am getting a prompt as well. Is there a way to disable it?
Oct 20 '08 #7

P: 5
I found out the solution. Sorry for sounding naive.

Thanks anyways

Turn confirmation messages on or off
On the Tools menu, click Options.
Click the Edit/Find tab.
Under Confirm, do one or more of the following:
To display a confirmation message when you change a record, select Record changes.

To display a confirmation message when you delete a database object, select Document deletions.

To display a confirmation message when you run an action query (Access database only), select Action queries.

Note The Record changes and Action queries options don't apply to data access pages.
Oct 20 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.