Excel query to get data for an Access Query

[kavomix]

Hey all, first post here. I've browsed this site numerous times and now I'm utterly stuck on this issue and don't know what to do, so any help is appreciated.

I currently have an excel file that pulls some raw data from an access file. I'm trying to get some code or find out how I can have the Excel file query an Access query using a start date and an end date.

For Example:
The excel file has a start date in cell A1, and an end date in A2. When a button is clicked a macro runs that connects to an Access file. It then goes to an access query called pallets, where it collects all data starting from, obviously the start date that is in cell A1 up to the date in A2, and puts all this data into the excel sheet on cell C2.

Currently the way im doing this is by creating a new macro for each month that connects to a query in Access which filters between dates. This means each month I have to create a new query and macro causing the excel file to grow ever bigger.

Please can anyone give me hints, tips, code....anything that can help me with this. Many thanks in advance.

 

[maytas]

Hi,

Sub RunQueryInAccessFromExcel()
    'Required Microsoft ActiveX Data Objects 2.x Library
    Dim Con As New ADODB.Connection
    Dim RS As New ADODB.Recordset
    Set Con = New ADODB.Connection
    
    Con.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & ThisWorkbook.Path & "\Database.mdb;"
    Set RS = New ADODB.Recordset
    RS.Open "SELECT * FROM [YourQueryName] Where Date_Field  between " & CDbl(CDate(Range("A1"))) & " and " & CDbl(CDate(Range("A2"))) & "", Con, adOpenDynamic, adLockOptimistic
    Range("A4").CopyFromRecordset RS
    
    RS.Close
    Con.Close
    Set RS = Nothing
    Set Con = Nothing
End Sub

 

[kavomix]

Thanks for repsonding however, firstly it looks quite different from the normal query, i just thought a little extra would need to be added for date.

However I have put it into Excel and attempted to run it however get this highlighted as an error

Con As New ADODB.Connection

 

[kavomix]

I thought the sort of code im looking for would be along these lines, however the code needs to be adjusted so that it only collects dates from C2 until C3



Sub Sales_Query()
Dim salesrep As Variant
Dim salesdate As Date

salesrep = Range("C2").Text
salesdate = Range("C3").Value
With ActiveSheet.QueryTables.Add(Connection:=Array( _
"ODBC;DSN=Northwind;Description=Northwind;APP=Microsoft Office XP;DATABASE=Northwind;Trusted_Connection=YES"), Destination:=Range("B5"))
.CommandText = Array("SELECT * FROM Orders WHERE EmployeeID=" & salesrep & " AND OrderDate > '" & salesdate & "' ORDER BY OrderDate")
.Name = "Sales Query from Northwind"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = True
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub

 

[maytas]

However I have put it into Excel and attempted to run it however get this highlighted as an error

Con As New ADODB.Connection

Press Alt+F11 and go to VBE window.
Select Tools=>References =>ActiveX Data Objects 2.x Library
Now makro will be run without error.

 

[kavomix]

Thank you very much, It does work now after you pin-pointed that.

Cheers mate