Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

To Link or Not to Link (a table)

Discussion in 'Information Technology' started by Amanda, Jul 28, 2006.

Tags:
  1. Amanda

    Amanda
    Expand Collapse
    Guest

    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!!
     
  2. Loading...

    Similar Threads Forum Date
    Eh Janam Tumhare Lekhe... Official Movie Link... Announcements Jun 3, 2015
    Sikhism Complete Shalok Mehala 9 (Link) Videos Apr 22, 2014
    UK British PM orders probe into Thatcher link to Operation Bluestar (UPDATED) Breaking News Jan 14, 2014
    India Gwalior: A Unique Link to India's History Breaking News Nov 16, 2013
    Researchers establish link between racism and stupidity General Sep 28, 2013

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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.

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Amanda" <amandalaine@gmail.com> wrote in message
    news:1152727236.409253.311250@h48g2000cwc.googlegroups.com...
    > 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!!
    >
     
  4. Tony Toews

    Tony Toews
    Expand Collapse
    Guest

    "Amanda" <amandalaine@gmail.com> wrote:

    >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
     
  5. Amanda

    Amanda
    Expand Collapse
    Guest

    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 wrote:
    > 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.
    >
    > --
    > Doug Steele, Microsoft Access MVP
    > http://I.Am/DougSteele
    > (no e-mails, please!)
    >
    >
    > "Amanda" <amandalaine@gmail.com> wrote in message
    > news:1152727236.409253.311250@h48g2000cwc.googlegroups.com...
    > > 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!!
    > >
     
  6. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    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)

    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Amanda" <amandalaine@gmail.com> wrote in message
    news:1152729582.089308.262870@35g2000cwc.googlegroups.com...
    > 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 wrote:
    > > 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.
    > >
    > > --
    > > Doug Steele, Microsoft Access MVP
    > > http://I.Am/DougSteele
    > > (no e-mails, please!)
    > >
    > >
    > > "Amanda" <amandalaine@gmail.com> wrote in message
    > > news:1152727236.409253.311250@h48g2000cwc.googlegroups.com...
    > > > 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!!
    > > >

    >
     
  7. Ron2006

    Ron2006
    Expand Collapse
    Guest

    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
     

Share This Page