Passing Parameters to a stored Proc in access

[Yesh]

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)

[ADezii]

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:

Dim cnn As ADODB.Connection
Dim cmd As ADODB.Command
Dim prm As ADODB.Parameter
 
Set cnn = CurrentProject.Connection
Set cmd = New ADODB.Command
 
cmd.ActiveConnection = cnn
 
cmd.CommandText = "<Your Procedure Name>"
cmd.CommandType = adCmdStoredProc
 
Set prm = cmd.CreateParameter("<Parameter Name>", adDBDate, adParamInput, #<Date Value Here>#
 
cmd.Execute

[Yesh]

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?

[ADezii]

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'?

[Yesh]

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

[ADezii]

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!)

[Yesh]

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?

[Yesh]

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.