Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Feb 2002
    Location
    Taranaki, New Zealand
    Posts
    193
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Linking with access (XP)

    I have also posted this message in access. I have been asked to link an access database with a spreadsheet in excel. Lots of calculations need to be done. This is not the problem. How do I link the information on the database to a spreadsheet ?? Do I use a macro and if yes, which one. So when the database is updated, so is the excel spreadsheet.
    Thanks for all the help..

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Linking with access (XP)

    If you want to display data from Access in an Excel spreadsheet, use the Excel menu option Data/Get External Data/New Database Query... This will start a wizard that leads you through the process.
    Once you've imported the Access data, the Excel spreadsheet won't be updated automatically. You can use the Data/Refresh Data menu option for this, or the Refresh Data button on the External Data toolbar.

    If you want to display or edit Excel data in Access, you can link an Excel "table" using the Access menu optionFile/Get External Data/Link Tables... You can then edit the table both in Excel and in Access.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Johannesburg, Gauteng, South Africa
    Posts
    104
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Linking with access (XP)

    I have done quite a bit of this lately to allow users to get hold of info from a db on a network server. I don't know if this is the best method, but it works for me. Hopefully other loungers will voice their opinion about what I describe below.

    You use MS Query through Excel to get the data into your spreadsheet/s. There are two main steps :

    1. Define a system DSN using your ODBC Data Source Administrator (<font color=red>Start/Settings/Control Panel/Administrative Tools/Data Sources (ODBC)</font color=red> under Win 2K) that points to your db - presumably on the network somewhere. If you have implemented Access security in your db, then you can embed a username and password in the DSN (preferably a user with minimal rights such as read-only), as well as point to the relevant workgroup information file (often System.mdw). To be honest, I don't remember why I use a <font color=448800>System</font color=448800> DSN as opposed to either a <font color=448800>User</font color=448800> or <font color=448800>File</font color=448800> DSN, but it works. Maybe someone out there can explain ?

    2. In Excel, go <font color=red>Data/Get External Data/New Database Query</font color=red> and then choose the DSN that you just created. Continue working your way through the wizard where you will choose the table or query that contains the data that you want and then select the required fields. Complete the wizard and the data will be returned to your spreadsheet wherever you choose.

    There are two features that I have found really useful in MS Query. You can pass parameters to the query from your spreadsheet and you can fill formulae down next to your returned data. As your data grows, the formulae automatically grow down as well.

    I often pass two date range parameters in the form of <font color=448800>From = Today() - 7</font color=448800> and <font color=448800>To = Today()</font color=448800> back to the query so that when the user opens the (read-only) spreadsheet, the last 7 days' worth of data are automatically displayed.

    A word of warning though - the MS Access ODBC driver does not understand many functions, so if your data is from a query as opposed to a table, make sure that you don't have any complicated calculations in any of the derived fields in your query in Access. It doesn't even understand the nz() function, not to mention any user defined functions that you may have written.

    HTH. If my description hasn't confused you and the above sounds like it will do what you want and you need more detailed instructions, feel free to ask.

    Regards,

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •