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

Excel as MS Access front end

HI,

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.

Ideas?

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

Thanks,

Bill
0
 
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.

Thanks
Rob
0
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
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?

Thanks,

Bill
0
 
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.

Thanks
Rob
0
 
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.
http://www.myengineeringworld.net/2013/10/running-access-queries-from-excel-vba.html
0
 
aikimarkCommented:
You might create a querytable result in  a worksheet and then update it when the workbook is opened.
0
 
PatHartmanCommented:
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.
0
 
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, _
      exclusive:=False
      
   '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]
      rst.MoveNext
   Loop
   rst.Close
    
   Set dbs = Nothing
   Set appAccess = Nothing
   
End Sub

Open in new window

0
 
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
0
 
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.

Best,

Bill
0
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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