VBA code for Access Parameter query

[SamBalsini]

Hi there,
I am quiet new to this forum and also to VBA. I have created an Access database with a few tables and queries. I linked all queries to an excel sheet to be able to build a reconciliations. The front page of workbook has got three parameters (Start date, finish data and store no). I set the same parameter to all queries in database. I am looking for a VBA code to set those parameters in access queries and update the excel sheets. Any help would be appreciated. Thanks

 

[VBA Geek]

i dont know your specifics, but to give you an idea, it would be something like this

Dim C As ADODB.Command: Set C = New ADODB.Command
Dim mconn As ADODB.Connection: Set mconn = New ADODB.Connection
mconn.Open "your connection string"
With C
    .ActiveConnection = mconn
    .CommandType = adCmdStoredProc
    .CommandText = "your query name"
    .Parameters.Append .CreateParameter("param name", adInteger, adParamInput)
    .Parameters("param name").Value = "your paramater value"
End With
Dim rs As ADODB.Recordset: set rs = new adodb.recordset 
rs.Open C

 

[SamBalsini]

Thanks for replying so quickly. I tried to write the code but still struggling. I managed to write the below code but it will copy and save the parameter table rather than updating any advice would be appreciated.

Sub RunParameterQueries()

'Step 1: Declare your variables
Dim MyDatabase As DAO.Database
Dim MyQueryDef As DAO.QueryDef
Dim MyRecordset As DAO.Recordset
Dim i As Integer

'Step 2: Identify the database and query
Set MyDatabase = DBEngine.OpenDatabase _
("C:\Reconciliation\ Database V1.accdb")
Set MyQueryDef = MyDatabase.QueryDefs("Cards Till Diffs")

'Step 3: Define the Parameters
With MyQueryDef
.Parameters("[Number]") = Range("Store_No").Value
.Parameters("[Start date]") = Range("Date_from").Value
.Parameters("[End date]") = Range("Date_to").Value
End With

'Step 4: Open the query
Set MyRecordset = MyQueryDef.OpenRecordset


'Step 5: Clear previous contents
Sheets("Cards Till Diffs").Select
ActiveSheet.Range("A8:H10000").ClearContents


'Step 6: Copy the recordset to Excel
ActiveSheet.Range("A8").CopyFromRecordset MyRecordset


'Step 7: Save reconciliation
Dim Fname As String
Fname = Range("Store_Name").Value
ActiveWorkbook.SaveCopyAs "C:\Reconciliation\" & "Cash & Banking Review" & " " & Fname & Format(Now(), " dd_mm_yyyy") & ".xlsm"


MsgBox "Reconciliation has finished"

End Sub

 

[VBA Geek]

to update the database you can use an update query
your code above is pulling data from access instead

 

[SamBalsini]

The aim is update the query in access with those parameters and refresh query table in excel and save a copy of workbook.