Importing records from two access files into one SQL table

[9944pdx]

My issue, I created one file it worked fine, but the users want a second file. That is easy - copy the file and rename. In the table I created ID FIELD NAME as DATA TYPE "AutoNumber" generate. I received an error because by an auto generated field had already been used. How should I create this field for two files? Should I use DATA TYPE "Number"? But how far do I space the numbers apart, I still fear that a number will be used. SQL Field Number (10,0).




Any suggestions?

[9944pdx]

Update: I found that Data Type "AutoNumber" has a format option to add a prefix to autogenerated number, how should I do this for two files?

[original post]

My issue, I created one file it worked fine, but the users want a second file. That is easy - copy the file and rename. In the table I created ID FIELD NAME as DATA TYPE "AutoNumber" generate. I received an error because by an auto generated field had already been used. How should I create this field for two files? Should I use DATA TYPE "Number"? But how far do I space the numbers apart, I still fear that a number will be used. SQL Field Number (10,0).

[June7]

That's just display, doesn't change the value saved. So yes, will still have conflicts. Can't use autonumber as primary key for importing data from multiple sources if that key is used as foreign key in related tables, at least not without a bunch of code to manage - which I have done.

[9944pdx]

This table has no primary key, and no other tables.
I am pulling data into the .db, I run a macro to format the records, and then export the file via excel to a network folder. I created a script to pick up the excel file from network location, and it is pulled into SQL table. I can still use 2 files.

[June7]

Then what is the autonumber field needed for?

[9944pdx]

The autonumber is used to create a unique number if each record.

[June7]

If each source table has autonumber then there will be conflict when imported to another table. Unless you set one of the autonumber fields to begin at a number the other will never reach. Or abandon the autonumber fields and create the unique number in the SQL table.

Autonumber is not meant to have meaning to users.

Why do users feel two files are needed?

[9944pdx]

Excellent question, we have an influx of parts coming soon, and the thought is - users won't want to wait for someone else to use the access file (current time using .db is less than 2 minutes to do the import/export action). I used to be one of those "users" and to be honest, waiting for another just takes coordination.
The Project Manager is demanding another file be created.

I hear what you are saying regarding using the autonumber for one and using another number high enough that is wouldn't catch up. (maybe by then SAP will be implemented)

[June7]

If you want a multi-user application then the db should be split. Data tables would be in the backend. Frontend links to backend and is user interface with queries/forms/reports/code. Each user has copy of frontend. Nobody waits for anyone.

Not really understanding business flow. If you have SQL tables as final destination for data, why Access tables? The backend could be the SQL (which SQL platform are you using?).

[9944pdx]

We have thought about splitting the .db - but thought is that it would be difficult.

My project: Import technical data into company manufacturing system.
In order to do that the data has to be reformatted and fields populated which is why I created .db to reformat and populate empty fields.

1. Export characteristics technical data package into and excel sheet.
2. Import excel data into the .db file.
3. A macro in the .db will run to format and populate fields required in the SQL table.
4. Export data back into an Excel file
5. Script will run from server to import data from excel file into company manufacturing system.

[June7]

Then I advise not to build second db if you really want autonumber. Why extra step of export to Excel? Why can't script pull from Access? Or Access link to SQL?

[9944pdx]

The server administrator preferred it this way. The company split up jobs to different departments, I don't have a visual on the back end tables and connections.
It takes a lot of time and effort to do my job, I hear it is changing slowly.

I will split as you suggested.