Linking Excel as a table

[Guest]

Hi I am linking a spreadsheet into access to give me a table, I then want to
build a form off that table, but I need an autonumber field to count records
and assign an ID to the records, access will not let me change the properties
in the linked table, any ideas what i should do

 

[John Vinson]

Hi I am linking a spreadsheet into access to give me a table, I then want to
build a form off that table, but I need an autonumber field to count records
and assign an ID to the records, access will not let me change the properties
in the linked table, any ideas what i should do

Since Excel does not have autonumbers, and since you cannot update a
linked Excel spreadsheet in any case, You Can't Do It This Way.

If your purpose is to count records, and Autonumber is the wrong tool
in any case! You can put a textbox in your Form's Footer, with a
control source

=Count(*)

to count the number of records in the form's recordsource; would that
get at least part of your problem resolved?

John W. Vinson[MVP]

 

[Guest]

Thanks John, my question was a bit misleading, I don't need acces to count
the records, I need a unique ID number to be automatically generated and
allocated to each record (row), so as a new record is added to the axcel
sheet, the table is updated and an ID number allocated in access to that
record, does this make a difference.

 

[John Vinson]

Thanks John, my question was a bit misleading, I don't need acces to count
the records, I need a unique ID number to be automatically generated and
allocated to each record (row), so as a new record is added to the axcel
sheet, the table is updated and an ID number allocated in access to that
record, does this make a difference.

Yes, it does.

You *CANNOT* do this.

Microsoft lost a lawsuit, and had to remove the functionality which
allows Excel spreadsheets to be updated from Access. If you have
current versions of Access with current service packs, then what you
ask to be done *cannot be done*.

You would need to import the Excel data into an Access table (perhaps
using an append query into a table containing an autonumber), and then
export that table out to Excel; you cannot update a linked Excel
spreadsheet. Sorry!

John W. Vinson[MVP]