Excel query to get data for an Access Query

kavomix

New Member
Joined
Sep 13, 2006
Messages
4
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.
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

maytas

New Member
Joined
Sep 5, 2006
Messages
18
Hi,

Code:
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

New Member
Joined
Sep 13, 2006
Messages
4
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

New Member
Joined
Sep 13, 2006
Messages
4
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

New Member
Joined
Sep 5, 2006
Messages
18
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

New Member
Joined
Sep 13, 2006
Messages
4
Thank you very much, It does work now after you pin-pointed that.

Cheers mate
 

Forum statistics

Threads
1,136,595
Messages
5,676,707
Members
419,644
Latest member
KeelsM

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top