Solved

Excel as MS Access front end

Posted on 2016-09-06
11
98 Views
Last Modified: 2016-10-10
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
Comment
Question by:Bill Ross
11 Comments
 
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.
0
 
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.

Thanks,

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

Thanks
Rob
0
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)

 
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?

Thanks,

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

Thanks
Rob
0
 
LVL 29

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.
http://www.myengineeringworld.net/2013/10/running-access-queries-from-excel-vba.html
0
 
LVL 45

Expert Comment

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

Assisted Solution

by:PatHartman
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.
0
 
LVL 31

Accepted Solution

by:
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, _
      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
 
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.

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

Best,

Bill
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

786 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