Adding an AutoNumber field to a linked table (1 Viewer)

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:58
Joined
Sep 12, 2006
Messages
15,606
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

Well-known member
Local time
Today, 21:58
Joined
Jan 6, 2022
Messages
1,454
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

Super Moderator
Staff member
Local time
Today, 12:58
Joined
Sep 12, 2006
Messages
15,606
@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.
 

isladogs

MVP / VIP
Local time
Today, 12:58
Joined
Jan 14, 2017
Messages
18,129
I've only skimmed this thread and apologise if I'm repeating earlier comments.
If you import your CSV file into a separate database and link to that, you can easily add an auto number field to the linked table either when it is created or by using DDL on an existing linked table.
The linked table will then be editable whereas a linked CSV file is not.
Will that provide any benefit to you?
 

KitaYama

Well-known member
Local time
Today, 21:58
Joined
Jan 6, 2022
Messages
1,454
If you import your CSV file into a separate database and link to that, you can easily add an auto number field to the linked table either when it is created or by using DDL on an existing linked table.
I don’t have a PC at home, Only tablets.
I didn’t know I can add an auto number to a linked table. We use DSNless linked table to sql server. So I haven’t used the wizard. I’ll check it first thing in the morning.

What is a DDL?

Will that provide any benefit to you?
We don’t need to edit the linked table or csv file. Read only is just fine. But if it gives us a serialNo or an auto number that’s great.

Thanks.
 

isladogs

MVP / VIP
Local time
Today, 12:58
Joined
Jan 14, 2017
Messages
18,129
DDL = data definition language

DDL queries are used to create or edit the structure of tables. More often done in local tables but also possible in linked tables.

However, it may be easier to linked the CSV then import those records to an existing table with an autonumber PK field
 

ebs17

Well-known member
Local time
Today, 13:58
Joined
Feb 7, 2020
Messages
1,745
But if it gives us a serialNo or an auto number that’s great.
 

Martynwheeler

Member
Local time
Today, 12:58
Joined
Jan 19, 2024
Messages
82
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.
Hi,

This looks like a great solution. I have a couple of questions.

In the form unload you have this

Code:
    ' Explizit aufräumen bei mehreren TBE
    ' Verlinkungen
    For Each td In ThisDB.TableDefs
        If Left(td.Name, 5) = "tmpT_" Then ThisDB.TableDefs.Delete td.Name
    Next
    ' Temp-Backends
    Kill Me.txtTempBEPath & "\*.*"

I am not sure what this does in addition to the code in your TempDb class. I have commented this last bit of code out and all links are still removed and the tempdb is deleted. Would you please explain this extra step. Is it necessary?

Thank you once again

Martyn
 

ebs17

Well-known member
Local time
Today, 13:58
Joined
Feb 7, 2020
Messages
1,745
You can design it however it is useful for you.
The code requested expresses indecision or variance in the event that several such backends were created at runtime and had to be removed.
Code:
' Explizit aufräumen bei mehreren TBE
=> Explicitly clean up for multiple TBE
In the majority of cases this will only be a theoretical case.
 

Martynwheeler

Member
Local time
Today, 12:58
Joined
Jan 19, 2024
Messages
82
You can design it however it is useful for you.
The code requested expresses indecision or variance in the event that several such backends were created at runtime and had to be removed.
Code:
' Explizit aufräumen bei mehreren TBE
=> Explicitly clean up for multiple TBE
In the majority of cases this will only be a theoretical case.
Thanks for your help.
So, I don't even need the hidden form. I can just create the tempdb from my main form, use the tempdb for temporary tables and it will be deleted when I close my main form. That is very clever
 

Martynwheeler

Member
Local time
Today, 12:58
Joined
Jan 19, 2024
Messages
82
Thanks for your help.
So, I don't even need the hidden form. I can just create the tempdb from my main form, use the tempdb for temporary tables and it will be deleted when I close my main form. That is very clever
hopefully final question. How do I access the db from another form? This is the first time I have used a second database.
 

ebs17

Well-known member
Local time
Today, 13:58
Joined
Feb 7, 2020
Messages
1,745
In the code, TBE is the object variable on the class.
The class then has public properties itself:
Code:
With TBE
   Debug.Print .FullPath
   Debug.Print .Path
   Debug.Print .Name
End With

You can get the DAO reference for creating a recordset, for example
Code:
TBE.TmpDB

