Integration of MS Access with Excel

[sayantan1286]

Hi,

I am relatively new to VBA coding. I want to extract data (by subsetting) from an access file and get the results in excel. After doing some research I have seen that using "querytables" in Excel VBA can do the trick. I have the following code :-

Sub test()
Dim a As String
Dim b As String
a = "ODBC;DBQ=C:\codes\Database1.mdb;Driver={Microsoft Access Driver (*.mdb)}"
b = "select values from table1 where ID=1"
With ActiveSheet.QueryTables.Add(Connection:=a, Destination:=Range("E15"))
.CommandText = b
.Refresh BackgroundQuery:=False
End With
End Sub

The table in Access has two columns: values and ID. For simplicity lets assume that ID can take two distinct values : 1 and 2.
When I run the above code for the first time, it works fine. The set of "values" for ID=1 are extracted in excel starting from E15. But the problem arises when I change the the value of ID to 2 and run the query. The original column gets shifted to F, which I do not want. I want the query to overwrite the results in column E.

I know there is a thing called "ActiveSheet.QueryTables(1).Refresh" but I am not able to use it properly.

I am trying to solve this issue for quite sometime now but with no luck. I must admit here that I am not well versed with all the functionalities of excel VBA since I am in learning phase.

Any help will be highly appreciated.

Thanks,
Sayantan

 

[SkipVought]

So why two queries using the exact same SQL code, including the same parameter values for your 2 criteria? You must have a good reason for that duplication.


The a few general comments regarding coding queries and VBA. Here's a typical example of something I might do.

Notice each select element line is self contained: I can ADD or DELETE in one fell swoop.
The FROM, WHERE, ORDER BY, is preceeded by vbLf on purpose to BREAK the string and it helps visually.
Where practical, line up your alias names.
I assign the sheet object CODE NAME, using a convention as [highlight]ws[/highlight]Nnnnn. This prevents users from messing up your code by changing a sheet name.
Debug.Print sSQL displays your SQL in the Immediate Window, which is helpful if you need to debug what's happening.

    Dim sSQL As String

    sSQL = sSQL & "SELECT"                          'MAIN oper extract
    sSQL = sSQL & "  oi.MATERIAL"
    sSQL = sSQL & ", substr(oh.ORDER_NO,length(oh.ORDER_NO)-9,10)||'-'||op.sequence_aplfl SAP_Ord"
    sSQL = sSQL & ", oh.MRP_CONTROLLER       RC"
    sSQL = sSQL & ", op.OPERATION            OP"
    sSQL = sSQL & ", op.CNTL_KEY             CK"
    sSQL = sSQL & ", wc.WORK_CNTR            WC"
    sSQL = sSQL & ", op.OPR_SHORT_TEXT       OP_TEXT"
    sSQL = sSQL & ", ot.OP_QTY               Opr_Qty"
    sSQL = sSQL & ", ot.CONFIRM_YIELD        Cmp_Qty"
    sSQL = sSQL & ", ot.CONFIRM_SCRAP        Rej_Qty"
    
    sSQL = sSQL & ", ot.STANDARD_VALUE_1     setup"
    sSQL = sSQL & ", ot.STANDARD_VALUE_3     run"
    
    sSQL = sSQL & ", ot.LATEST_START_DT_exec PLN_ST"
    sSQL = sSQL & ", ot.LATEST_END_DT_exec   PLN_EN"

    sSQL = sSQL & ", ot.ACT_OP_START_DT      ACT_ST"
    sSQL = sSQL & ", ot.ACT_OP_END_DT        ACT_EN"
    
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM"
    sSQL = sSQL & "  SIX_SIGMA.VS_AFKO_ORDER_HEADER     oh"
    sSQL = sSQL & ", SIX_SIGMA.VS_AFVC_ORDER_OPERATIONS op"
    sSQL = sSQL & ", SIX_SIGMA.VS_CRHD_WORK_CENTER      wc"
    sSQL = sSQL & ", SIX_SIGMA.VS_AFVV_ORDER_TASKS      ot"
    sSQL = sSQL & ", SIX_SIGMA.VS_AFPO_ORDER_ITEM       oi"
    sSQL = sSQL & ", Six_Sigma.SAP_Material_Plant_List  mp"
    sSQL = sSQL & ", Six_Sigma.Vs_Aufk_Order_Master     om"

    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE op.PLAN_NUM            = oh.PLAN_NO"
    sSQL = sSQL & "  AND op.PLAN_NUM            = ot.PLAN_NUM"
    sSQL = sSQL & "  AND wc.RESOURCE_OBJ_ID     = op.RESOURCE_OBJ_ID"
    sSQL = sSQL & "  AND op.GEN_COUNTER_APLZL   = ot.GEN_COUNTER_APLZL"
    sSQL = sSQL & "  and om.Client              = oh.Client"
    sSQL = sSQL & "  and om.Client              = oi.Client"
    sSQL = sSQL & "  AND oi.ORDER_NO            = oh.ORDER_NO"
    sSQL = sSQL & "  and oi.Material            = mp.material"
    sSQL = sSQL & "  and oi.Pln_Plt             = mp.plant"
    sSQL = sSQL & "  and om.client         = '400'"
    sSQL = sSQL & "  and om.del_flag       is null"
    sSQL = sSQL & "  and oi.material       is not null"
    sSQL = sSQL & "  and oi.Item_Qty       > oi.GR_Qty + oh.Confirm_Scrap"
    sSQL = sSQL & "  AND om.TechCompletion = '00000000'"
    
    sSQL = sSQL & "  AND oh.actual_rel_dt  >'00000000'"               'releaded orders

