Autonumber field, fill with data while importing

Posted on 2006-07-21
Medium Priority
Last Modified: 2008-03-04

I have  an access database. I need to import date from another source which includes a id field. This id field in the access database is set to be an autonumber. I can not seem to find a way to import in such a way that I can set this field while importing, and have it autonumber afterwards.

I only need to do this once, at the very start of using the database.

I have tried setting the field to numeric and then after the import change it to autonumber but that won't work

I'm used to working with SQL Server, there you can play with the 'identity insert' option to do this.

Any thoughts?
Question by:palloquin
  • 5
  • 4
  • 2
  • +1
LVL 66

Expert Comment

by:Jim Horn
ID: 17154133
>This id field in the access database is set to be an autonumber. I can not seem to find a way to import in such a way that I can set this field while importing, and have it autonumber afterwards.

You'll need to import to all fields EXCEPT FOR the AutoNumber column.  The AutoNumber column will populate automatically when the new records are inserted.

Hope this helps.
LVL 16

Expert Comment

by:Chuck Wood
ID: 17154366
If you really need the AutoNumber number, you can import it into a Number-Long Integer field, if it is a Long Integer AutoNumber, but you can't change THAT field to an AutoNumber field.


Author Comment

ID: 17154369
That is not an option. The id field is refered to in other tables to be imported and also in external systems that expect items to still have the same id...

I NEED to fill the id field with the field in the import. And I NEED to somehow get it to be  an autonumber after...
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

LVL 66

Expert Comment

by:Jim Horn
ID: 17154385
>I NEED to fill the id field with the field in the import. And I NEED to somehow get it to be  an autonumber after...
What you are asking is not possible in Access.  You can NOT intentially insert a value into an AutoNumber-formatted column.
There is also no work-around that I am aware of.
LVL 16

Assisted Solution

by:Chuck Wood
Chuck Wood earned 400 total points
ID: 17154453
You might be able to write code that implements this information from the Access 2000 Help:

Change the starting value of an incrementing AutoNumber field
For a new table that contains no records, you can change the starting value of an AutoNumber field that has its NewValues property set to Increment to a number other than 1. For a table that contains records, you can also use this procedure to change the next value assigned in an AutoNumber field to a new number.

1. Create a temporary table with just one field, a Number field; set its FieldSize property to Long Integer and give it the same name as the AutoNumber field in the table whose value you want to change.

2. In Datasheet view, enter a value in the Number field of the temporary table that is 1 less than the starting value you want for the AutoNumber field. For example, if you want the AutoNumber field to start at 100, enter 99 in the Number field.

3. Create and run an append query to append the temporary table to the table whose AutoNumber value you want to change.

Note   If your original table has a primary key, you must temporarily remove the primary key before running the append query. Also, if your original table contains fields that have the Required property set to Yes, the Indexed property set to Yes (No Duplicates), or field and/or record ValidationRule property settings that prevent Null entries in fields, you must temporarily disable these settings.

4. Delete the temporary table.

5. Delete the record added by the append query.

6. If you had to disable property settings in step 3, return them to their original settings.
When you enter a record in the remaining table, Microsoft Access uses an AutoNumber field value 1 greater than the value you entered in the temporary table.

Note   If you want to compact the database after changing the starting AutoNumber value, make sure to add at least one record to the table first. If you don't, when you compact the database, the AutoNumber value for the next record added will be reset to 1 more than the highest previous value. For example, if there were no records in the table when you reset the starting value, compacting would set the AutoNumber value for the next record added to 1; if there were records in the table when you reset the starting value and the highest previous value was 50, compacting would set the AutoNumber value for the next record added to 51.
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17155024
I may not have read the question or responses properly - but would importing to a temporary table and then just appending from that straight into the target table from there be all you need.
Assuming there are no PK violations (i.e. already matching rows existing in the target table's PK) then the values should append (including the autonumbers as specified).

Or is there something else I've not spotted?

Author Comment

ID: 17159422
cwood-wm-com: Damn that sounds complicated.... My head hurts while trying to understand WHY this would work... I found the article in the Access Help though :)

LPurvis: Yes you missed something. I need to import data into a table AND keep the autonumbered ID field exactly that, but fill it with id's in another table.

I might have thougt of a solution myself...

what if.. I would start with an empty table, and insert 'blank' records up to the max ID in my source. Than I could delete all records with ID's not in the source table. After that I could update the blank records instead of inserting them. It's a hassle but I think it might work.

Gonna try this. If it doesn't work I'm going to try and understand  cwood-wm-com's / ms Access help's solution :)

Any reactions?

I'll award point to cwood-wm-com since it sounds workable, but try my own first :)
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17159593
OK - I've re-read the question and your comments throughout.

