Preserve the order of items in table without an ID or primary key

[togo]

This is a CSV file that comes through an Access specification. I need to be able to copy the items from the table to other tables but somehow apply a unique and sequential ID to the rows first, this is essential to the job. The text import spec applies a unique ID but save the spec, and call it using VBA, it doesn't do that.



Also, there seems to be a bug where using a macro to run the text import specification, (and this is after an update from Microsoft), that it doesn't recognize the specs anymore... after I deleted several of them.

Any ideas?

[togo]

I'm going to try straighten out this mess with Access specs, but wow, what a mess to deal with. Poorly designed program

[togo]

this is turning into a nightmare. I have the specs I want, they can be accessed through VBA, but then when I try use a spec through a macro, it tells me the spec does not exist. I've looked at the hidden system tables with spec info in them, changed the primary key to 1 and 2 and Access still doesn't see them

[ssanfu]

I'll give this a shot.. But I'm going to jump around:

Also, there seems to be a bug where using a macro to run the text import specification, (and this is after an update from Microsoft), that it doesn't recognize the specs anymore... after I deleted several of them.

Don't use macros - never have. Too limiting and no error handling. No help there...
-----------

"Preserve the order of items in table without an ID or primary key"

Hmmmm. Don't know if that is possible. A table is just a bit bucket. It has no inherent order. So, 9 out of 10 times you might read a table and get the data in the same order. But the tenth time it could be different. Or eighth time. Or sixth time...
Somehow, you need to have a field that you can look at and say "This record is before or after the current record". If you want "a sequential ID", you have to create it yourself. (not an autonumber type field)
-----------

I've looked at the hidden system tables with spec info in them, changed the primary key to 1 and 2 and Access still doesn't see them

Mucking around with hidden tables is VERY dangerous... Access controls/modifies them. That is why they are hidden. Not a good idea.
-----------

This is a CSV file that comes through an Access specification. I need to be able to copy the items from the table to other tables but somehow apply a unique and sequential ID to the rows first, this is essential to the job. The text import spec applies a unique ID but save the spec, and call it using VBA, it doesn't do that.

Now my questions.

OK, lets say you have a table that is named "ImportedData".
* The 2nd (or 3d or 4th) time you import a CSV field, are there still records from the previous import?
** Are the "sequential IDs" sequential for each import or over all imports?
* Is the "sequential ID" sequential for this import or for all imports (if the previous imported records are not deleted)?
* When you copy the records from the "ImportedData" table, do the "sequential IDs" get copied also?
* How often are the imports?
* How many records in the CSV file? Each time?
-----------

Options:

- If you are careful, you can open the CSV file in Excel and add a "sequential ID" column and re-save it as a CSV (text) file.
- You could edit the CSV file in NotePad+ (or any text editor) and add a "sequential ID" column. (Manually add the ID)
- You could use VBA code...

I use VBA to process and import CSV files. I have more control and error handling that way.
Using VBA, you can read a line from the CSV file, do whatever processing that is necessary, generate and add a "sequential ID" and insert it into a table. If you knew what table(s) a line form the CSV would be written to, you can skip a step (saving to a temp table) and insert it immediately to the required table(s).

[togo]

Could you show me an example of how you use VBA to do that?

The files are in the order they need to be in but there's no ID field in them. An ID has to be added, starting at 1, during/after importation. And this process has to be automated, because that's the whole point of the report generator. Seems pretty retarded that Access can't handle that. This is obviously a bug, I can no longer save/import CSV specs at all. And before I modified the hidden tables (probably have to re-install Access now) it wasn't working properly anyways - if you go through the import wizard it allows the option of Access applying an autonumber primary ID field to the CSV file

if you run a macro that uses that spec, it will do it fine... it wasn't doing the autonumber primary ID field for me at home using VBA code but on a computer here at work it does it. My computer at home has been hot patched, not sure if the one at work has. My code here at work seems to be working though on that computer, but I never touched hidden tables on it either

overall a very confusing situation and enough to make me learn Python in order to avoid having to do much work with Access, which I will never ever recommend to anyone

but I get paid to make it work so any suggestions welcome

[ssanfu]

Would you post an example of the CSV file? (3 - 5 lines) Change any sensitive data first. It would help to know the table name, field names and field types.
What number do you want the "sequential ID" column to start with? Always 1 or whatever the last number was?
Can you post the process you are using/tried to use?

I am using 2 - 3 different import specs in VBA with no problems. It took a little experimentation to get them to work correctly, but haven't had to touch them for over a year.

An ID has to be added, starting at 1, during/after importation. And this process has to be automated, because that's the whole point of the report generator. Seems pretty retarded that Access can't handle that. This is obviously a bug, I can no longer save/import CSV specs at all.

Where did we go off into the report generator??

I am not the biggest fan of M$, but Access is a powerful program; far an above something like Filemaker.
I don't like what has been done to the interface (actually it is extremely bad), but it is am incredible RAD app.

[togo]

I don't see why it matters, but sure. I just deleted the temporary table the CSV file normally gets inputted into, and the spec will no longer work with a recreated table.

DMS-carbon capt,I121000113,Drawing Number,"2024-co2-2001-36""",description,weld,JOB #,332472,Sheet,1 of 2,,,,,,,ND,,ND,,   0.34,   0.06, 0.0820, 0.0168,ND,,ND,, 0.0781, 0.0159,  21.18,   0.13,   0.65,   0.06,  64.75,   0.27,ND,,  12.80,   0.14,ND,,ND,,ND,, 0.0134, 0.0017, 0.1013, 0.0040,ND,,ND,,ND,,ND,,ND,,ND,,ND,,ND,,ND,,ND,,ND,,,

DMS-carbon capt,I121000113,Drawing Number,"2024-co2-2001-36""",description,weld,JOB #,332472,Sheet,1 of 2,FW,7,E308L,1.33711,,0,,,,,,,,,,,ND,,ND,,  22.64,   0.69,ND,,  64.24,   1.38,ND,,  12.99,   0.73,ND,,ND,,ND,,ND,, 0.1401, 0.0249,ND,,ND,,ND,,ND,,ND,,ND,,ND,,ND,,ND,,ND,,ND,,ND,

[togo]

Well, I'm done. After I deleted that table it is repeating the same behavior and refuses to apply a unique ID key to each item on the way in. My boss was expecting this to be done over the next day or two I will be looking at python in order to avoid the hours of headache this program seems to produce on a regular basis

[ssanfu]

Well, OK, good luck..