Link

[Chi]

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

 

[Mr. B]

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

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

 

[Chi]

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

 

[Douglas J. Steele]

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.

 

[Chi]

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
they lost a year or two ago.

 

[Mr. B]

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







- 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

 

[Douglas J. Steele]

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.

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/

 

[Mr. B]

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- Hide quoted text -

- 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

 

[Douglas J. Steele]

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.

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

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

 

[Mr. B]

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

Seehttp://support.microsoft.com/kb/904018/

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

 

[Douglas J. Steele]

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. :>)

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

 

[Mr. B]

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

--
Doug Steele, Microsoft Access MVPhttp://I.Am/DougSteele
(no private e-mails, please)- Hide quoted text -

- 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