Adding an AutoNumber field to a linked table

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

 

[isladogs]

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]

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]

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]

But if it gives us a serialNo or an auto number that’s great.

Open and update Excel Workbook and saveas Question

I believe above the second step is taken before the first step. I heard the statement that you want to SEE the data. To do this, you would link the text file as a table in the frontend: DoCmd.TransferText acLinkDelim, ... In many cases I prefer to make this link directly in a query, which has...

www.access-programmers.co.uk

 

[Martynwheeler]

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

    ' 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]

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.

' 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]

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.

' 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]

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]

In the code, TBE is the object variable on the class.
The class then has public properties itself:

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

TBE.TmpDB

The methods used in the class (Execute, Exists, Link, Drop) already use the necessary DAO reference, no further information is necessary.

TBE.Execute sSQL, dbFailOnError
TBE.LinkTable "tmpT_Gruppe"
' and so on

 

[Martynwheeler]

In the code, TBE is the object variable on the class.
The class then has public properties itself:

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

TBE.TmpDB

The methods used in the class (Execute, Exists, Link, Drop) already use the necessary DAO reference, no further information is necessary.

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]

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.

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

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]

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:

If TBE Is Nothing Then ' instantiate TBE

 

[Pat Hartman]

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]

@Pat Hartman
I like this pragmatic approach. However, he violates the stylistic principle of cleaning up your stuff straight away.

 

[Pat Hartman]

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]

Once you have defined the name of the object variable to be used (TBE), you can of course always check:

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:

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]

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.

Copy Template

This sample gives you a framework for using a "side" BE. Sometimes we have temporary data. We may need to import it every day because the PTB won't let us link to it or maybe we create it in the course of some type of processing. In any event. This code shows how to just copy the empty...

www.access-programmers.co.uk