include access database in excel workbook

[Guest]

hello,

I did a small excel vba program that displays data from an access database.
The access database is used as a data container only. The client is now not
happy with the fact that he always has to take care of two files (the XLS and
the MDB), he'd like to have everything in one file. Is there a way to have
the whole database included in the excel workbook (e.g. as OLE object)? I
tried the OLE thing, but I was not successful...

using office 2003, by the way.

thanks a lot!

 

[Guest]

Have you thought about writing the macro in ACCESS rather than Excel? Is the
Customer not happy with the fact there arre two files, or is the problem with
the data entry being in Access and the Output being in Excel?

We need a little bit more explanation of what the customer requirements are
to determine the best solution. I think just adding a Front End (macro in
excel) for writing the data into Access is may be the right solution.

 

[Guest]

thanks for your reply!
the client is not happy with the fact that there are two files. And they
want to use Excel to see the data, as they might want to do further
calculations in Excel.
The macro in Excel only GETs data from the Access database, so Access is
only the data container, because there's too much data to have everything in
an Excel Workbook. (And there's no need to write TO the Database, the
database is "read-only"...)
hope this makes it a bit clearer

 

[Guest]

Don't you think the better solution is to keep the data in Access. You can
always use the menu option in Access to see the data in Excel? If the
customer is not happy, then simply perform the export from Access to Excel
once and give him the excel file. Don't tell the customer the source is
really an Access file.

Access Menu.
Tool - Office Links - Analyze it with Microsoft Excel

 

[Guest]

ok, I'll try to explain it more:
the Excel consists of some drop downs which the client uses to dynamically
get data from the Access database. This works fine, the only problem is that
the access database is an external file. I'm looking for a way to keep the
current functionality and layout but to kind of include the access database
into the Excel file (just because the client is afraid that when e.g.
travelling, he only copies the Excel file to the local disc but not the
Access database)

 

[Guest]

Another suggestion. Store the database as Excel (not Access) in the
workbook. You can export the data from access into excel and then hide and
protect the worksheet as the source data. Let the customer use a copy of
this worksheet that he can format and make changes to.

What you probably want is simply add the Access file to the excel worksheet
by doing the following

1) Insert Menu - Object - Create From file Tab
2) browse for the file
3) Check the box Display as Iconj.

I performed this operation while recording a macro and this is the code it
produced the macro below, but it doesn't work. Need to figure out what the
problem is with this recorded macro.

ActiveSheet.OLEObjects.Add(Filename:="C:\TEMP\Part Log.mdb", Link:= _
False, DisplayAsIcon:=True, IconFileName:= _
"""C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE""
/NOSTARTUP ""%1""" _
, IconIndex:=0, IconLabel:="C:\TEMP\Part Log.mdb").Select

 

[Guest]

we're getting closer now.
I thought about the "Insert Menu - Object - Create From file" solution too,
but it didn't work. The reason why using access is that there's just too much
data to have it stored in an Excel Worksheet (the size of the access database
is about 35MB). I could manually add the MDB as an object, but I didn't find
a way then to create the connection to it from within the Excel macro...

 

[Guest]

It seems to me that the only solution is an Access solution and not an excel
solution. The Access file is too large for excel, but excel is not too large
for Access.

Create macros iin access to automatically produce the excel worksheet and
format the excel spreadhseet with basic features like drop down list or
autofilters.

Maybe the customer needs some Command scripts to automatically copy both the
excel and access files from his Office PC to his Lap Top.

How often does the Access Database get updated? How does the customer
update the spreadsheet after the Database get updated. You need to look at
all the customer processes first, then look for a solution that solves all
the customer problems. I know you have partially have done this work, but
does the customer know all the options. Sit down and write a requirement
list and do the comparison with all the options. This doesn't have to be
fancy. Scribble a couple of notes on a piece of paper if necessary. Having
everything writen makes it easier to make decisions.