Adding an AutoNumber field to a linked table

[KitaYama]

When adding a linked table to a csv file, is it possible to add an AutoNumber field to be used as a PK while working with the records?
I can't see any option for adding a PK to the linked table in the wizard?

Thanks.

 

[ebs17]

A primary key including an unique index is a property in a real database table. You cannot create this on a linked table, and certainly not on a text file.

There are two ways:
- A continuous number is generated in a query on the linked table. This gives you an identification feature.
- You import into a temporary table. In this table you can create additional fields and set your own planned indexes.

 

[KitaYama]

A continuous number is generated in a query on the linked table. This gives you an identification feature.

Brilliant. Never thought of this.

 

[KitaYama]

I wasn't able to add the continous number on my own. The csv file hasn't any column to be able to use it for Dcount in the query.
The following is a mockup of the table.

Any further assist is much appreciated.

ParentNo	LineNo	DrawingName	DrawingNo	QuantityPerAssembly
11287BD701A01	10​	test	11287BD007	1​
11287BD701A01	110​	test	11012BD101	1​
11287BD701A01	130​	test	11092BC104	1​
11287BD701A01	150​	test	11287BD101	3​
11287BD701A01	160​	test	AJ3321BC112	2​
11287BD701A01	180​	test	3412BD211	1​
11287BD701A01	210​	test	1287BD102	1​
11287BD701A01	220​	test	AJ226SR105	1​
11287BD101	110​	test	3266BF2	1​
11287BD101	120​	test	3266BF202	1​
11287BD101	140​	test	3266BF203	1​
11287BD101	160​	test	SF1037TR108	5​
11287BD101	170​	test	1037CK243	15​
11287BD101	210​	Jtest	1037DP297	0​
11287BD701	10​	test	11287BD008	1​
11287BD701	110​	test	AJ11287BD801	1​
11287BD701	120​	test	11287BD802	1​
11287BD701	150​	test	AJ11287BD203	1​

 

[gemma-the-husky]

You would have to add a sequential number column to the CSV file, but then you need a way to retain the sequence number between iterations of the CSV file (I presume), so why is it a CSV file rather than a relational table in the first place?

If not a sequential number, then you need a set of columns that provide a unique key.

Eg [parent no] + [line no]

 

[KitaYama]

so why is it a CSV file rather than a relational table in the first place?

The file is sent via a web base order system by some of our customers. We login to our account and the only format available for downloading new orders is a csv file.

 

[gemma-the-husky]

The file is sent via a web base order system by some of our customers. We login to our account and the only format available for downloading new orders is a csv file.

So simply [parentno] + [lineno] seem to give the same, or in fact better functionality than a mere numberic identifier. (As I edited my post to note)

You could preprocess any file to append a sequential number to each row. You could start at 1 each time, or continue with an existing sequence. That depends on what exactly you need to do with your data.

 

[KitaYama]

So simply [parentno] + [lineno] seem to give the same,

ParentNo+lineno is not unique. There's a good chance for having the same combination in the same csv.

You could preprocess any file to append a sequential number to each row.

Sorry, I can't understand what you mean by this.
Do you mean opening the file with Excel and adding a column and fill it manually?


Maybe the shortest way is to use a sub report instead of a sub form. (the linked table is used as a sub form)
I hope it's not the only possible solution.

 

[ebs17]

SELECT
   DCount("*", "TableX", "ParentNo & '|' & LineNo <= '" & T.ParentNo & "|" & T.LineNo & "'") + 1 AS XNo
   [, FieldList]

FROM
   TableX AS T

Join multiple fields for an expression that is unique.
However, this field combination could also serve as a multi-field key on its own, as individual fields without a delimiter.

Because one thing has to be said: the generation of a sequential number shown is very resource-consuming, which is always the case with correlating subqueries due to the system. The subsequent use of this number as a key very quickly becomes performance critical.

Therefore, for more complex tasks and larger amounts of data, I quickly tend to use temporary tables where you can fix such intermediate states and have complete freedom in terms of design. Such temporary tables are then placed neither in the frontend nor in the backend, but in an additional temporary local backend file. This way you avoid bloat problems in your own important DB files.

 

[KitaYama]

SELECT
   DCount("*", "TableX", "ParentNo & '|' & LineNo <= '" & T.ParentNo & "|" & T.LineNo & "'") + 1 AS XNo
   [, FieldList]

FROM
   TableX AS T

Join multiple fields for an expression that is unique.
However, this field combination could also serve as a multi-field key on its own, as individual fields without a delimiter.

Because one thing has to be said: the generation of a sequential number shown is very resource-consuming, which is always the case with correlating subqueries due to the system. The subsequent use of this number as a key very quickly becomes performance critical.

Therefore, for more complex tasks and larger amounts of data, I quickly tend to use temporary tables where you can fix such intermediate states and have complete freedom in terms of design. Such temporary tables are then placed neither in the frontend nor in the backend, but in an additional temporary local backend file. This way you avoid bloat problems in your own important DB files.

The main reason I try to stay away from temp tables, is frequent delete an append ends up to bloat.
I'll try the given solution tomorrow as soon as I'm back to my desk. If the performance was critical, I will use a temp table in another database.

Million thanks for the advice and solution.

 

[ebs17]

Fear of temporary tables: They also exist in SQL Server, in an additional file. There they are automatically managed and removed again. My little solution is just an approach to imitation.
Attached is my standard solution for dealing with temporary tables. The interface is in German, but you can understand the process from the codes alone.

Principle:
When you open the frontend, a frmHidden is opened. This is used to create the additional backend. The form is there for closing and scheduling the backend because there is no file close event in Access.
The additional temporary backend has its own 2 GB of space for temporary garbage.
Closing the application will delete the entire file.

 

