C & R

[pdanes]

Yes, you do. If the user makes a mistake and opens Access and clicks on the last used database, the new FE will not be downloaded and the one in the local directory will be used. This wouldn't be terrible unless this was the day that the versions changed. Better safe than sorry.

The only problem I see with your monolithic solution is that if the user downloads a new version and copies it to work offline without opening it first so it can run the copy code, he ends up off site with no data. Otherwise, as long as the data transfer is automated, it should be fine.

I suppose there is always some way for a creative enough user to mess things up. So, you have an FE that checks - what? You put a new FE on the server and at the same time update a version number somewhere in the BE, and the FE check to see if they match? Also a good method.

My single-file solution has one key feature, and that is the name. For example, I have one in the field for a lichenologist, and hers is named Lichen.accdb - wildly imaginative, I know. When I send her a new, empty version, it is named LichenNew.accdb, and that is what she puts into the same folder and runs, as her first step. The code sees its own name and empty tables, so it asks her for the name and location of her current data, with default as Lichen.accdb and the current folder. All she needs to do is hit Enter. The current data is read into LichenNew.accdb, doing any necessary manipulations along the way. When finished, it spawns a VBScript and kills itself. The script watches for the LichenNew.laccdb file to disappear, a Database.accdb to appear and disappear again (the new version does a C & R on close after reading in the current data). When all that settles, it renames Lichen.accdb to (e.g.) Lichen_Archive_2024-05-29_10-49-37.accdb, renames LichenNew.accdb to Lichen.accdb, with pauses between each action to allow the Windows file system to catch its breath, then restarts the newly renamed Lichen.accdb, and finally, deletes itself. So, she always know that the app she runs, which contains her data, is named Lichen.accdb, and LichenNew.accdb exists only for the duration of the import, then vanishes on its own.

Yes, she could copy the new, empty database somewhere and be stuck, but there is a limit to just how much hand-holding I can do. These users are not computer experts, but they are not idiots - they are highly respected natural scientists, often world-class authorities in their fields. When I give them clear and concise instructions, I can enjoy a reasonable expectation that they will be able to follow them. And again, the single-file configuration vastly simplifies everything - they know that THIS file is where their database is, period. Even if I wanted to go to a split system, beyond any doubt, the first response from every one of them would be, "Why all this? I liked it better when there was only one thing. Can't you put it back the way it was?"

 

[Pat Hartman]

what? You put a new FE on the server and at the same time update a version number somewhere in the BE, and the FE check to see if they match? Also a good method.

Yes there are version tables in both the FE and the BE and they have to match.

Your update method for the single db is clever but complicated.

"Why all this? I liked it better when there was only one thing. Can't you put it back the way it was?"

They don't actually need to know about the BE if you provide a copy script that always copies both the FE and the BE.

 

[pdanes]

Dim i As Long
For i = 1 To 10000
    With CurrentDb
        .Execute "insert into table1 (T) select top 100 T from table2", dbFailOnError
        .Execute "delete from table1", dbFailOnError
        .Execute ResetCounterDDl, dbFailOnError
    End With
Next

=>no crash.
Tested with Access 365 version 2405
Can you reproduce the problem?


... TableDef is used and not DDL.

Okay, you've got me - I don't know. I will be digging into this and changing the code to not use autonumber, but I will try to reproduce the problem myself before I dismantle the existing setup - I'm really curious now. Here is the entire routine that bombs:

Public Sub ResetTmpTable(ByVal x$)
With CurrentProject.Connection
    .Execute "DELETE * From tmp" & x & "AutoID"
    .Execute "ALTER TABLE tmp" & x & "AutoID ALTER COLUMN RaditPodle Number"
    .Execute "ALTER TABLE tmp" & x & "AutoID ALTER COLUMN RaditPodle COUNTER(1, 1)"
End With
End Sub

The tables that this works on contain only two fields - an ID number, used for a join to the appropriate record table, and the RaditPodle field, which is purely a sequence number, used later in an Order By clause, to export the records in a specific order. The ordering specification can be a zoo, so I scan the table according to the ordering spec, write the record IDs into this table, with that autonumber set to start at 1, the export them via a join and ordered by this autonumber.

The second ALTER statement is the line that crashes. But as I look at it now, I'm wondering if the first ALTER is not the source of the problem. Again, the online help says: "Access also creates a new field (increasing the internal column count by 1) for every field whose properties you modify." I don't recall now why that first ALTER is even there, but I wonder if THAT is not the line that increments the field counter, and I have been blaming an actually innocent autonumber reset.

Well, time to get to work on this. I wonder what it will turn out to be.

 

[pdanes]

Your update method for the single db is clever but complicated.

Well, if you can think of something that is simpler AND lets me keep it all in one file AND does not make the user's life any more complicated, I'd love to see it.

They don't actually need to know about the BE if you provide a copy script that always copies both the FE and the BE.

Sure, but again, that makes the entire setup more complex. They could be copying to a flash drive, and the drive letter could be anything. They could be putting it into OneDrive, to let the OD mechanism do the copying and transferring to a different computer, and pick it up there. They could be sending it to someone via FTP, or some online file transfer mechanism. And they can have it in different places in different computers. They could be making a backup copy. They could be copying to several different flash drives. They could be moving it to another drive in the same computer, because the current drive is running out of space. Probably other possibilities as well. Do you want to write a copy script that deals with all that? I don't.

