datatype Decimal in temporary table. (1 Viewer)

[Bob]

Hi,
I create a temporary table on the fly using DAO. A couple of fields needs to be of type dbDecimal. The <Field> object does not provide the Precision and Scale properties, but <Field3> does. I struggle to use <Field3> and gain access to these to properties, perhaps my references are not correct. Copilot was not much help, so now I ask some real people.

Thanks in advance

 

[Pat Hartman]

If the tables are always the same, a simple method is to create a separate BE for the temp tables. Define the tables. Then copy the template BE to your working directory and overlay the previous copy each time you want to run the "temp" process again. This method also avoids the bloat issue you end up with if you create temp tables in the production FE or BE.

 

[Bob]

Thanks. It is exactly the bloat issue I want to avoid.
What do you mean by "overlay the previous copy"

 

[The_Doc_Man]

If you have a "side-end" table - a file that looks like a back-end but in which the tables are defined but 1empty - and you make a copy of it in the area where you need it, then link to the "side end" tables, you are set. Now the next time you launch this, if you use a batch launcher you can just re-copy the unpopulated version of the file to where you were using ing - THEN launch the app. You see, Access links by NAME, not by some internal file identifier - even though such a thing exists. So you delete the OLD copy of the side-end table, replace with a new "virgin" copy, and presto - all bloat has vanished.

Note that even if you want to start with some things in the side-end tables - but those things are always the same, make a copy of the file as you want it to start and copy THAT to where you will use it.

The ONLY issue that could ever be a problem is if you need relational integrity between a side-end table and a non-side-end table. You cannot have RI between tables in two different files.

 

[Pat Hartman]

What do you mean by "overlay the previous copy"

You said these are "temp" tables. That means that you create them, fill them, and then either discard them or delete/refill them using code. The process to use the template BE is to have the temp tables already defined and empty. When you need to import the temp data or create it as part of some other process, your code copies the Temp_WorkTables.accdb to your local folder as WorkTables.accdb. That automagically replaces any existing copy of WorkTables.accdb.

To start the process.
1. create the temp BE
2. copy the temp BE to your standard working folder, renaming it in the process
3. link to the table(s) in the WorkTables.accdb

Then your code simply copies the temp version to replace the working version. NO tables need to be relinked. As long as the BE and table names are consistent, the links don't break. Since the working copy of the temp BE is never deleted, there is always a copy in your working directory unless you manually delete it - which you shouldn't.

I use a different name for the template Be than the working copy simply to avoid confusion. I always know what the "master" copy is and don't accidentally use it instead of the working copy.

I don't use a separate launcher. I include the copy code in the process that uses the table. That allows the data to persist in the temp table until I need to replace it. So, for example, I might only need a new version of the copied data once a month. That means I only run the "process" once a month and the data stays in the temp table for a month until it is overlayed.

 

[Bob]

Thank you for your ideas, Doc and Pat! Definitely a lot quicker to implement and maintain than my current approach.
Fun times ahead implementing this.