Querying MS Access data from Excel

abhay_547 · #1 (**permalink**) 09-07-09, 13:51

Hi All,

I have some database in MS access which i am trying to query and get the output directly in excel through the below excel macro.

Sub GetMyData()
Const strDb As String = "c:\Mydata.mdb"
Const strQry As String = "SELECT * from [Japan] WHERE ([Japan].[COST_CENTRE_CODE]='0872655010')"

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & strDb & ";"
Set rs = New ADODB.Recordset
With rs
Set .ActiveConnection = cn
.Open strQry
End With
If rs.EOF Then
MsgBox "No Data Found!"
Else
While Not rs.EOF
Sheets.Add
Dim intCol As Integer
For intCol = 0 To rs.Fields.Count
On Error Resume Next
ActiveSheet.Cells(1, intCol + 1) = rs.Fields(intCol).Name
On Error Resume Next
Next intCol
ActiveSheet.Range("A2").CopyFromRecordset rs
'Worksheets("Data").Range("A2").CopyFromRecordset rs
Wend
End If
rs.Close: cn.Close
Set rs = Nothing:
Set cn = Nothing
End Sub

Now above macro works fine but i want to modify it in such a way that in my database i have a datatable named "Japan" and there is field / column called COST_CENTRE_CODE in it and i have a another table in my access database which is named as "Cost Center Mapping" now "cost center mapping" table has field / column "COST_CENTRE_CODE" which is common field in my data table and my cost center mapping table apart from this my cost center mapping also has a field / column which is named as "Product" now i want a macro which will look into the cost center mapping for all those cost center's which are mapped against a particular product and get me those records for eg : In the above code i have entered COST_CENTRE_CODE='0872655010' which gives me all those records which contains above mentioned cost_centre_code in it but instead of this i would enter Product='ABC' and the macro should get all those records of the "COST_CENTRE_CODEs which are mapped against above mentioned product in my mapping table.

Thanks for your help in advance

weejas · #2 (**permalink**) 09-08-09, 04:18

Surely it would be easier to change the SQL statement in strQry?
SELECT a.*
FROM [Japan] AS a
INNER JOIN [Cost Center Mapping] AS b
ON a.[COST_CENTRE_CODE] = b.[COST_CENTRE_CODE]
WHERE b.[Product] = 'ABC';

You could further refine it by using InputBox to prompt for the required product code at run time.

abhay_547 · #3 (**permalink**) 09-10-09, 12:51

Hi,

Thanks a lot for quick reply. The code is working properly but if i want to add and additional criteria to this. For example to give the data only for a particular period. In my data i have a column / field as period which contains numbers from 1 to 12 which are actually months. so if i want to get the data for only a particular month then i how do i add this criteria to the above code and one more thing the code should also look for another field in a different mapping table that is "Cost element mapping" and field which is common in my data table and in mapping table is "Unique Identifier". Now cost element mapping table has a field called "FSI". So i would require the query / code in such way that which will look into cost center mapping for "abc" product data and then it should further filter that data on the basis of "cost element mapping" table and should look for / filter only "xyz" FSI data from the above data.

I tried making changes to the above code but was unsuccessful.

On more thing can i use this code in the background of a userform and if yes, so do i need to define a string for my criteria's For eg : If if am selecting a product "abc" and FSI "xyz" in a combo boxes of userform so can i directly refer to the values of combo box in the above code. If yes, can you please provide me an example.

Thanks a lot once again and it was really very helpful for me.

weejas · #4 (**permalink**) 09-10-09, 15:02

The easiest way to get what you want is to create the query in the Access database and refine there until it produces exactly what you want. Then you can open the query in SQL mode and take the statement that it's produced.

In order to use selected values from a form, the syntax of the query's "WHERE" clause could be modelled on the following:
"WHERE b.Product = '" & [Formname]![ProductComboBox] & "' AND c.FSI = '" & [Formname]![FSIComboBox] & "';"
Remember to include sense-checking code before the SQL is generated, to make sure that the users have selected everything that they're supposed to!

abhay_547 · #5 (**permalink**) 09-10-09, 15:15

Hi,

I am not so good with MS Access queries so i request you to provide me with an example. I think whatever i have written in my last thread is little bit confusing. So this is a brief which will give you an idea what i need. In the sql query code which you provided me in your earlier thread it basically filters my data table on the basis of a cost center mapping table same way i want to add an additional filter to the code provided by you which filter the data further from cost element mapping table on the basis of "unique identifier" column / field which is common in mapping as well as my data table.

Thanks a lot for your quick reponse. I would be very thankful if you can crack this for me.

weejas · #6 (**permalink**) 09-10-09, 16:32

This is starting to move away from my area of working. However, it might be easier to do all the work in Access and create the Excel workbook from there.

Also, it's a little hard to produce an example of a query that would work for you without the database! :P

abhay_547 · #7 (**permalink**) 09-17-09, 22:34

Hi,

I have managed to get below mentioned query code for SQL server 2005 (from my friend) and this code can gives me exact data output which i need, but from sql server 2005. Can you please let me know how can i incorporate this query in my excel macro.

HTML Code:

SELECT *
FROM      [myDatabase].[dbo].[Japan]
WHERE     (UNIQUEIDENTITY IN
                          (SELECT DISTINCT UNIQUEIDENTITY
                            FROM          dbo.COST_ELEMENT_MASTER
                            WHERE      (FSI = 'XYZ'))) AND (COST_CENTRE_CODE IN
                          (SELECT DISTINCT COST_CENTRE_CODE
                            FROM          dbo.COST_CENTER_MASTER
                            WHERE      (PRODUCT = 'ABC')))

