How can I add an auto number to an imported sheet from excel?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

MaryE - 17 Aug 2005 19:40 GMT

I am importing a spreadsheet from excel into an access database. Once in
access the data in this table needs to have individual numbers(example ticket
numbers that cannot be duplicated). This will be a continuing process, so the
number will need to alway be increasing.

Thank you

Reply to this Message

Klatuu - 17 Aug 2005 21:09 GMT

Best way to do this would be to create a table with the structure you need,
including the autonumber field. Set the data types for each field to match
what will be imorted from each row in the spreadsheet.
Create an append query that will copy the data from the spreadsheet to the
table. I suggest you wrap the Nz function around every column in the
spreadsheet that is numeric. It avoids having a Null come in that will
create a problem. The reason is that if a cell in Excel is formatted as
General and no entry has been made in the cell, Access will see it as a Null.
The way to do that is in the Update To row of the query in the Numeric
Colums:

Expn: Nz([ExcelTableName]![ExcelFieldName],0)
(First Numeric field would be Exp1, next Exp2, etc)

Then, rather than importing the spreadsheet, Link to it.
Run the Append Query.
Delete the Excel Link (Use the DeleteObject method)

> I am importing a spreadsheet from excel into an access database. Once in
> access the data in this table needs to have individual numbers(example ticket
> numbers that cannot be duplicated). This will be a continuing process, so the
> number will need to alway be increasing.
>
> Thank you

Reply to this Message

John Nurick - 17 Aug 2005 21:32 GMT

Import or link the Excel data to a "temporary" table. Then manually
create a new table with the fields you need, including an Autonumber
field. Next use an append query to move the data to the new table: the
autonumber field will populate itself with unique sequential numbers.
Finally delete the temporary table.

If you will be adding more records afterwards, you should be aware that
the Autonumber field can be relied on to give each record a unique
number, but the numbers cannot be relied on to be sequential and in some
circumstances they may be negative. If that's a problem for you, there
are ways of applying sequential numbers as records are created.

>I am importing a spreadsheet from excel into an access database. Once in
>access the data in this table needs to have individual numbers(example ticket
>numbers that cannot be duplicated). This will be a continuing process, so the
>number will need to alway be increasing.
>
>Thank you

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Klatuu - 17 Aug 2005 21:46 GMT

John,
I would appreciate your thoughts on the preference for Linking or Importing.
My preference is to Link. My thought is, although not substantiated by any
testing, is that importing will create more bloat in the database. I also
think it is more time efficient, because data has to be moved only once with
a link.

I noticed you included a temporary table in your solution. Is that
necessary? If you do an append query on a table that has an auto number
field will it not also create new autonumbers for the appended records?

Please understand, this is not a dispute, but a learning exercise for me.
When I see a technique different that one I use, I like to have a comparison
of the techniques.

> Import or link the Excel data to a "temporary" table. Then manually
> create a new table with the fields you need, including an Autonumber
[quoted text clipped - 19 lines]
>
> Please respond in the newgroup and not by email.

Reply to this Message

John Nurick - 18 Aug 2005 20:35 GMT

Hi Klatuu,

Maybe I'm missing something, but your description:

>Create an append query that will copy the data from the spreadsheet to the
>table.

[snip]

>Then, rather than importing the spreadsheet, Link to it.
>Run the Append Query.
>Delete the Excel Link (Use the DeleteObject method)

seems backwards to me. How can you create the query if you haven't
created the linked table from which it gets its data?

So I think we're doing basically the same thing:

Create a temporary table (temporary in the sense that it will
be deleted once the data has been appended to the table where
it's needed). This can be either a linked table or a "real" one.
Use an append query to move the data.
Delete the temporary table

As for linking vs importing, linking is neater in principle, but I've
often had problems because the Excel ISAM uses different rules to assign
field types when linking and when importing. So I tend to try one, and
if that doesn't bring in all the data I try the other, and if that
doesn't work either add apostrophes to text fields or export from Excel
to a text file.

Importing does increase the size of the mdb, but compacting seems to
reduce it again. And with the limit of 64k rows in Excel we're not
dealing with big tables anyway.

>John,
>I would appreciate your thoughts on the preference for Linking or Importing.
[quoted text clipped - 34 lines]
>>
>> Please respond in the newgroup and not by email.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Reply to this Message

Klatuu - 18 Aug 2005 21:00 GMT

I see your point, I guess I left out a step. In reality, I would link the
table, create the query, copy it to VBA code as an SQL statement and go from
there. Because of the data import issues, sometimes it is necessary use the
spreadsheet link as a recordset and use code to edit the data and create
records in the target table by hand. Slower, yes, but IMHO the most trouble
free and accurate.

Where I am working now, they were having terrible problems getting Excel in.
I replaced all their manual imports to the method I described above, and we
have had no problems since then.

Thanks for your opinion.

> Hi Klatuu,
>
[quoted text clipped - 72 lines]
>
> Please respond in the newgroup and not by email.

Reply to this Message