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]

hj,

What version of Excel?

Your QueryTable example is valid for Excel 97-2003. Excel 2007+ is different!

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

 

[sayantan1286]

Hi Skip,

i am using excel 97-2003 format only. My excel file is saved as a .xls file.

One more thing : Previously I used sql.request for this project and the whole automation was running fine. The hitch is that sql.request is not supported in Excel 2010. So I am basically looking for an alternative.

Thanks,
Sayantan

 

[SkipVought]

Why are you using "excel 97-2003 format only" while your version is 2010?

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

 

[sayantan1286]

Yes..I was expecting this question. Actually I wanted to test run it in the compatible format. My assumption is that if it runs in the compatible format, then it will run fine in any environment. I might me wrong here...

Anyways, what do you suggest I should do in this case? I need this thing for excel 2007+ only..

Thanks again for your help,

Sayantan

 

[SkipVought]

If you run in compatability format, it assumes that Excel versio 97-2003+ will be used, thereby making 2007+ features inoperable in that workbook.

Is that your intention?

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

 

[sayantan1286]

No, that is not my intention. I want my automation to run in Excel 2010. Could you share some material(like a link to a website) that I can refer to solve my problem? Also it would be great if you could give me a heads start to solve this problem.

One thing : I think the work I did so far is of no use because the automation will not be running in excl 2010. Anyways, I learnt at least something regarding this feature of VBA. I look forward to your reply.

Thanks,
Sayantan

 

[dhookom]

sayantan,

Can you delete the query first with code like below? I changed the file and query to meet my requirements.

Sub test()
    Dim a As String
    Dim b As String
    If Not IsEmpty(ActiveSheet.Range("E15")) Then
        Range("E15").Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.QueryTable.Delete
        Selection.ClearContents
    End If

    a = "ODBC;DBQ=C:\Temp\Access\Northwind.mdb;Driver={Microsoft Access Driver (*.mdb)}"
    b = "select Orderdate from Orders where EmployeeID=1"
    With ActiveSheet.QueryTables.Add(Connection:=a, Destination:=Range("E15"))
        .CommandText = b
        .Refresh BackgroundQuery:=False
    End With
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[sayantan1286]

Duane,

Yeah..I actually tried similar kind of manipulation but not with much luck. I want to create the query table only once and then refresh it to get the new results (depending on the SQL Query). Also the code should run in excel 2010.

Thanks for your help!!

Sayantan

 

[dhookom]

The version I tested in was 2010.

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

hi,

Each time that you run the macro you posted a NEW and SEPARATE QueryTable is ADDed to your sheet. Unless you have deleted any previous QueryTable, the QueryTable you intend to refresh may not be ActiveSheet.QueryTables([highlight]1[/highlight]).

As a matter of practice, I add my QueryTable manually, since I an most likely designing a new query, edit it as necessary, and THEN programatically refresh, via code for ongoing extracting. Most often, ONE QT PER SHEET.

dim qt as querytable

for each qt in ActiveSheet.querytables
  qt.delete
next
'now add your new qt

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

 

[SkipVought]

Oh, and another caveat. Since your version of Excel is 2010, you MUST add the QueryTable via code, since the native method, via Data > Get External Data > From Other Sources > From Microsoft Query... adds a ListObject and then a QueryTable as

ActiveSheet.ListObjects(1).QueryTable

and, of course, the ListObject is not in the Excel 97-2003 Object Model, which is why you must add the QT to the Sheet Object via code.

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

 

[sayantan1286]

Skip,

Thanks..This works fine. Really learnt a new thing!!

Sorry for this but one more thing : I have prepared the report in the compatible format (.mdb and .xls). Now I need to make the same thing in 2010 format (.accdb and .xlsm). I am sure the overall logic will be the same but there will be some changes in the syntax.

I know you have given some insights in this issue but unfortunately, I am not able to make out much from that. If you could tell me the modifications that are required in the code (that I had posted in the first mail of this thread), then it would be really helpful.

Thanks again for this. i really appreciate it.

Sayantan

 

[dhookom]

Sayantan,

This works for me. Notice, I added some of what I feel are best practices in coding by using descriptive memory variable names, pulling some data from the worksheet, and moving any hard-coded values to the top of the code so they can be easily changed WHEN needed.

Sub test()
    Dim strConnect As String
    Dim strSQL As String
    Dim qt As QueryTable
    Dim lngEmpID As Long
    Dim strOutputRange As String
    Dim strFilename As String
    
    
    lngEmpID = Range("B2")
    strOutputRange = "E15"
    strFilename = "C:\Temp\Access\TestSorting.accdb"
    
    For Each qt In ActiveSheet.QueryTables
      qt.Delete
    Next
    If Not IsEmpty(ActiveSheet.Range(strOutputRange)) Then
        Range(strOutputRange).Select
        Range(Selection, Selection.End(xlDown)).Select
        Selection.ClearContents
    End If
    strConnect = "ODBC;DBQ=" & strFilename & ";Driver={Microsoft Access Driver (*.mdb, *.accdb)}"
    strSQL = "select Orderdate from Orders where EmployeeID=" & lngEmpID
    With ActiveSheet.QueryTables.Add(Connection:=strConnect, Destination:=Range(strOutputRange))
        .CommandText = strSQL
        .Refresh BackgroundQuery:=False
    End With
End Sub

Duane
Hook'D on Access
MS Access MVP

 

[sayantan1286]

Duane,

Thanks so much for this. I do understand the "good coding practices" that you showed. But I had tried similar code (before I saw your post) in the 2007 environment. I was getting an error-I don't know why. when I tried to debug, I saw the "yellow" line in the ".refresh backgroundquery :=false".