and apart from this how can i use the above code to extract data from mutiple tables present in mydatabase at one go. For example currently we are trying to extract data only from Japan table in above code. If i want to extract data for 3 regional tables at one go that is Americas, Japan, Asia. So my friend has suggested to use "Union" for this but can you please provide me an example to use the same as how to use the same in the above code.

Thanks for your help in advance.

weejas · #8 (**permalink**) 09-18-09, 07:04

To answer your questions in reverse, for the UNION clause to work, all the source tables need to have the same field names with the same data type and they need to be in the same order. If your regional tables come from a common design, that ought not to be a problem. If not, you will need to alias the fields from each source table, which takes a little longer.

Code:

SELECT Japan.[Field1] AS ThisField, Japan.[Field2] AS ThatField...
SELECT Asia.FirstField AS ThisField, Asia.SecondField AS ThatField

Union queries are quite simple to write - the easiest way would be to copy the statement as it's been given to you, then underneath it type "UNION" and underneath that, paste the statement again. Then you just change the names of the source table to the next one. Repeat this until you've included all the required source tables. If you need some way to identify the source table for each record, you can add a field to the SELECT statements:

Code:

..., 'JAPAN' AS SourceTable
..., 'ASIA' AS SourceTable

However, this isn't always necessary.

How you'll use this depends on whether you're getting the data from Access or SQL. If it's Access, you just need to populate a variable with the SQL statement and use it instead of strQry in your original code. I have no experience of extracting data from SQL Server into Excel, though. Also, I'm not sure if nested subqueries will work in Access - you'll have to check to the Help documentation for that.

abhay_547 · #9 (**permalink**) 09-18-09, 16:49

Hi,

All my data tables are in same layout that is column order and field name are exactly same and i have all my data in ms access. Just to give you example i had asked my friend to write a query in sql server 2005 which exactly meets my requirement. I am little bit confused with your last post. Can you please provide me with a sample code for my excel macro. Let me once again brief you the whole thing.

For example :
Following are the things which i have in my data :
a) I have 2 data tables in my ms access file. Following are the names of the same:
1) Japan
2) Asia

b) i have two more tables in the same ms access file which are as follows :
1) Cost Center Mapping or COST_CENTER_MASTER
2) Cost Element Mapping or COST_ELEMENT_MASTER

c) All 4 of the above tables have following fields common in them.
1) COST_CENTRE_CODE : which is common in Cost Center Mapping and my data tables that is both Japan and Asia
2) UNIQUEIDENTITY : which is common in Cost Element Mapping and my data tables that is both Japan and Asia

Now what i am looking for and what should be the output of my macro. Following is the code which was been provided by you in 08-09-09 post.

SELECT a.*
FROM [Japan] AS a
INNER JOIN [Cost Center Mapping] AS b
ON a.[COST_CENTRE_CODE] = b.[COST_CENTRE_CODE]
WHERE b.[Product] = 'ABC';

As far as the above code is concerned it's working fine. I just want to add an additional criteria to the same. In the above code you have filtered the data from Japan table for Product "ABC" on the basis of the common field "COST_CENTRE_CODE" present in both Cost Center Mapping and Japan table. Now i want you to add an additional criteria to the above code that is it should also look for FSI "XYZ" in the same data table on the basis of "UNIQUEINDENTITY" field / column which is common in both Japan table and Cost Element Mapping.

Apart from this i want to get the same output from multiple table as of now if you see we have 2 tables that is Japan and Asia. So Please provide me a separate example which will use Union function and give the consolidated output for the both the tables. Now as both the table structures are exactly same, Please provide me a example in which i don't have to repeat Product and FSI looking criteria again and again for each table. I want something like below :

HTML Code:

SELECT * FROM (
        SELECT * FROM Japan
        UNION ALL
        SELECT * FROM Asia        
) subQry
WHERE Product = 'ABC' and FSI = 'XYZ' 
Note : Look for the Product "XYZ" data on the basis of "COST_CENTRE_CODE" field which is common in datatables and Cost Center Mapping table and further filter / look for the FSI "XYZ" data on the basis of "UNIQUEIDENTITY" field which is common in datatables and Cost Element Mapping" in the same datatables

Thanks a lot for your help in advance.

weejas · #10 (**permalink**) 09-23-09, 05:07

Given that the UNION keyword is generally used to join two or more SELECT queries together, there's not a lot of getting away from repeating the WHERE clause for each one. That's the way that I've always written them, anyway.

Alternatively, you can create a Union query in the Access database that melds as many tables as required, and then use that query as part of your SQL statement.
uqryAllData=

Code:

SELECT Japan.*
FROM Japan
UNION
SELECT Asia.*
FROM Asia;

Then your SQL statement would be something like

Code:

SELECT uqryAllData.*
FROM (uqryAllData INNER JOIN [Cost Center Mapping]
ON uqryAllData.COST_CENTRE_CODE = [Cost Center Mapping].COST_CENTRE_CODE)
INNER JOIN [Cost Element Mapping]
ON uqryAllData.UNIQUEIDENTITY = [Cost Element Mapping].UNIQUEIDENTITY
WHERE [Cost Center Mapping].[Product] = 'ABC'
AND [Cost Element Mapping].FSI = "XYZ";

Internet Services

Web Development

Digital Marketing

Consumer Tech