Autonumber field, fill with data while importing

>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.
-Jim

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.

-Chuck

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

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

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?

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

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.

-- 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!

DUH!
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..

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

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.