Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Nov 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Excel Form Populating Access Db (Access 2003)

    Can this be done?
    I would like to make a user entry template in Excel, so that any user can open the template (read-only), enter their data and then click submit. The data would then populate an Access table for later analysis by a superuser. Multiple users will be required to use the template.
    I realise this can be easily achieved using a form in Access, but that will then require all users have access to MS Access, which is not possible.
    Also I find creating and validating in Excel far easier than Access where my knowledge is somewhat limited.

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

    Re: Excel Form Populating Access Db (Access 2003)

    A belated welcome to Woody's Lounge!

    Should a new table be created each time the Submit button is clicked? Or should records be appended to an existing table?

    (Probably more questions to come later, depending on the answer)

  3. #3
    New Lounger
    Join Date
    Nov 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Form Populating Access Db (Access 2003)

    Thanks for the welcome.

    Records should be appended.

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

    Re: Excel Form Populating Access Db (Access 2003)

    The idea would be as follows:
    - Make sure that fields in the Access table are in the same order as the columns in the worksheet.
    - I have assumed that the first row contains column headers (field names)
    - Set a reference to the Microsoft DAO 3.6 Object Library in Tools | References in the Visual Basic Editor.
    - This library lets you work with Access databases even if the user doesn't have Access installed.
    - Use code like that in the attached text file. You'll have to substitute the correct names, of course.

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

    Re: Excel Form Populating Access Db (Access 2003)

    The attachment from my original reply was lost in the Lounge server crash of August, 2007.

    Unfortunately, I don't have the original code any more, but here is a new version. As in the original one, you'll have to set a reference to the Microsoft DAO 3.6 Object Library and to alter the path of the database and the name of the table to match your situation.
    Attached Files Attached Files

  6. #6
    New Lounger
    Join Date
    Nov 2006
    Posts
    5
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Excel Form Populating Access Db (Access 2003)

    Many thanks Hans!

Posting Permissions

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