Are you sure that the above code will run in the 2007 environment?

I am looking forward to your reply.

Thanks,
Sayantan

 

[dhookom]

I don't know if it will run in 2007 since you specifically asked "Now I need to make the same thing in 2010 format (.accdb and .xlsm)."

Duane
Hook'D on Access
MS Access MVP

 

[SkipVought]

the Refresh error most often occurs when the syntax is in error. Is your EmployeeID a numeric field?

Most often, I'll MANUALLY edit the query so I KNOW that it executes with the results I expect, and THEN I'll 1) extract that code from the QueryTable and 2) paste it into my browser to make it part of my refresh code.

Assuming that thiese is ONLY ONE QueryTable on the sheet

With ActiveSheet.Querytables(1)
   Debug.Print .Connection
   Debug.Print .CommandText
End With

So now in the Immediate Window, I have the exact string that represents the CONNECTION and the SQL, and I assign them thus:

    Dim sConn As String, sSQL As String, sPath As String, sDB As String
    
    sConn = sConn & "ODBC;DSN=MS Access Database;"
    sConn = sConn & "DBQ=C:\Users\ii36250\Documents\Database1.accdb;"
    sConn = sConn & "DefaultDir=C:\Users\ii36250\Documents;"
    sConn = sConn & "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

    sSQL = sSQL & "SELECT CAP_T_OPER_BASIC.PARTNO, CAP_T_OPER_BASIC.TPLN, CAP_T_OPER_BASIC.OPERNUMB, CAP_T_OPER_BASIC.MACHGRP, CAP_T_OPER_BASIC.DEPT"
    sSQL = sSQL & "FROM `C:\Users\ii36250\Documents\Database1.accdb`.CAP_T_OPER_BASIC CAP_T_OPER_BASIC"
    sSQL = sSQL & "WHERE (CAP_T_OPER_BASIC.PARTNO='000-000-000-101 ')"
    
    With ActiveSheet.QueryTables(1)
        .Connection = sConn
        .CommandText = sSQL
    End With

Then clean it up a bit...

    Dim sConn As String, sSQL As String, sPath As String, sDB As String
    
    sPath = "C:\Users\ii36250\Documents"
    sDB = "Database1.accdb"
    
    sConn = sConn & "ODBC;DSN=MS Access Database;"
    sConn = sConn & "DBQ=" & sPath & "\" & sDB & ";"
    sConn = sConn & "DefaultDir=" & sPath & ";"
    sConn = sConn & "DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"

    sSQL = sSQL & "SELECT ob.PARTNO, ob.TPLN, ob.OPERNUMB, ob.MACHGRP, ob.DEPT"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "FROM `" & sPath & "\" & sDB & "`.CAP_T_OPER_BASIC ob"
    sSQL = sSQL & vbLf
    sSQL = sSQL & "WHERE (ob.PARTNO='000-000-000-101 ')"
    
    With ActiveSheet.QueryTables(1)
        .Connection = sConn
        .CommandText = sSQL
        .Refresh False
    End With

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

 

[sayantan1286]

Guys,

Thanks....My report is running now in 2010 environment.

I need to discuss one more thing with you guys. Hopefully if this is solved then I can go ahead and prepare my whole report.

The issue I am now facing is with the indices of the querytables. I know you had suggested that I delete all the querytables first and subsequently the querytable that will be created will have an index "1". But my report will have many querytables (both in a worksheet and across worksheets). So it is very important for me to assign a name to a querytable. This will help me in refreshing them later. The thing that I have tried is:-


Dim qt As QueryTable
Dim qt1 As QueryTable

Set qt = ActiveSheet.QueryTables.Add(Connection:=strConnect, Destination:=Range(strOutputRange))

With qt
.CommandText = strSQL
.Refresh BackgroundQuery:=False
End With

Set qt1 = ActiveSheet.QueryTables.Add(Connection:=strConnect, Destination:=Range(strOutputRange1))

With qt1
.CommandText = strSQL1
.Refresh BackgroundQuery:=False
End With


In this way, I am trying to create two querytables in the same worksheet. Now when I need to refresh them, I am using the following code in the editor for sheet1:-

Private Sub worksheet_calculate()
Sheet1.QueryTables("qt").CommandText = "select values from data1 where ID=" & Range("B1") & "and na='" & Range("E1") & "'"
Sheet1.QueryTables("qt").Refresh

Sheet1.QueryTables("qt1").CommandText = "select values from data2 where ID=" & Range("B1") & "and na='" & Range("E1") & "'"
Sheet1.QueryTables("qt1").Refresh
end sub

While running this, I am getting an error "subscript out of range" and the yellow line is in the first line of the above code.

PLEASE NOTE : The report is running fine when I use the indices of the querytable. Problem arises when I name the querytable.

Really looking forward for a reply from your end.

Thanks,
Sayantan

 

[SkipVought]

Then do assign a name...

with  ActiveSheet.QueryTables.Add(Connection:=strConnect, Destination:=Range(strOutputRange))
   .Name = "qMyFirst"
   .CommandText = sSQL1
   .Refresh False
End With

This gets executed ONE TIME.

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

 

[sayantan1286]

Skip,

When I try to refresh the querytable using :-

ActiveSheet.QueryTables("qMyFirst").CommandText = "select values from data1 where ID=" & Range("B1") & "and na='" & Range("E1") & "'"
ActiveSheet.QueryTables("qMyFirst").Refresh

I am geting "Subscript out of range" error. My main aim is to refresh the querytable using its name.

Looking forward to your reply.

Thanks,
Sayantan