'use this for testing
'    sSQL = sSQL & "  and substr(oh.ORDER_NO,length(oh.ORDER_NO)-9,10)||'-'||op.sequence_aplfl in ('1000149755-000000','CAMS023400-000000','1000001188-000000')"

    sSQL = sSQL & vbLf
    sSQL = sSQL & "order by"
    sSQL = sSQL & "  oh.MATERIAL"
    sSQL = sSQL & ", oh.ORDER_NO"
    sSQL = sSQL & ", op.sequence_aplfl"
    sSQL = sSQL & ", op.OPERATION"
    
    Debug.Print sSQL
    
    With [b][highlight]ws[/highlight]OPS[/b].ListObjects(1).QueryTable
        .Connection = "ODBC;DSN=A060PROD;;DBQ=A060PROD;"
        .CommandText = sSQL
        .Refresh False
    End With

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sayantan1286]

Skip,

The reason for my duplication:-

Note that the parameter values are same in the two SQL Queries but the Table from which I am extracting are different. one is "data1" and the other is "data2". I know for this also one querytable is sufficient. BUT the main reason why I need two different querytables is : I need the data to be extracted in two different locations in the worksheet.

If I use one Querytable then (the destination range being the same) the result from the second query will replace that of the first. Have I made myself clear?

To be specific : My report will have 5 worksheets and in each worksheet, I need to have four querytables (because I will have to extract the data at 4 different locations). So in all, I need 20 querytables. If I can refresh these tables with the help of its name (instead the indices), my work should be done.

Is the reason for my duplication strong enough or is there some alternative that I am missing?


Thanks again for your time,
Sayantan

 

[SkipVought]

No problem. It is not duplication, as you have explained.

Yes, go ahead and name each QT. This can be done via code, as I posted previously. (I hardly ever use code to name QTs!) But since this is a ONE TIME EVENT, it can also quite easily be done on the sheet, as I almost always do to name QTs...
[tt]
1. right-click in any QT
2. select Data range Properties...
3. in the External Data Range Properties window, enter a properly formated RANGE NAME of your choice. FYI, I might use names like qEmployeess, which will group all query range names in the Name Box, together.
[/tt]


Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sayantan1286]

Skip,

I am sorry for this but how do I "right click" on a QT. After assigning the name (say "q1") via code, I could find it in the name manager box (the name was changes to "q1_" though). But when I right clicked on this, nothing happened.

All I need to do is refresh this newly created QT ("q1") using a code like :-

activesheet.querytables("q1").refresh

but I am getting "subscript out of range" error in this code. I even tried "q1_" but same error.

Thanks again for your time,
Sayantan

 

[SkipVought]

As I stated, the QT name is a RANGE name. Q1 or q1 is ALREADY a range: column Q, row 1!

I'd stay away from those kind of names, ie xxxnn that look ANYHTHING like a cell address. That is why your q1 was actually renamed q1_!

But why use such a cryptic name? Why not something like qEmp or qInv. If you want numbers for the sake of programmability, then qSeq1, qSeq2, qSeq3 might work as none of those represent cell addresses. I'd only do this if the refresh were to be executed in a loop.

Lastly, if you look in the Formulas > Defined Names > Name Manager, you will see your query tanbe range names. Take notice of the Scope of each name. This is another way that Excel 97-2003 QTs are different than Excel 2007+ ListObject/QTs as the former Scopes are to a particular sheet, while the latter scopes are Workbook.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sayantan1286]

Skip,

Yes I now understand your point. I have done accordingly and my report is working fine. Just wanted to let you know where I had gone wrong.