The methods used in the class (Execute, Exists, Link, Drop) already use the necessary DAO reference, no further information is necessary.
Code:
TBE.Execute sSQL, dbFailOnError
TBE.LinkTable "tmpT_Gruppe"
' and so on
 

Martynwheeler

Member
Local time
Today, 12:58
Joined
Jan 19, 2024
Messages
82
In the code, TBE is the object variable on the class.
The class then has public properties itself:
Code:
With TBE
   Debug.Print .FullPath
   Debug.Print .Path
   Debug.Print .Name
End With

You can get the DAO reference for creating a recordset, for example
Code:
TBE.TmpDB

The methods used in the class (Execute, Exists, Link, Drop) already use the necessary DAO reference, no further information is necessary.
Code:
TBE.Execute sSQL, dbFailOnError
TBE.LinkTable "tmpT_Gruppe"
' and so on
Thanks for your explanation.

The point I am not sure about is if that the TBE object is instantiated in one form (your hidden form in the example) from the class, how do I access this object from another form? Do I have to use a global variable?

I realise that I can get the path to the TBE created but I can't see how to use the reference to the object so I can use TBE.execute on another form. Sorry if I am missing something obvious?

Thank you again for your help.
 

ebs17

Well-known member
Local time
Today, 13:58
Joined
Feb 7, 2020
Messages
1,745
The idea of frmHidden is that this form is opened immediately when the application is opened and is only closed when the application is closed. As a result, this TBE object will always exist at runtime of the application.
If you use other forms with different terms, you will make your life unnecessarily more difficult. The great idea turns into a bad one.

Code:
' in modDBReferences
Public TBE As clsTempDB
The class has already been publicly declared under the name TBE and is therefore known and accessible across the application.

There is another unnecessary declaration in the form code.
Code:
Dim TBE As clsTempDB
This is unnecessary and can be removed or commented out. I don't remember why I added this and then left it. Surprisingly, this double declaration does not trigger an error.
 

ebs17

Well-known member
Local time
Today, 13:58
Joined
Feb 7, 2020
Messages
1,745
I am not sure about is if that the TBE object is instantiated
Once you have defined the name of the object variable to be used (TBE), you can of course always check:
Code:
If TBE Is Nothing Then ' instantiate TBE
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:58
Joined
Feb 19, 2002
Messages
42,724
So, I don't even need the hidden form. I can just create the tempdb from my main form, use the tempdb for temporary tables and it will be deleted when I close my main form. That is very clever
I would do the delete at the beginning of the process rather than as the form closes. It eliminates a problem should the file not be deleted for some reason and it makes testing easier if you can let the process work and see what ended up in the table.
 

ebs17

Well-known member
Local time
Today, 13:58
Joined
Feb 7, 2020
Messages
1,745
@Pat Hartman
I like this pragmatic approach. However, he violates the stylistic principle of cleaning up your stuff straight away.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:58
Joined
Feb 19, 2002
Messages
42,724
It isn't wrong to clean up after yourself. But in this case, I don't do it. I have been quite happy to be able to actually look at the database after the fact. Just boots on the ground experience.
 

Martynwheeler

Member
Local time
Today, 12:58
Joined
Jan 19, 2024
Messages
82
Once you have defined the name of the object variable to be used (TBE), you can of course always check:
Code:
If TBE Is Nothing Then ' instantiate TBE

Once you have defined the name of the object variable to be used (TBE), you can of course always check:
Code:
If TBE Is Nothing Then ' instantiate TBE
Thanks again for your help with this. I now have all my temporary tables in the tempdb.

The only thing I have noticed is, while testing, if encounter any sort of error the TBE object is lost. Is this normal?

I guess that I could do as you suggested and always check the TBE object is not nothing before accessing it. I was just wondering why the object died if I hit an error.

Anyway, thank you again
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:58
Joined
Feb 19, 2002
Messages
42,724
I guess that I could do as you suggested and always check the TBE object is not nothing before accessing it. I was just wondering why the object died if I hit an error.
One of the problems with global variables is that they lose their value when the application encounters an unhandled error. Fix your code to trap errors better.

I'm not sure why you need this particular object once the tables are linked. I didn't look closely at whatever code you are using. I use a "side" BE quite regularly and it doesn't require an object of any kind.

Here's the sample if you need it.

 

Users who are viewing this thread

Top Bottom