Running An Access Query Using Excel VBA

[RunTime91]

Hello...

I have the following code in my Excel VBE attached to a button that when clicked runs a query in Access then places the results into an Excel sheet

It works perfectly, but only so far as I hard code the date of the data I want returned in the Access query.

Naturally, I need the query to return data based upon a date entered into a cell in my Excel sheet.

I've tried several solutions (using parameters and other types of variables) which have resulted in several types of errors.

The Sheet.Range where the date will be entered is: Sheets("DataEntry").Range("D5") - Which you will see referenced (commented out) in the code as a TargetRange Variable.

Thank you so much for any help - Also, I have cross posted this in the Excel forum as I'm not sure which one this question belongs to.

Sub RunRepository()Dim Con As Object
Dim Rs As Object
Dim AccessFile As String
Dim TargetRange As Range
Dim i As Integer
Dim Cmd As ADODB.Command
Dim StrQuery As String

Application.ScreenUpdating = False

AccessFile = ("N:\Referral Management (RAOC)\Internal\@DeptCommon\MorningRpt\MorningRpt.accdb")
StrQuery = "QryTest"

On Error Resume Next

Set Con = CreateObject("ADODB.Connection")
If Err.Number <> 0 Then
  MsgBox ("Connection was not created")
  Exit Sub
End If
On Error GoTo 0
With Con
  .Open "Provider = Microsoft.ACE.OLEDB.12.0;Data Source=" & AccessFile
End With

'''''Set TargetRange = Sheets("DataEntry").Range("D5")
'''''With Cmd
'''''  .Parameters = ("Prm")
'''''End With

Set Rs = CreateObject("ADODB.Recordset")
If Err.Number <> 0 Then
  Set Rs = Nothing
  Set Con = Nothing
  
  MsgBox ("Recordset was not created")
  Exit Sub
End If
On Error GoTo 0
Rs.Open StrQuery, Con
 'Check if the recordet is empty.
    If Rs.EOF And Rs.BOF Then
        'Close the recordet and the connection.
        Rs.Close
        Con.Close
        'Release the objects.
        Set Rs = Nothing
        Set Con = Nothing
        'Enable the screen.
        Application.ScreenUpdating = True
        'In case of an empty recordset display an error.
        MsgBox "There are no records in the recordset!", vbCritical, "No Records"
        Exit Sub
    End If
Sheets("Repository").Range("A2").CopyFromRecordset Rs

Rs.Close
Con.Close 

End Sub

 

[Fluff]

Also https://www.mrexcel.com/forum/excel...access-query-using-excel-vba.html#post5059441

Whilst I appreciate that you didn't know which board to post this question. Please do not post the same question multiple times.
Otherwise you could end-up with members trying to help on both threads & potentially wasting their time, if the question has already been answered.