Link

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Chi - 19 Jun 2007 15:10 GMT

Hi,

Would you please show me how I can make a link or connection between Excel
and Access?

For example: I wanted to import or link data form Excel sheet to Access
table and as soon as I enter new records to Access table, the new records
will be updated on Excel also. Is there a way to do that?

Thanks

Chi

Reply to this Message

Mr. B - 19 Jun 2007 17:04 GMT

> Hi,
>
[quoted text clipped - 8 lines]
>
> Chi

Use the File/Get External Data/Link Tables option to create link to
the data in your spreadsheet.

Build a form based on the the linked table.

That's it. You should be able to update data and/or add records to
the data from your Access form. You will not be able to delete a
record unless you write som VBA code to delete the row in Excel.

HTH

Mr B

Reply to this Message

Chi - 19 Jun 2007 17:36 GMT

Hi Mr.B,

Thanks for your help! I created a form based on the linked table. However, I
was unable to do anything, such as update data, add new records.

Please help
Chi

> > Hi,
> >
[quoted text clipped - 21 lines]
>
> Mr B

Reply to this Message

Douglas J. Steele - 19 Jun 2007 17:50 GMT

It's not possible to update linked Excel tables, at least not in Access 2002
or newer. Microsoft was forced to remove the capability due to a law suit
they lost a year or two ago.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Hi Mr.B,
>
[quoted text clipped - 31 lines]
>>
>> Mr B

Reply to this Message

Chi - 19 Jun 2007 18:05 GMT

Thank you for your information.
Chi

> It's not possible to update linked Excel tables, at least not in Access 2002
> or newer. Microsoft was forced to remove the capability due to a law suit
[quoted text clipped - 35 lines]
> >>
> >> Mr B

Reply to this Message

Mr. B - 19 Jun 2007 18:07 GMT

> Hi Mr.B,
>
[quoted text clipped - 31 lines]
>
> - Show quoted text -

A couple of things:

First try opening the linked table and adding a record. If you can
add a record there (which you should be able to), then you should also
be able to add a new record through your form.

I have just now tested this by creating a simple table in excel.
Three column and only about 4 or 5 rows. I then linked this data as a
linked table to a database file. I then created a form using this
table as my record source. I can add and edit records with no
problem. I tested in both continuous form and single form.

There is really no other reason that you should not be able to edit
and/or add records.

HTH

Mr B

Reply to this Message

Douglas J. Steele - 19 Jun 2007 18:18 GMT

> A couple of things:
>
[quoted text clipped - 10 lines]
> There is really no other reason that you should not be able to edit
> and/or add records.

What version of Access are you using? As I mentioned elsewhere in this
thread, yes, there is a very good reason why you can't edit and/or add
records to a linked Excel spreadsheet, assuming Access 2002 or newer: the
capability was removed. See http://support.microsoft.com/kb/904953/

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Reply to this Message

Mr. B - 19 Jun 2007 20:07 GMT

> > Hi Mr.B,
>
[quoted text clipped - 52 lines]
>
> - Show quoted text -

Doug,

I am using Access 2002, sp3 at the moment and I am able to link to
excel data as I described in a previous post.

I'm not trying to disagree with you, but I am able to add and update
records using a form and linked table.

Mr B

Reply to this Message

Douglas J. Steele - 19 Jun 2007 20:13 GMT

> Doug,
>
[quoted text clipped - 3 lines]
> I'm not trying to disagree with you, but I am able to add and update
> records using a form and linked table.

Then obviously you haven't applied the October 18, 2005 Access 2002 update.

See http://support.microsoft.com/kb/904018/

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

Reply to this Message

Mr. B - 19 Jun 2007 22:59 GMT

On Jun 19, 2:13 pm, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

> > Doug,
>
[quoted text clipped - 11 lines]
> Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
> (no e-mails, please!)

Doug,

I am currently on a contract assignment at a client where I do not
have the capibility of applying this myself, but I will check this
out.

I just assumed that sp3 was the latest update.

Oh, well, you know what happens when you assume. :>)

Mr B

Reply to this Message

Douglas J. Steele - 20 Jun 2007 01:47 GMT

> On Jun 19, 2:13 pm, "Douglas J. Steele"
> <NOSPAM_djsteele@NOSPAM_canada.com> wrote:
[quoted text clipped - 19 lines]
>
> Oh, well, you know what happens when you assume. :>)

To be honest, I don't see why you'd want to apply it! <g>

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no private e-mails, please)

Reply to this Message

Mr. B - 20 Jun 2007 15:55 GMT

On Jun 19, 7:47 pm, "Douglas J. Steele"
<NOSPAM_djsteele@NOSPAM_canada.com> wrote:

> > On Jun 19, 2:13 pm, "Douglas J. Steele"
> > <NOSPAM_djsteele@NOSPAM_canada.com> wrote:
[quoted text clipped - 30 lines]
>
> - Show quoted text -

Well, I must admit (now that you bring it up) I really have not
intention of bringing it up.

I just did not realize that my situation was any different that it
should be. When I read the OP I did a quick test and just figured
that it was not big deal. (I still don't think it should be an
issue.) So, lessen learned.

Mr B

Reply to this Message