Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Link to Access (Excel XP)

    I would like users with Excel to interact with an Access DB. Is this possible. Will they be able to trigger a macro inside Access that will automaticly email them a report based on who the user is?

    TIA

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

    Re: Link to Access (Excel XP)

    Do these users have Access installed on their PCs?
    Is it essential that the action is initiated from Excel, and if so, what role does Excel play in this?

  3. #3
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to Access (Excel XP)

    No they don't have access. I would like them to use Excel to trigger import of files and running queries and reports via Excel. At this stage 1 user is responsible to run all the reports and if possible I would like the recepient of the reports to initiate the action

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

    Re: Link to Access (Excel XP)

    It is possible to work with tables and queries in an Access database from Excel without having Access installed, but it is not possible to run macros, export reports etc. The user MUST have Access for that.

  5. #5
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to Access (Excel XP)

    Thanks

  6. #6
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Vienna, Wien, Austria
    Posts
    5,009
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to Access (Excel XP)

    >Will they be able to trigger a macro inside Access that will automaticly email them a report based on who the user is?

    >I would like them to use Excel to trigger import of files and running queries and reports via Excel.

    To repeat what <!profile=HansV>HansV<!/profile> said: the manipulation of Access data & running of reports can only be done inside Access. The actual data, however, is available to both Excel & to Word. It is technically possible to, for example, extract Access data into Word, format it into a report and then have it available by e-mail from Word. If you are talking about 100 extra Access licenses as an alternative, then the work may be worthwhile. If you are only talking about, say, 10 extra Access licenses, then it may not.

    Similarly, you may be able to accomplish something with Excel - with the same (or even greater) amount of effort.

    HTH
    Gre

  7. #7
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to Access (Excel XP)

    Thanks for the information. Won't it be possible to set an ontime event in Access macro to be triggered it an value changes/appears in a table, then from Excel change the value in the table or am I dreaming.

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

    Re: Link to Access (Excel XP)

    But where would Access run?

  9. #9
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to Access (Excel XP)

    I will be running Access on a shared machine/network

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

    Re: Link to Access (Excel XP)

    Here is a possible solution, there may be better ones.

    The backend database with the tables would have to be in a network folder where all users have full permissions.

    The backend would contain (among others) a table tblUsers with the following fields:
    ID: AutoNumber, primary key
    Email: text, size large enough for the longest e-mail address you need.
    Handled: yes/no
    Add other fields if you need to pass or store specific information. Date/time fields DateAdded and DateHandled could be useful, for instance.

    The users with Excel would use DAO or ADO code to add a new record to the tblUsers table.

    One PC with Access installed would have to have a frontend database with forms, reports etc., permanently open.
    In the frontend database, a form would be permanently open.
    This form would have its TimerInterval property set to for example 300000 (milliseconds), this would cause the On Timer code to run every 5 minutes (5 minutes = 300 seconds = 300000 milliseconds)
    The On Timer event of the form would open a recordset that selects all records from tblUsers for which Handled is False, loop through the records, send the report for each record and set the Handled field to True to prevent the record from being processed again.

  11. #11
    3 Star Lounger
    Join Date
    Aug 2001
    Location
    Cape Town, South Africa, South Africa
    Posts
    399
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Link to Access (Excel XP)

    Brilliant

    Thanks

Posting Permissions

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