It wounds to me as if you have data in an external source.
That source has an ID field.
Other external sources refer to those records - through the ID field - hence it needs to be preserved on import.
After that point - the Access table into which the import has occurred needs to continue on from that point with it's sequential autonumbering.

That seems to agree with your statement
"I need to import data into a table AND keep the autonumbered ID field exactly that, "

But I don't follow what you mean by
"but fill it with id's in another table."

Do you mean keep it an autonumber - but fill with your own choice of IDs?

I'm not clear on what I'm missing.

Author Comment

ID: 17159649
-- That seems to agree with your statement
-- "I need to import data into a table AND keep the autonumbered ID field exactly that, "

Up to there you understand perfectly!

-- But I don't follow what you mean by
-- "but fill it with id's in another table."

By this I just mean that during the import I need to preserve the ID field. poorly phrased perhaps...

Do I understand correctly that an 'append' query will allow me to 'insert' rows in to a table with an autonumber field. And that through this query it will accept the values in the id field from the (temp) source table as the values for the (autonumbered) id field in the destination table?

if so do you have a small SQL example of what this query would look like?

Thank You!
LVL 44

Accepted Solution

Leigh Purvis earned 800 total points
ID: 17159667
Yep - that's exactly what I've been getting at all along.

Autonumbers are *not* updateable (you can't change them once they're entered) and cannot be specified through the UI.

But you can append to them with the value you want at will.
(As long as that value isn't already present in an existing record of course - but you say the table will be empty, so no probs).

INSERT INTO tblRealTable (AutoPK, Field1, OtherField)
  SELECT ImportedID, MyField1, OtherField
  FROM tblTempImports

It's one of the ways you can specify an autonumber to begin at a defined number.
Just do an Append to it with that high number - delete the row and the autonumber will carry on from there.

Author Comment

ID: 17162722
I never would have guessed that would work...
just proves again (to me, and i apologise if i hurt anybodies feelings) what a crappy and inconsistent database access is!

I'll split some points around. Thank you all for helping..
LVL 44

Expert Comment

by:Leigh Purvis
ID: 17162806
Well - I'm possibly known as likely to respond to such opinions - but am not offended by it (have heard it a few times now, and it's still as wrong as ever - no offense ;-)

Just a couple of thoughts for you which hopefully you might find useful.
Access isn't a database.  Access is a front end RAD tool designed completely to work with databases.  JET is the database engine about which you're complaining - and JET is a Windows component.  (You wouldn't believe how much software uses it).
When used well - JETis extremely good at what it does, well optimized, capable of very good performance.

It has limitations of course.
I love SQL Server - and happily acknowledge it's wider range of functionality and power.  (I'm obviously not the only Access 'bod' to think so - if you look in Jim's profile you'll see a cert for SQL Server there too).
But it's entirely a question of scope.  They each do very well what they were intended to do.

"Access" (JET) doesn't support a great many concurrent users or very large databases.
It's processing is done on the client.  It uses a slightly less standard version of SQL.
Access as a FE to SQL Server can harness the functionality it then offers (again - if used correctly).
When it's using JET properly - it is also very good at using *its* strengths.

The ability which you sought (to append specified autonumber entries) would have been something MS realized might be required.  It makes sense - for examples like your exact situation.
(Equally not being able to edit existing autonumbers also makes perfect sense, but may make it seem as if they're not at all flexible).
OK - they didn't implement it upon importing from an external file.
That'll likely be a discontinuity between Access and JET (they're not the same product remember).  

But the solution is still available.
For what it's worth - I very rarely import directly into a target table.
An import table to look at the data (with all the tools at my disposal to do so once it's in a local table) before appending live exerts a far greater control of the process.  (All done through code naturally - users don't fanny about with any of this).

I'm just a bit confused as to why, having found that Access is actually capable of doing as you needed - that this helped re-enforce your conculsion that it's crap.
But you're welcome - none the less :-D

Author Comment

ID: 17163358
Once a long time ago, when i started programming Access was my db. But after having worked with SQL Server (and oracle & mySQL for that matter), I have learned that all the 'peculiarities' of access are not a implicit fact of life when working with relational database. The semantics of which part is 'access' and wich part is 'JET' are not that interesting, since working with access means working with JET in most cases.

Having said that, and read your opinions, I have to agree that access I a tool with it's own uses. I *love* to use access as a front-end to a SQL Server database (adp project). It gives my all the power SQL server offers (proper implementation of the SQL language, stored procedures, sheer power, UDF, etc), and combines that with the RAD environment Access offers with all it's form & reporting tools.

again, thank you for the trouble.

Featured Post

Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
When we develop an application in Ms Access 2016 we should also try to protect the queries, macros and table links. I know I may not have a permanent solution but for novice users, they will not manage to break your application. Below is the detail …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

597 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question