asked on

VBA: From Excel, export worksheet to .mdb and run sql and retrieve data back to Excel

Hi All,

i'm looking for a way to run an SQL statement on a .mdb in Excel's VBA, right after I export the Excel worksheet to the .mdb. I would like to be able to retrive the SQL results into the same workbook but for another worksheet. I found the below code online which exports the data flawlessly to a table(the weakness of it is the database has to exist).

I'm really just having trouble with Google searches bc I don't know how to phrase the term I'm trying to seek. If anyone can assist, thanks!

also is it true that DAO is faster than ADO for .mdb? I read that online somewhere but I can't verify it.

Sub DAOFromExcelToAccess()

Dim strTableName As String
strTableName = "tblSurvey"

' exports data from the active worksheet to a table in an Access database
' this procedure must be edited before use
Dim db As Database, rs As Recordset, r As Long
    Set db = OpenDatabase("C:\Documents and Settings\bryan.chu\Desktop\Metrics\Survey.mdb")
    ' open the database
    Set rs = db.OpenRecordset(strTableName, dbOpenTable)
    ' get all records in a table
    Dim lngTotalRows As Long
    lngTotalRows = ShtSurveyData.Columns(1).SpecialCells(xlCellTypeConstants).Rows.Count - 1
    Dim i As Long
    Dim strFieldNames() As String
    ReDim strFieldNames(1 To 6)
    strFieldNames(1) = "Product"
    strFieldNames(2) = "Group"
    strFieldNames(3) = "SubGroup"
    strFieldNames(4) = "Category"
    strFieldNames(5) = "BankID"
    strFieldNames(6) = "Volume"
    Dim n As Integer
    n = 1
With rs
    For i = 1 To lngTotalRows
        .AddNew ' create a new record
        For n = 1 To 6
            .Fields(strFieldNames(n)) = ShtSurveyData.Cells(i + 1, n).Value ' add values to each field
        Next n
        .Update
    Next i
End With
    rs.Close
    Set rs = Nothing
    db.Close
    Set db = Nothing
End Sub

Open in new window

ASKER CERTIFIED SOLUTION

carsRST

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mikesuss

Take a look at this

http://www.exceltip.com/st/Import_data_from_Access_to_Excel_(ADO)_using_VBA_in_Microsoft_Excel/427.html

Jerry Paladino

Can you be more specific on what you are trying to accomplish? You have a list of data in Sheet1 and you want to run a SQL statement on the list and write the results to Sheet2. Is that correct? If so, that can be done with Excel's MS-Query built-in tool within the same workbook and you do not need to move the data to an Access database and back.

Shino_skay

ASKER

Hey ProdOps:

You got it right on the money. I never heard of MS-Query tool. I'll go look up on it, i hope it's free and vba-friendly.

Jerry Paladino

It is free and bundled with Excel and can be manipulated from VBA.
Save the attached file to C:\EE\myFile.xls on your PC. Delete several of the rows in the Subset worksheet. Then, right mouse click on any of the remaining cells and select REFRESH from the sub menu. It will execute the embedded SQL statement and read the values from the FullData tab.

myFile.xls

SOLUTION

Jerry Paladino

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shino_skay

ASKER

hey jerry, thanks for the attachment. just got out of an meeting so I'll take a look at it. appreciate it.

Shino_skay

ASKER

the attachment works. as far as selecting "data" and importing external data as noted in the pdf, that option is greyed out. Still trying to find out why.

Jerry Paladino

MS-Query may not have been installed when MS-Office was installed on your PC. However, I am surprised the attached file works if you do not have MS-Query. All of my PCs have it installed so I don't have a place to test how the file would work without MS-Query being installed. If your IT department will allow it to be installed you can try this path. Otherwise - one of the other solutions from above may be best.
Jerry

Norie

You could also use ADO to query the Excel data, from Excel.

Shino_skay

ASKER

hi imnorie,

would you mind providing a template on that? or a link if possible? Thanks.

Shino_skay

ASKER

hey carsRST, the method you provide works. Thanks for the 2 examples on retrieving the entire recordset and the fieldnames. How would you export the data from Excel to Access, I would like to learn the ADO method. Thanks.

SOLUTION

carsRST

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jerry Paladino

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shino_skay

ASKER

Hey PropOPs, thanks for your solution, it looks a bit more complicated than carsRST and I haven't tried it out yet but I will as soon as I get a grasp around this.

carsRST, your template work but boy it was a pain in the behind to write the Insert Into SQL. The DAO method is more code friendly (as my original post), Here is the code to export the sheet to access using the ADO method (below).

I'm going to take a wild guess you can't run SQL using the DAO method? which would make it pointless if I wan to run queries.

also, I read something a while ago saying that you shouldn't use "new" when you dimension a variable but should set it afterwards (at least for class variables). This that hold true for the line

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset

? Thanks everyone for the education!

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

Sub ADOFromExcelToAccess2()

Dim con As ADODB.Connection
Dim rs As ADODB.Recordset

Set con = New ADODB.Connection
Set rs = New ADODB.Recordset

Dim Connstr As String

Dim strDataBaseName As String
Dim strPathName As String

strDataBaseName = "Survey.mdb"
strPathName = ThisWorkbook.Path & "\" & strDataBaseName

Connstr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
          "Data Source=" & strPathName & ";" & _
          "Persist Security Info=False"

con.Open Connstr

Dim strFieldNames() As String
Dim lngTotalRows As Long

lngTotalRows = ShtSurveyData.Columns(1).SpecialCells(xlCellTypeConstants).Rows.Count - 1
Dim r As Long
Dim i As Long
Dim n As Integer


ReDim strFieldNames(1 To 6)
strFieldNames(1) = "[Product]"
strFieldNames(2) = "[Group]"
strFieldNames(3) = "[SubGroup]"
strFieldNames(4) = "[Category]"
strFieldNames(5) = "[BankID]"
strFieldNames(6) = "[Volume]"

Dim strValue() As String
ReDim strValue(LBound(strFieldNames) To UBound(strFieldNames))

n = 1

Dim strInsertSQL As String

For i = 1 To lngTotalRows
    For n = LBound(strFieldNames) To UBound(strFieldNames)
        strValue(n) = ShtSurveyData.Cells(i + 1, n).Value
    Next n

    strInsertSQL = "Insert into tblSurvey" & _
            "(" & strFieldNames(1) & "," & strFieldNames(2) & "," & strFieldNames(3) & "," _
            & strFieldNames(4) & "," & strFieldNames(5) & "," & strFieldNames(6) & ")" & _
            "Values(""" & strValue(1) & """,""" & strValue(2) & """,""" & strValue(3) & """,""" _
            & strValue(4) & """,""" & strValue(5) & """,""" & strValue(6) & """);"
    
    con.Execute strInsertSQL

Next i

con.Close
Set con = Nothing

End Sub

Open in new window

carsRST

Prods might be a little faster, but I've had datatype issues that way - the first few rows determine data type. So if u have a few rows of numbers and then later have strings, u run the risk of a bad upload. I like to have total control. Just me.

SOLUTION

Jerry Paladino

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Shino_skay

ASKER

cool, you guys are all great.