+ Reply to Thread
Results 1 to 4 of 4

Linking an Access Database with Excel

  1. #1
    Registered User
    Join Date
    Northampton, UK
    MS-Off Ver
    version 2212, office 365 enterprise

    Question Linking an Access Database with Excel

    I need help linking an Access Database to an Excel spreadsheet. I need to be able to read and edit the spreadsheet in excel and have that update onto the access database and vice versa. I have so far managed to import the data onto a speadsheet and can refresh the data to update it but it doesn't seem to work the other way.

    I have also tried to create a macro in Access to link the data from excel but the changes I make don't seem to register.

    Both software versions are from Office 2003.

    Is there an easier way to do this? Keep in mind I have quite a basic understanding of the programs.

  2. #2
    Registered User
    Join Date
    I had similar issues with trying to run Project VBA from Excel. What
    you need to do is make sure the correct References are selected in
    your Excel VBA compiler. Open the VBA window, click Tools
    =>References and then check Microsoft Access 11.0 Object Library. The
    two should be able to communicate after that. Depending on your knowledge of VBA - you should be able to write your code to bring in the data from Access and organize it based on some event. Is this what you're looking for?

    Also, if you need to run this from different computers, or have others who don't know anything about VBA, you could program the reference in in your macro. Chip Pearson (http://www.cpearson.com/excel/MainPage.aspx) explained
    how to do this in an email:
    You can certainly add a reference using VBA code. To accomplish this, two
    things must be true. First, the object library being referenced must exist
    on the target machine and must be registered with Windows in the Registry.
    For example, you can safely set a reference via code to the VBA
    Extensibility Library since you can be sure that the library will exist on
    the machine. However, if you are distributing a DLL or typelib that you
    created yourself, you must put the file in an appropriate location and then
    register that file with Windows, typically by using the RegSvr32.exe program
    (or RegAsm.exe for NET components).

    Next, the code that adds the reference must execute before the VBA RunTime
    decides to recompile the code. If you distribute a workbook between
    machines, VBA may determine that the code should be recompiled when the
    workbook is opened, prior to the execution of Auto_Open or Workbook_Open.
    (The most common reason for a recompilation at start up is that the workbook
    was written and last compiled on a different version of Excel and/or Windows
    than the versions on which it is being presently run.) Because the
    compilation takes place before the code to add the reference is executed,
    you will get compiler errors (which cannot be ignored or trapped with an On
    Error statement). The error you will get is "User-defined type not
    defined". For example, if you have a line of code like
    Please Login or Register  to view this content.
    and SomeObj is defined in a DLL that is not referenced, the compiler will
    complain about the SomeObj data type before the file that defines SomeObj is
    added to the references list. It is sort of a chicken and the egg situation
    - the code can't run until the reference is added, but the reference cannot
    be added until the code runs.

    If you know the exact file name to be referenced, you can add a reference
    using AddFromFile. For example, the following adds a reference to the VBA
    Extensibility library.
    Please Login or Register  to view this content.
    However, the same DLL or typelib may reside in different locations on
    different machines and operating system versions. In this case, you can use
    the GUID (Globally Unique Identifier) of the library. The GUID is unique
    to a specific typelib or DLL, and the GUID for that component will be the
    same on all machines. For example, the GUID for the Extensibility library is
    used only by that library and nothing else, and the GUID will be the same on
    every machine regardless of the operating system version and regardless of
    where in the machine that file resides.
    Please Login or Register  to view this content.
    By using 0s for Major and Minor, you will reference the most recent version.
    When adding a reference via GUID, VBA will search the registry for the
    specified GUID and take a few jumps around the registry to find the file
    associated with the GUID.

    You can use AddFromFile or AddFromGUID in the Workbook_Open event to set the reference when the workbook is opened. Be sure to use an On Error statement
    to ignore the error is you are attempting to add a reference that already

    If necessary, you can use what is called "Late Binding" and declare
    everything that exists in the library to be referenced As Object rather than
    As WhateverObject and use the CreateObject method to create an instance ofthe root object. For example, with "Early Binding" you would access the
    Scripting RunTime's FileSystemObject with code like:
    Please Login or Register  to view this content.
    This code requires a reference to the "Microsoft Scripting RunTime Library".
    Instead of relying on the reference to the Scripting Runtime, you can use
    Late Binding as shown below.
    Please Login or Register  to view this content.
    You'll take a performance hit with late binding, since code must execute
    behind the scenes to determine what the properties of and methods an object

    Chip Pearson
    Microsoft Most Valuable Professional
    Excel Product Group 1998 - 2008
    Pearson Software Consulting, LLC
    The San Diego Project Group, LLC
    Hopefully that helps...


  3. #3
    Registered User
    Join Date
    Northampton, UK
    MS-Off Ver
    version 2212, office 365 enterprise
    Thanks dude,

    but to my understanding and according to these web sites:



    you can't link data from excel to an existing table in access. Access creates a linked table.

    What I needed was to be able to edit the data in access and in excel and have them be linked (update automatically) but apparently this cannot be done!

    Do you have any ideas?

  4. #4
    Registered User
    Join Date

    I'm sorry, but i have never used Access and don't have any clue how to link the two. Also, priorities shifted and the scope of my project changed so i still have yet to actually get passed learning how to have Excel VBA to recognize Project. One thing i have learned over the last few months of playing with VB is that you can do damn near anything that comes to mind with regards to VB and Microsoft Office apps. Don't give up hope just yet. Somebody out there knows.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)


Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1