Excel as MS Access front end

Posted on 2016-09-06
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
LVL 32

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 32

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.

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 32

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.

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

LVL 28

Assisted Solution

by:Subodh Tiwari (Neeraj)
Subodh Tiwari (Neeraj) earned 125 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 45

Expert Comment

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

Assisted Solution

PatHartman earned 125 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 250 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
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…

920 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

12 Experts available now in Live!

Get 1:1 Help Now