[CJ_London]

Rather than using a temp table (presumably populated using transfertext), use sql to create a query.

SELECT *
FROM
     (SELECT  ParentNo & '|' & LineNo as XNo, * FROM [TEXT;DATABASE=X:\pathtofile;HDR=Yes].filename.csv)  T

Edit: clearly there is no indexing so might be slow for very large datasets but the above can easily be modified to compare with existing tables, be filtered at the outset, converted to append or update (or update/append) queries

 

[ebs17]

An additional problem exists with linked text files, regardless of the form in which the link is made:
If you only use append queries based on this table, you won't have any problems. However, if you also use update queries, a bug related to an ISAM error often occurs. At least that was the case in the past. Then, from a purely technical perspective, you have to switch from linking to importing and thus automatically have a temporary table.

 

[KitaYama]

If you only use append queries based on this table, you won't have any problems. However, if you also use update queries, a bug related to an ISAM error often occurs.

No update is necessary. Only appending.
Thanks for the additional info and your sample file in #11.

 

[ebs17]

Why do you need an autonumber field for a simple append? Such a field is automatically incremented in the target table.

 

[KitaYama]

What is the purpose of adding a uniqueID to this table?

Why do you need an autonumber field for a simple append?

It's not a simple append. Before appending each record of the csv to tblOrders, there are a lot of calculation that should be done: The simplest one is the amount of necessary Materials, manufacturing, inspecting, shipping time study, and above all changing the current production line schedule to include new orders.
The process that should be done on this linked table, may take a long time, in some cases maybe more than a day or two.
So if for some reason there's an interrupt in the process, or the user has to stop in the middle and attend some other jobs, Without having a unique ID, it's almost impossible to recognize how many of the records have been processed and from where to continue.
I also need this unique id for some specific filtering (for example showing only every 5 record : WHERE UniqueID MOD 5 = 0

There are some other usage that I think is out of the scope of this discussion.

Thanks for your concern.

 

[ebs17]

The process that should be done on this linked table, may take a long time

If this process is, or can be represented as, a SQL query process, that would be an argument for importing into a temporary table. A big advantage is that you can specifically index fields or field combinations, which can give performance a significant boost. Secondly: If you go into the temporary tables area, you also have the option of fixing calculation results as a table and indexing their fields in turn. Anything to reduce overall processing time.

may take a long time, in some cases maybe more than a day or two

Such times for pure processing would be out of the question for me and would trigger a need for action.
When I think of processing times, I think of minutes and fractions of them, but never hours or days.
The risk of a break in the middle of processing is also a huge problem in itself. More complex things in particular should be carried out as part of a transaction.

 

[gemma-the-husky]

ParentNo+lineno is not unique. There's a good chance for having the same combination in the same csv.


Sorry, I can't understand what you mean by this.
Do you mean opening the file with Excel and adding a column and fill it manually?


Maybe the shortest way is to use a sub report instead of a sub form. (the linked table is used as a sub form)
I hope it's not the only possible solution.

I meant.

A csv file is a text file. Simply read in the rows a line at a time and add a ", n" at the end, where n is an incrementing counter. Now you have a new column with a sequential record number.

 

[KitaYama]

Such times for pure processing would be out of the question for me and would trigger a need for action.
When I think of processing times, I think of minutes and fractions of them, but never hours or days.

It seems I've made another confusion here. Mostly because of the stupidity of Google Translate and my limited English vocabulary.

The process time that I was talking about, is not the execution time of a query or anything like that.
Before we accept an order and save it as a new record in our table, the user has to go through a lot of steps and if all passed, then an order is accepted. Checking current amount of materials, manufacturing line schedule, asking for outsourced parts and tools' quotes , .........

A csv file is a text file. Simply read in the rows a line at a time and add a ", n" at the end,

That was the whole purpose of this thread. We were trying to find a way not to read the csv file line by line, but use it as a linked table.
I hoped there may be a simple way just like adding a serialNo to a report.


At present we are testing the solution offered in #7 , #9 and using a listbox with a rowsource to the linked table.

Thanks to all for the help.

 

[gemma-the-husky]

@KitaYama

Well, it would only take a few moments to add a numeric field, unless there's hundreds of thousands of rows.

If there's that many rows, I wouldn't really want to try and read them all into a combo box or list box. It may be OK. I don't think I've ever tried to use a csv or excel file as a table/data source directly.

What you need to do also depends on whether each csv file is processed in isolation, or whether successive csv files need to be combined, and therefore whether records encountered in a previous file are reimported, possibly with changed data in subsequent files.

You could get users to manually edit the input file and add a column called Sequential or RecordID, and populate it for all data rows. Then when you import the table test to see if that column exists, and if not reject the import. You can do other validations as well. Force the users to make sure the data is clean and useable. I often do that sort of thing. It's better than finding problems later on.

Note that manipulating csv files in excel might change some of the data, so you may need to consider that possibility.

Note also that if you do import a csv file and there is no reliable way of sorting the data into the original order (unique key, in other words), then depending on the process you use to import the file you can't be sure the data in the table will be in the same order as the data in csv file. (I think that's right)

If every file should have the same structure, the most reliable way might be to read the csv files in a row at a time, split them into fields, add an index value, and append the row to the temporary table. Now you have an access table that you can manipulate as you will. Insert "processed" columns to record what happens to the row subsequently. If docmd.transfertext will get to you a satisfactory check point in this process without having to do it all in code it will save you time. but that clearly depends on you having a way to reconstruct the original row order, if the row order is significant. I've done stuff like this. You only have to do develop it once.

I hope this is all not too confusing. You know how you need to use your data after all.