Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Excel as MS Access front end

Posted on 2016-09-06
Medium Priority
Last Modified: 2016-10-10

Is it possible to click a button in an Excel workbook and import a parameter query from an Access database into a new sheet in the same Excel workbook?  I need to then format the sheet.


Thanks in advance.  Bill
Question by:Bill Ross
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
LVL 33

Expert Comment

by:Rob Henson
ID: 41785967
On the Data Tab, look at the "Get External Data" options, one of which is from Access.
LVL 14

Author Comment

by:Bill Ross
ID: 41785980
Hi Rob,

That's a manual process and works OK but I need to automate it so it's transparent to my client.


LVL 33

Expert Comment

by:Rob Henson
ID: 41786009
Hi Bill,

In which case, I suggest you give some more detail to the question so that other EE users might be able to help further.

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 14

Author Comment

by:Bill Ross
ID: 41786034
Hi Rob,

More detail...

I have an Access database with 500k++ rows of data.  In it we have several Access queries that pare the data down and that are exported manually to an Excel sheet.  Users do not have Access installed.  What I want to do is create an Admin sheet in a workbook that has buttons to import the various queries and format the sheets - or maybe just drop the data into pre-formatted sheets...

The queries are parameter queries so I need to pass the parameters or the SQL to the Access DB.  I suspect I will need to use VBA but not sure how to do this in Excel.  I could do in an Access form - I'm just not familiar with Excel automation.

What more information is needed?


LVL 33

Expert Comment

by:Rob Henson
ID: 41786049
Hi Bill

Sorry but that is beyond my expertise so will have to bow out and leave it for other Experts to help you.

LVL 32

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 500 total points
ID: 41786066
The steps involved in importing the data from access database are as below...

  1. Create and open a connection to the Access database.
  2. Create a recordset that will contain the query results.
  3. Create the necessary SQL select statement or set the query name.
  4. Open the recordset.
  5. If the recordset is not empty, write its data into Excel.
  6. Close both recordset and connection.

For more details, visit the following link. This will give you a stating point.
LVL 46

Expert Comment

ID: 41786392
You might create a querytable result in  a worksheet and then update it when the workbook is opened.
LVL 39

Assisted Solution

PatHartman earned 500 total points
ID: 41786656
I'm pretty sure that you don't see parameter queries when you use the connection dialog to connect to an Access database.  So, you would need to connect to the tables directly and then make your own parameter query in Excel.

You cannot automate Access from Excel unless a retail version of Access is installed.  You could try using the runtime engine but I'm pretty sure it wouldn't work.

Access - the rapid application development tool is a separate product from Jet and ACE the database engines.  The database engines Jet (.mdb) and ACE (.accdb) are free and can be installed and used with ODBC drivers and do not require "Access" to be installed.  But automation is a different process and that required accessing objects in the database without using the ODBC driver.

Depending on how the data is being used, you might be able to build them an interface that relies on forms that you create to select data and export it to Excel.  The users could install the Access runtime engine (which is free), and run the exports from Access.
LVL 31

Accepted Solution

Helen Feddema earned 1000 total points
ID: 41796052
You might have to run a make-table query to get the data from the parameter query into a temp table, which could then be imported into Excel using the CopyFromRecordset method.  The make-table query could be run from code -- here is some sample code for doing various things in an Access database from elsewhere:

Public Sub OpenAnotherDatabase()
'Created by Helen Feddema 14-Feb-2010
'Last modified by Helen Feddema 14-Feb-2010

   Dim appAccess As New Access.Application
   Dim strDBNameAndPath As String
   Dim dbs As DAO.Database
   Dim rst As DAO.Recordset
   Dim dbe As DAO.DBEngine
   'Change to your db name and path
   strDBNameAndPath = "G:\Documents\Access 2002-2003 Databases\General.mdb"
   appAccess.Visible = True
   appAccess.OpenCurrentDatabase filepath:=strDBNameAndPath, _
   'Run a procedure
   'appAccess.Run "PrintOrdersReport"
   'Run a macro
   'appAccess.DoCmd.RunMacro "mcrPrintOrdersReport"
   'Run an action query
   'appAccess.DoCmd.OpenQuery "qryDeleteSomeOrders"
   'Run SQL code
   strSQL = "DELETE tblOrders.ShippedDate FROM tblOrders WHERE ShippedDate = #8/4/1994#;"
   Debug.Print "SQL string: " & strSQL
   'appAccess.DoCmd.RunSQL strSQL
   'Iterate through a recordset
   Set dbe = appAccess.DBEngine
   Set dbs = dbe.OpenDatabase(strDBNameAndPath)
   Set rst = dbs.OpenRecordset("tblCategories")
   Do Until rst.EOF
      Debug.Print rst![CategoryName]
   Set dbs = Nothing
   Set appAccess = Nothing
End Sub

Open in new window

LVL 14

Author Comment

by:Bill Ross
ID: 41796061
Hi Experts,

I've been pulled away from this project for a bit.  It looks like there are several good options and I'll get back to it this week.  Thanks for your help!  I'll let you know the result.

LVL 14

Author Closing Comment

by:Bill Ross
ID: 41836835
Hi Experts,

I combined several of these.  The real trick was getting the temp tables created and Helen - thanks for the tips!  Solution is working nicely.



Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …

721 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