Getting access data using parameters in Excel

[balaji3081]

I love this, this is my 3rd thread in 24hrs....

I have a template which I have attached, this has macros to pull data from a particular query and makes a file for each country based on the Base file,

thanks to the members here I was able to reach till here

now in addition to what i already have , I want to pull data from a new query into a new tab using the same parameters on column B (e.g UK,SA,KR)

Any help with VBA for this.


Thanks,
Bala

 

[balaji3081]

If any help here is the SQL for the new access query

SELECT RDW_PROJECTS.PROJECT, RDW_PROJECTS.PROJ_GOC_OFF AS [PROJECT GOC OFFICE], RDW_PROJECTS.PROJECT_NAME, RDW_PROJECTS.ED_NAME, RDW_PROJECTS.SOFT_OPEN_DATE, RDW_PROJECTS.SOFT_CLOSE_DATE, RDW_LOCATION_HIERARCHY.L6_PARENT_NAME
FROM RDW_PROJECTS INNER JOIN RDW_LOCATION_HIERARCHY ON RDW_PROJECTS.PROJ_GOC_OFF = RDW_LOCATION_HIERARCHY.LOCATION
WHERE (((RDW_PROJECTS.ACTIVITY)="PE") AND ((RDW_LOCATION_HIERARCHY.HIERARCHY)="CO"))
GROUP BY RDW_PROJECTS.PROJECT, RDW_PROJECTS.PROJ_GOC_OFF, RDW_PROJECTS.PROJECT_NAME, RDW_PROJECTS.ED_NAME, RDW_PROJECTS.SOFT_OPEN_DATE, RDW_PROJECTS.SOFT_CLOSE_DATE, RDW_LOCATION_HIERARCHY.L6_PARENT_NAME, RDW_PROJECTS.CLOSE_DATE, RDW_LOCATION_HIERARCHY.L6_PARENT_LOCATION
HAVING (((RDW_PROJECTS.CLOSE_DATE) Is Null) AND ((RDW_LOCATION_HIERARCHY.L6_PARENT_LOCATION)=[Enter L6]))
ORDER BY RDW_PROJECTS.SOFT_CLOSE_DATE;

 

[SirJB7]

Hi, balaji3081!
In SQL SELECT statements HAVING clause is used to specify conditions of aggregate functions (SUM, COUNT, ...) and WHERE clause is used to filter the records to be considered. So you should move the 2 conditions of the HAVING clause to the WHERE clause.
More info:
https://office.microsoft.com/en-us/access-help/introduction-to-access-sql-HA010341468.aspx
Regards!

 

[balaji3081]

This is what I use for another query, where the data is pulled in in excel, though the query does not have any parameters in excel, its all in the query.....

Trying to use this .......please let me know your views....

Sub Get_data()
Dim db_path As String
Dim oAccess As Object
Dim rst As DAO.Recordset
Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Application.DisplayAlerts = False
db_path = Sheets("Lookup").Range("B4").Value
Set oAccess = CreateObject("Access.database")
oAccess.Application.OpenCurrentDatabase (db_path)
oAccess.Application.Visible = False
 
Sheets("Actuals- 1").Activate
Sheets("Actuals- 1").Range("A2:X500000").Select
Selection.ClearContents
 
Set db = oAccess.CurrentDb()
Set qdf = db.QueryDefs("Firm Function - Actuals - 1")
Set rst = qdf.OpenRecordset
Sheets("Actuals- 1").Range("A2").CopyFromRecordset rst
Set rst = Nothing
 
End Sub

 

[balaji3081]

I have now changes the SQL query to have all group by -
Is it possible to build the VBA around it the file I attached above....

Regards,
Bala

SELECT RDW_PROJECTS.PROJECT, RDW_PROJECTS.PROJ_GOC_OFF AS [PROJECT GOC OFFICE], RDW_PROJECTS.PROJECT_NAME, RDW_PROJECTS.ED_NAME, RDW_PROJECTS.SOFT_OPEN_DATE, RDW_PROJECTS.SOFT_CLOSE_DATE, RDW_LOCATION_HIERARCHY.L6_PARENT_NAME, RDW_PROJECTS.CLOSE_DATE, RDW_LOCATION_HIERARCHY.L6_PARENT_LOCATION, RDW_PROJECTS.ACTIVITY, RDW_LOCATION_HIERARCHY.HIERARCHY
FROM RDW_PROJECTS INNER JOIN RDW_LOCATION_HIERARCHY ON RDW_PROJECTS.PROJ_GOC_OFF = RDW_LOCATION_HIERARCHY.LOCATION
GROUP BY RDW_PROJECTS.PROJECT, RDW_PROJECTS.PROJ_GOC_OFF, RDW_PROJECTS.PROJECT_NAME, RDW_PROJECTS.ED_NAME, RDW_PROJECTS.SOFT_OPEN_DATE, RDW_PROJECTS.SOFT_CLOSE_DATE, RDW_LOCATION_HIERARCHY.L6_PARENT_NAME, RDW_PROJECTS.CLOSE_DATE, RDW_LOCATION_HIERARCHY.L6_PARENT_LOCATION, RDW_PROJECTS.ACTIVITY, RDW_LOCATION_HIERARCHY.HIERARCHY
ORDER BY RDW_PROJECTS.SOFT_CLOSE_DATE;

 

[SirJB7]

Hi, balaji3081!
Firstly I only focused on the SQL statement, I didn't analyze the code. I'm a bit lazier than usual, so would you please elaborate and explain me in other words what do you want to do, where are you going to use the SQL select statement, and so? Thank you.
Regards!
PS: It's Friday night almost, a long week, ... help us help you