Adding an AutoNumber field to a linked table

[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

 

[ebs17]

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

 

[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

 

[ebs17]

if encounter any sort of error the TBE object is lost

Hopefully errors are not the norm.
- The first measure and stylistic device used should be to work without errors. Most of the reasons for possible errors are known, so in such situations you can first check to avoid the situation, take other paths or use substitute values for missing values.
As an example: I like to point out that for me index errors are also errors. So I would very rarely fire duplicates to a unique index when doing append queries and imports.
- Unforeseeable errors and the rest can be dealt with using your existing error handling.
- Anyone who uses error handling to control the program and continues happily despite experiencing errors in the program should know what they are doing.
So, hopefully errors are not the norm.

Technically:
What does it mean when the TBE object is lost?
The real question is then whether the side BE has also disappeared as a file. If not, you can find it and renew the reference to it (GetObject). If the file disappears, all information disappears with it. You can then set up a new object again, but the previous program flow will probably not be easy to continue because of the loss. So if you have to start over, the error that caused it must be eliminated and eliminated. Self-healing object variables won't help either.

Since the proposed solution is mine: No, I have not tested or even ruled out every possible error in this solution that beginners and unintentional people could find. It's not "foolproof". But my way of working, which is based on what was described above, is sufficient.

Another consideration may be: What do you use temporary tables for and how long does each one take? So do I use a temporary backend for the entire runtime of the application? This will usually be sufficient by default.
Or, because you have mastered the creation of the temporary backend, do you only use it for certain tasks depending on the situation and then use a new one for other tasks? So by destroying the object yourself, would you prevent the loss due to errors?

 

[Martynwheeler]

I quite agree that errors should not be the norm. They were just cropping up while I was learning and testing your code. I will make sure that they are handled properly in the finished version

 

[Martynwheeler]

I am interested in your statement:
"If not, you can find it and renew the reference to it (GetObject)".
I have the filename saved, can it be assigned to the TBE object? I am struggling to achieve this
Set TBE = GetObject(Forms("frmHidden").txtTempBEFullPath.Value, "Access.Application")
throws an error of type mismatch.

It really should not throw an error as I have checked the code but it would be good to handle extreme cases.

Thanks

 

[ebs17]

I have the filename saved, can it be assigned to the TBE object?

A lost object (class is terminated)?

So you should re-instantiate the class, with the option of being able to use an existing backend in addition to creating a new backend and controlling this option from the outside.

Forms("frmHidden").txtTempBEFullPath.Value

Something like that doesn't work at all. Information is passed to a class from outside via designated interfaces.

GetObject was probably a misguided and ill-conceived idea. The fact that the temporary backend is not actually opened (using an application object) is out of the question. You probably need an OpenDatabase similar to the procedure in Init.

But I won't bother with the code because it completely contradicts the original purpose of the solution.
If you want to re-integrate existing backends - that's a different case and there are a number of solutions.