• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 145
  • Last Modified:

Excel as MS Access front end


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
Bill Ross
Bill Ross
3 Solutions
Rob HensonFinance AnalystCommented:
On the Data Tab, look at the "Get External Data" options, one of which is from Access.
Bill RossAuthor Commented:
Hi Rob,

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


Rob HensonFinance AnalystCommented:
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.

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Bill RossAuthor Commented:
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?


Rob HensonFinance AnalystCommented:
Hi Bill

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

Subodh Tiwari (Neeraj)Excel & VBA ExpertCommented:
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.
You might create a querytable result in  a worksheet and then update it when the workbook is opened.
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.
Helen FeddemaCommented:
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

Bill RossAuthor Commented:
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.

Bill RossAuthor Commented:
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.


Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now