Generating a primary key

[Access::Student]

So I'm doing something for a client that probably isn't the best way, but the
client insists to have it that way. The client wants to work primarily out of
an excel spreadsheet then periodically export the data to an access database
that includes extra information about the records.

The problem I'm having is transferring them from excel to access since I
obviously need a key to uniquely identify each record. Since the client is
working in excel the primary key has to be generated in excel and be
transfered to access later.

I can't figure out a reliable way to generate an automatic identifier on the
fly in excel. I've found a lot of forums posts with the same problem but
don't seem to completely understand any of the solutions.

http://www.mcgimpsey.com/excel/udfs/sequentialnums.html

I have seen the previous link come up many times but I don't know if it's
the best implementation for my project. The excel spreadsheet is stored on a
shared network drive if the affect the solution to the problem.

Thanks for any help.

 

[Anthony Fontana]

Could you perhaps setup the application in Access first, use the autonumber
field for the key, then periodically use the Access tools menu | Office Links
| Analyze it with MS Office Excel to export the data into Excel.

If its a primary key you want in Access, this might be the best way.

 

[Access::Student]

Thanks for the reply.

I understand that solution but the problem is that the work and generation
of content will take place in excel. It involves tons of numbers and large
formulas that are easier to change and work with in excel. So storing data in
access and exporting it would not be useful as all the formulas and
calculations would be lost in translation.

The access database is really only to store extra information about the
records that can't be stored in the flat excel file. But by transferring the
information from the excel spreadsheet to it it makes everything come
together very nicely.

So all I need is a way to periodically upload or link the excel to access.
But to do either I need some primary key to identify the records, and the key
must be generated in excel because that's were any additions to the data will
take place.

 

[Roger Govier]

Hi

In cell A2 enter a formula like
=IF(B2="","","Key"&TEXT(ROW(),"00000"))
and copy down

Before copying to Access, select the range of cells in column A for the
number of rows to be copied>Copy>Paste Special>Values

 

[Access::Student]

Hey, thanks for the reply.

This solution won't work for me either, since if a new record is inserted
somewhere in the middle of the old ones (which it could), all the key would
be off and the whole purpose of it would be lost.

What I need is a key that has uniqueness enforced and will not change after
it is created.

 

[jaf]

Hi,
Get the best of both worlds.
If the data in Excel is in a named range, the easiest way to make this happen is to open Access and do an import of the data from
Excel.

*** You have the option to import or LINK to the Excel data. ****

You can do the auto number in Access, and every time the Access database is opened, it will automatically update to the linked table
in Excel.

John

 

[Access::Student]

Hello,
I am not sure what you mean by that. When I link an excel table in access I
get an un-editable table. I can't add any fields into the table or add
information. What can I do to add an auto number field and have it push the
result back to excel?

 

[Roger Govier]

Hi

Then a small piece of VBA code to copy the formula down the range, copy and
paste special before exporting to Access should do the job.