Database Link

[Guest]

Hello,

I am working with budgets, for each item I have a Name, Unit, and Price per
unit then I just specify a Quantity multiply it by the Price per unit and get
a Total...

So far what I have is a Excel sheet with Name, Unit and Ppu, I made it to a
List, and defined a Name for the item's Name column, so each entry I add
automatically becomes part of it. Then on another sheet I used Data
Validation to choose from those Names from the List, the Unit and Ppu are
then taken from the "database sheet" with a VLOOKUP formula and then I just
enter a Quantity and get a subtotal.

It works great, but I would like to separate the database from the file, to
make the file lighter and to have other people being able to use it too. Does
anybody know how to:

A) Take that info from a Access database, so far my attempts copy the whole
database as a table to the Excel sheet, I would like to have it as I have it
right now, just not having all the database data inside the Excel file, or,

B) Take that info from a different Excel file, the problem with that is that
Data Validation is only allowed from the same file

thanks in advance

 

[Bryan Hessey]

Ed,

I think you are probably looking towards the functions supplied by DDE,
and in MS Access, help on the the DDE Server, together with the Excel VB
help on DDE Initiate etc, will indicate how to query an Access database
and receive the reply.

I have not used DDE in either Access nor Excel, so am of no further use
to you in this respect, but hope this may help guide your questions.

--

 

[Arvi Laanemets]

Hi

As much as I see, you have no formulas in units list - only values. Such
tables don't affect perfomance much, and having them in separate source file
you probably gain nothing - but reverse is more likely.

The main cause for slow perfomance for such databases are formulas -
especially when they contain volatile or array functions. Another possible
cause is, that Excel archives all changes made - this may occur when the
workbook is set for shared use.

Solutions:
1) When your workbook size is growing seemingly on no reason: save it under
different name , then save this new workbook under old name again (overwrite
it) from time to time. SaveAs drops workbook's history.
2) Replace formulas in old entries with values (I have used an Open event
for this sometimes) - p.e. the code overwrites all formulas in row with some
date field older than X days/months with their values.
3) When there are some tables with lot of complex formula, and which aren't
changed every day, split them into separate workbook(s). When they are used
as datasources for another worksheets, then import them into your base
workbook using ODBC query - you get values instead of formulas in mirrored
tables.
4) When you have many users working with workbook, but every user has edits
his own set of data (+ uses some common source tables), then consider having
a separate workbook for evwery such user + an summary workbook, to where all
users tables are read in through links or ODBC queries, and where all common
tables are kept (user workbooks mirror them also through links or ODBC
queries). An additional advantage - all users can edit their tables without
interfering with others.