To Link or Not to Link (a table)

[Amanda]

What are the downsides of linking tables to a DB (as opposed to just
importing the data and keeping it there)? These tables are all excel
files.

Does anyone have any advice on whether it's better to link tables to DB
or to import them?

I'm working in an environment where there are many, many excel files
that should all be talking to one another and should be more easily
manipulated. (Hence, my efforts to create a DB that'll do this.)

However, I can ask users to a) continue updating excel files as they
have been and link the spreadsheets to the access DB, or b) create a
way in access to import data and then tell the users to import the data
into access.

My concern with option A is that an excel file could be accidentally be
deleted or the data could be easily messed up (whereas with the access
DB, I'm going to add some security). Also, I'm afraid of the DB losing
connection with any of these excel docs... or something weird like that
happening.

My problem with option B is only that it's more time consuming (for me)
and that my users will need to create a new habit.

Thank you very much in advance!!

 

[Douglas J Steele]

In actual fact, the ability to update linked Excel tables was recently
removed from Access 2002 and 2003 after a lawsuit Microsoft lost, so at
least some of your concerns may no longer be an issue.

It wouldn't be overly difficult to add an option to reimport the data from
Excel, and either automatically run it everytime the database is opened, or
let the users decide when to rerun it.

 

[Tony Toews]

I'm working in an environment where there are many, many excel files
that should all be talking to one another and should be more easily
manipulated. (Hence, my efforts to create a DB that'll do this.)

However, I can ask users to a) continue updating excel files as they
have been and link the spreadsheets to the access DB, or b) create a
way in access to import data and then tell the users to import the data
into access.

Why not create a DB with the functionality required of the 2 or 5 most
heavily used Excel files. Then import the data, move those Excel
spreadsheets to another folder inaccessible to the users and get the
users to start using your MDB. Once that's running smoothly then
repeat the process for the other spreadsheets.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Amanda]

Thanks!

What exactly can no longer be done? I don't need to update Excel from
Access but vice versa. That can still be done, correct?

I did just notice one problem - I can't open an Excel file if Access
was opened before it and is referencing that file.

Do you know of any other difficulties in linking excel files as tables
in Access?

Thank you again!

 

[Douglas J Steele]

Yes, you can read the Excel data from Access, but you can't update it from
Access.

Afraid I can't offer too much help in terms of compiling a list of
difficulties. I very seldom link Excel spreadsheets: my applications don't
normally need to get data from Excel, although they use Automation to take
data from Access, write it to Excel then generate graphs in Excel. (Easier
to do that than doing it in Access, since I can mail the spreadsheets to
people)

 

[Ron2006]

Another aspect is the types and frequency of data in the spreadsheets.

I have had better success in being able to even look at data that was
in spreadsheets if I import them than if I simply link to them. For
instance if there is a field that will sometimes contain alpha data,
when Access is linked to it, it will often determine that the data is
numeric and throw out the alpha entries. Importing that same data can
be controlled so that the field can be defined the way I want it to be.

If you are linked to the spreadsheet and open then others can't get in.
If the linked spreadsheet is erased and then recreated then I believe
that Access loses the link (I am not sure of that one but believe that
was what was causing me problems with some linked sheets.)
If the user changes the title row or the tab name of the spreadsheet
you will have a problem.

Develop the functionality that the user needs in Access. Pull the
sheets in and then hide them and have the user use Access. The sooner
the better.

I export to Excell and manipulate a lot of data in excell after the
fact to present it to the user, but have avoided linked spreadsheets as
much as posible.

Douglas's comments are good advice as far as I am concerned.

Ron