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.

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
exists.

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
supports.


Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group 1998 - 2008
Pearson Software Consulting, LLC
The San Diego Project Group, LLC
www.cpearson.com

Hopefully that helps...

Regards,
Thedude

Thanks dude,

but to my understanding and according to these web sites:

http://office.microsoft.com/en-us/ac...950951033.aspx

http://en.allexperts.com/q/Using-MS-...cess-excel.htm

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?

Chris,

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.