Solved

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

Posted on 2010-08-31
18
1,398 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
  • 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 4

Expert Comment

by:mikesuss
ID: 33569507
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
 

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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 33

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

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

This is an explanation of a simple data model to help parse a JSON feed
If you’re thinking to yourself “That description sounds a lot like two people doing the work that one could accomplish,” you’re not alone.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now