Solved

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

Posted on 2010-08-31
18
1,459 Views
Last Modified: 2013-11-27
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

0
Comment
Question by:Shino_skay
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
  • 3
  • +2
18 Comments
 
LVL 16

Accepted Solution

by:
carsRST earned 383 total points
ID: 33569485
You're not going to notice any difference between DAO and ADO.

            sub pullData()
		    Dim con As New ADODB.Connection
		    Dim rs As New ADODB.recordset

		    dbPath  = "c:\....\file.mdb"

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

		    con.Open ConnStr

		    set rs.activeconnection = con

		    rs.open "select * from <<table name>>"
		    if not rs.eof then
			    for i = 0 to rs.fields.count-1
				activesheet.cells(1,i+1) = rs.fields(i).name
			    next i
		    end if


		    activesheet.cells(2,1).copyfromrecordset rs

		    rs.close
		    con.close
		    set rs=nothing
		    set con = nothing
            
            end sub

Open in new window

0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 33569651
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.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Shino_skay
ID: 33569743
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.
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 33569800
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
0
 
LVL 16

Assisted Solution

by:Jerry Paladino
Jerry Paladino earned 117 total points
ID: 33570438
The attached file has step-by-step screen shots for using MS-Query with Excel.  It may be helpful if you decide to use MS-Query.
Jerry

Reading-Excel-Datal-via-MS-Query.pdf
0
 

Author Comment

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

Author Comment

by:Shino_skay
ID: 33571573
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.
0
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 33571655
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
0
 
LVL 34

Expert Comment

by:Norie
ID: 33575931
You could also use ADO to query the Excel data, from Excel.
0
 

Author Comment

by:Shino_skay
ID: 33576271
hi imnorie,

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

Author Comment

by:Shino_skay
ID: 33576851
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.
0
 
LVL 16

Assisted Solution

by:carsRST
carsRST earned 383 total points
ID: 33576931
Glad it worked.

See quick exaple code below.  Just conform to your situation.


            sub pushData()
		    Dim con As New ADODB.Connection

		    dbPath  = "c:\....\file.mdb"

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

		    con.Open ConnStr
		    
		    
		    lastrow = Cells.SpecialCells(xlCellTypeLastCell).Row
		    
		    with activesheet
		    
		    	for i = 2 to lastRow 'assume header row on first row
		    	
		    		'build insert statement
		    		insertSQL = "insert into <<table>> (fld1, fld2) values ('" & .cells(i,1) & "', '" & .cells(i, 2) & "')"
		    		
		    		con.execute insertSQL
		    	next i
		    	
		    end with



		    con.close

		    set con = nothing
		    
		    msgbox "Import complete"
            
            end sub

Open in new window

0
 
LVL 16

Assisted Solution

by:Jerry Paladino
Jerry Paladino earned 117 total points
ID: 33576937
ADO Examples - Excel to Access
Sub ExportCommDataToAccess()

' Example Procedure assumes that:
' The Access db is - g:\CMD\ProfitDatabase.mdb
' Access Table name is - YearlyCommision
' Excel file is - C:\Desktop\DailyCommission_20100524 (or current date)
' Worksheet name that contains the expert data is - DailyCommission
' Using Access 2003 and Excel 2003 (2007 syntax also included)
'
    Dim cn As Object, strQuery As String
    Application.StatusBar = "Appending Current Data to Access Table YearlyCommission"

    Set cn = CreateObject("ADODB.Connection")

    mydb = "g:\CMD\ProfitDatabase.mdb"
    thiswb = "'C:\Desktop\DailyCommission_" & Format(Date, "YYYYMMDD") & "'"

    With cn
        'For Access 2003 Database
        .Provider = "Microsoft.Jet.OLEDB.4.0"

        'For Access 2007 Database
        '.Provider = "Microsoft.ACE.OLEDB.12.0"
        .ConnectionString = mydb
        .Open
    End With

    strQuery = "INSERT INTO [YearlyCommision] " & _
               "SELECT " & _
               " [RepName] as [RepName], " & _
               " [GL Date] as [GL Date] " & _
               " [Sales] as [Sales] " & _
               " [Commission] as [Commission] " & _
               "FROM [DailyCommission$] " & _
               "IN " & thiswb & "  'Excel 8.0;HDR=Yes;' "

            '  For Excel 2007
            '  "IN " & thiswb & "  'Excel 12.0 Xml;HDR=Yes;' "

    cn.Execute strQuery
    cn.Close
    Set cn = Nothing
    Application.StatusBar = False

End Sub

========================================================
SHORT VERSION....

Sub ExportExcelDataToAccess()
    Dim cn As Object, strQuery As String
    Set cn = CreateObject("ADODB.Connection")
    mydb = "C:\EE\Sample.accdb"
    thiswb = "'C:\EE\myExcel.xlsx'"
  
    With cn
        .Provider = "Microsoft.ACE.OLEDB.12.0"   'For Access 2007 Database
        .ConnectionString = mydb
        .Open
    End With
        
     strQuery = "INSERT INTO [DataFromExcel] Select * FROM [PartsData$] " & _
                "IN " & thiswb & "  'Excel 12.0 Xml;HDR=Yes;' " & _
                "Where [PartNum] Is Null ; "
      
    cn.Execute "DELETE FROM DataFromExcel"
    cn.Execute strQuery
    cn.Close
    Set cn = Nothing
End Sub

Open in new window

0
 

Author Comment

by:Shino_skay
ID: 33578165
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

0
 
LVL 16

Expert Comment

by:carsRST
ID: 33578217
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.
0
 
LVL 16

Assisted Solution

by:Jerry Paladino
Jerry Paladino earned 117 total points
ID: 33578394
Shino,
Look at the short version of the code I rprovided... It is in the lower section of the code box above.  The long version just had a comments and options for 2003 or 2007.
carsRST is correct that the INSET INTO is faster than the row-by-row but it also has the possible datatype issues if you not sure of the data you are moving.  The row-by-row definitely provides more control over the data.  
Either way is correct and just gives you more options to choose from.
0
 

Author Comment

by:Shino_skay
ID: 33578582
cool, you guys are all great.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question