And again, the first response would be,
"Why a copy script? I know how to copy a file."
There are two files now. (Actually, at least three, if you count the copy script - maybe more, for zipping and unzipping, etc.)
"Why two files?"
And away we go...

 

[Josef P.]

Is it perhaps related to deleted data fields? I was able to reproduce this error.
The space is only released again after Compact.

.Execute "ALTER TABLE tmp" & x & "AutoID ALTER COLUMN RaditPodle Number"

This line is also unnecessary. This will probably force a new data field in the background. => 1 deleted field + 1 new field => deleted fields require space up to compact.

 

[pdanes]

Is it perhaps related to deleted data fields? I was able to reproduce this error.
The space is only released again after Compact.

I wouldn't think so - I don't delete or create any fields in these tables.

You did get the error, though? How did you finally do it?

 

[pdanes]

@pdanes - My first question to you would be to check the table when this is done to see if it renamed the autonumber field. Normally, you don't care about the name of the field THAT much because, as noted, autonumbers are in theory not supposed to have any meaning other than "unique record number." If there is no table that depends on the table in question (i.e. no child table exists), then there are also no links involving this field and you would have no collateral issue in a "hidden" rename.

I do not rename the field. I posted the code that does all this in #23. The tables are used in a join, but are not defined as linked via foreign keys.

 

[ebs17]

With CurrentProject.Connection
    .Execute "DELETE * From tmp" & x & "AutoID"
    .Execute "ALTER TABLE tmp" & x & "AutoID ALTER COLUMN RaditPodle Number"
    .Execute "ALTER TABLE tmp" & x & "AutoID ALTER COLUMN RaditPodle COUNTER(1, 1)"
End With

You change the same field RaditPodle twice. Do you notice that?
If you are serious about a temporary table, you will delete it and create a new one. When you delete it, all the legacy data disappears.

With CurrentProject.Connection
    .Execute "DROP TABLE tmp" & x & "AutoID"
    .Execute "CREATE TABLE tmp" & x & "AutoID ( RaditPodle COUNTER )"
End With

 

[pdanes]

Okay, I created a tiny loop calling that routine. It reliably crashes on the second ALTER, which does the reset.

BUT...

It DOES NOT crash when I comment out the only first ALTER.
It DOES NOT crash when I comment out the only second ALTER.
It crashes 100% reliably after 127 iterations when I execute BOTH the ALTER statements.

 

[pdanes]

You change the same field RaditPodle twice. Do you notice that?
If you are serious about a temporary table, you will delete it and create a new one. When you delete it, all the legacy data disappears.

Yes, I noticed that - I mentioned it in #23. I also realized that I don't really need to reset it to one. I use it in an Order By clause, and all I really care about is the sequence. I suppose I could skip the reset and just let it keep increasing, maybe reset it if it ever gets close to max LONG INTEGER, which is not likely. Or maybe I can keep resetting it, but only reset - see previous post.

 

[Josef P.]

It DOES NOT crash when I comment out the only first ALTER.
It DOES NOT crash when I comment out the only second ALTER.

Because then no new data field has to be created for AutoNum.

Test files:

 

[pdanes]

Because then no new data field has to be created for AutoNum.

Test files:

Then why does it crash when I execute both?

 

[pdanes]

Because then no new data field has to be created for AutoNum.

Test files:

I just added this line to the routine, after the second ALTER:

    .Execute "Insert Into tmp" & x & "AutoID (ZaznamAutoID) Values(1)"

No change - if I comment out either one of the ALTER statements, it runs with no problem. When they both execute, it crashes after #127.

 

[Josef P.]

Then why does it crash when I execute both?

Because Jet/ACE seems to need a new data field for the conversion from Number to AutoNumber (also vice versa) all changes of data type and if a new field is inserted, the old one has to be deleted. Deleted data fields only release the space again after compact.

/edit: tested with:

db.Execute "ALTER TABLE " & TabName & " ALTER COLUMN ID byte"
db.Execute "ALTER TABLE " & TabName & " ALTER COLUMN ID Long"

Change data type => will crash after n loops

 

[pdanes]

Because Jet/ACE seems to need a new data field for the conversion from Number to AutoNumber and if a new one is inserted, the old one has to be deleted. Deleted data fields only release the space again after compact.

Oooohhh.. - you mean the first ALTER changes it to a regular number, and the second ALTER changes it back to an autonumber?

Now that makes sense. It would explain why I only get to 127, instead of 255 - two changes with each cycle. And why it makes no difference whether I add data or not. And why there is no problem when I comment out either ONE of the statements - because then JET is presumably smart enough to see that the field is already that way, and nothing needs to be done.

So what I was actually running up against was not the autonumber starting point reset, but the fact that I was (inadvertently and twice) actually changing the field type, back and forth between plain number and autonumber. Simply resetting the autonumber start was blameless in this, and can actually be done an unlimited number of times.

Jeez - I feel like a complete dork, but it all makes sense now. I'm guessing that first ALTER was from some initial testing when I was first putting this together, and I just forgot to remove it when I put in the second one. Or more likely, I commented it out, to leave the text in place if the new code didn't work properly, then accidentally hit Ctrl/Z one too many times after dinking around with subsequent edits, and never noticed that I re-enabled this line.

Okay, I guess I owe MS an apology for this one. And you a round of beers. If you ever get to Prague, look me up.

Many thanks.

 

[Josef P.]

[OT]

If you ever get to Prague, ...

I only have to drive about 500 km north.