I know you had clearly stated in one of your previous posts that the code to CREATE the Query table should be run ONLY ONCE. I misunderstood this : I thought that the QT should be created every time I open the worksheet but the code (to create it) should not be run more than once once I have opened the worksheet.

Anyways, As I slowly get to know the working of QTs (thanks to you), I now have to deal with another issue, which is kind of interesting. I have not yet got the time to work on it (though I have identified the issue). Just wanted to share the same with you:-

Look, as I told you before my objective is to make an automated report. Now the report uses QTs. I will be able to create the report now and it will work fine on my system. But what if I ask someone else to run that report (in his system) without asking him to create the QTs again?

The user of my report should not be bothered about creating the QTs. Yes one solution : I make the VBA code, to run the QTs, (in the user's system) to run automatically the first time he opens the worksheet. Here also, I should take care that when he opens the worksheet for the second time, the code to run the QTs is not executed.

Am I able to explain the issue I need to work on? Let me know your thoughts.

Thanks for your continued support in this. I really appreciate it.

Thanks,
Sayantan

 

[SkipVought]

Not sure I'm understanding you.

I consider "the system" to be the workbook, containing all the ONE TIME CREATED QUERYTABLES. So my users NEVER EVER need to add a QT!!!!! I added each and every one of them. EVERY time I open this workbook, the QTs are there, with their respective connections & SQL. They are Objects on the sheet with certain property VALUES, just like othe Excel objects like ranges, that maintain their property values thru save, close, open.

So I'm reasonably sure that we are not on the same page. I forensically believe that you think that the QT property values do not persist in the workbook when opened subsequent to save, close.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sayantan1286]

OK Skip, Let me give you an instance of the issue:-

Suppose I have created the report..and it is working fine in my system. The report is actually an excel spreadsheet(.xlsx). this rport uses an access database (database1.accdb).

Now suppose you are the user. I send you these two files- the report(.xlsx and the .accdb file). You save these files to your local drive and open the .xlsx file to run the report. Will you be able to run the report in your system, without creating the QTs??

Here by "system", i mean another computer. Would the QTs (that I have made in my computer via code using a specific location of the access database) be applicable in another computer (where the access database is stored in some other location different from mine)??

Have I made myself clear?

Thanks,
sayantan

 

[SkipVought]

The environment I am referring to is one where the database, in your case Access, is globally accessible by any and all users.

In the event that the database(s) move, which seems that it might be your case, then some VBA must run to reassign the connection property value of the EXISTING QT OBJECT only.

This would also be the case when you query a sheet (table) within your workbook that is saved in some other path.

Also, your workbook cannot be a .xlsx. Must be .xlsm or .xlsb.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sayantan1286]

Yes, The access database will not globally placed. the path will depend on the location in which the user saves it. Getting the path to be automated is not a big problem. I can use "thisworkbook.path" to get the path in the user's system (computer).

But the catch is : The user should run the code to create the QT ONLY ONCE (that is the first time he opens the report). He closes the report and opens it second time-then the code to run the QT SHOULD NOT RUN.

It would have been much easier if the user manually created the QT or run the code only once. But as I said earlier, the user should not be bothered about it.

I think I have identified the issue correctly. I will do some more research and let you know my findings.

Please let me know if (whatever I am writing) is making sense to you?

Right...my mistake--the workbook is .xlsm.

Thanks for your time,
Sayantan

 

[SkipVought]

There is ONE database. All the user need do is identify the path to the new db.

You can use the Application.GetOpenFileName() function.

Then simply assign in the connection string.

But if you want to delete & recreate QTs, have at it.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[SkipVought]

Just a word about approach or philosophy of design.

I tend to be a minimalist in my designs. I'd rather have one QueryTable on a sheet that does multiple things. Others would rather have multiple QTs. It is a matter of style and approach. There are pros and cons, the arguments for which might wax esoteric in nature.

If your approach is sound and accomplishes the task at hand without being a Rube Goldberg monstrosity, it is perfectly justified.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[sayantan1286]

I also concur with you in being a minimalist in designs. But as I told you earlier, the results for different SQL queries should be placed (in other words the destination range) at different locations in the worksheet. That is why I am thinking of multiple QTs, each with a destination range of my choice.

Now it may be the case that the destination of a particular QT can be changed (Just like the way a particular QT can use different SQL queries using the command text statement). But I have no idea how to do this.

I still didn't get a chance to work on the issues I discussed in my earlier posts. I will work on that (based on your suggestions) and get back to you.

Thank you very much,
Sayantan