Need to trim *all fields* across *all tables*

[darkwind]

Since I added a parameter, you have to call the sub. I just did it in the immediate pane, but you could make another sub to do that.

For the immediate pane, press CTRl+G to open it, then type: Call TrimFields and press enter. If you want it to delete the old tables when done, instead use Call TrimFields(True).



If you want to have a sub saved to do that, just something like:

Public Sub RunTrim()
    Call TrimFields()
End Sub

[skydivetom]

Jack:

Per post #29, I did the following:
- Ran all 3 functions.
- Reviewed the tables... they still contain the trailing spaces.

//

Darkwind:

Per post #28, I did the following:
- Called the function via command button. I first ran it *without* TRUE as the parameter.
- Reviewed the new tables. The LEN values are correct. Awesome!
- I also added the following line at the bottom of the function:

'Show table changes in the database window
RefreshDatabaseWindow

Next:
- I replaced all tables with the old "junk" tables.
- Added TRUE to the function call.
- Commented out the 2 "DoOpen.Table" lines.
- Now, my original queries (linked to the original table names) display the correct LEN. FANTISTIC!!

Darkwind -- thank you for providing a GREAT SOLUTION/WORK-AROUND!!

//

To all other contributors. I also would like to thank you for your continued/dedicated assistance. When I opened this thread, I was really under the impression that the TRIM function (loop) would be a simple fix.

Apparently, this was not the case. But hey, I guess the Unicompression topic is something to brush up on. Again, thank you for the feedback/advice.

Cheers,
Tom

[darkwind]

Glad it ended up working for you, Tom.

Just to close out for anyone still confused about what in the world was going on here:

The tables here were likely generated using some VBA or SQL code. When tables are generated via most programmatic methods, they by default have Unicode Compression set to No. This is a relatively little-known behavior, because this setting doesn't change much that's visible to the user or developer. When tables are manually created, the Unicode Compression property by default is set to Yes. Considering the vast majority of devs and users have no idea what that setting does, and it doesn't seem useful to them, the vast majority of tables are left in the default configuration for this property. Changing this property can be done manually, but setting the property via DAO does not work correctly. It is also impossible to set the property to TRUE programmatically via the GUI or native SQL. The only programmatic way to change this property is to use ADO or Jet OLE DB in coordination with the WITH COMPRESSION keyword. The simplest method is via the CurrentProject.Connection.Execute method.

As for what Unicode Compression actually is... It's just a means of saving data space on the disk. English characters in the Unicode character space all start with a byte of zeros, followed by a second byte that's actually populated with data. This is to make conversion between older character spaces, like ASCII, simpler. Characters outside the English language contain non-zero data in both bytes that comprise the Unicode character. With Unicode Compression set to No, the full data of the Unicode character is saved in the table to the disk. For example, the character A is saved using two bytes, as 00 40 (hex). Non-English characters as saved as normal- for example, Ӂ is saved as 04 C1. With Unicode Compression set to Yes, Ӂ is saved the same way, still taking up two bytes each. However, English characters drop the empty byte and are saved as a single byte - A would be saved as just 40. The leading byte is added back in on data retrieval via software - so the difference is entirely invisible to the end user. The only difference is that English Unicode characters take up half as much disk space with Unicode Compression on. I suspect that on fast disks (SSDs), performance is marginally faster with Unicode Compression off, due to the computational overhead of adding back in and trimming off these bytes. I've never bothered to test that, though, and I expect performance would be essentially identical with Unicode Compression on or off when using slower disks. Either way, data storage space is likely to be much more important than some extremely marginal performance differences, and therefore I consider it best practice to always have Unicode Compression set to Yes.

But really... the difference between the two, in the vast majority of cases, is pretty trivial. Data storage consumption was, as far as I knew until today, the only difference between the two settings. I'd never seen any indication that either setting could cause any issue whatsoever from any source. Moke123 seemed to identify that this was the problem pretty quickly, though - so maybe this issue is more known than I'm aware of.

In any case, Tom's tables had Unicode Compression set to No, and his text fields were all populated up to their size limit with hanging spaces that could not be removed via any method until Unicode Compression was changed to Yes. This behavior is very odd to me, and I'd never seen it before. That's just not what Unicode Compression being off does... so I really don't understand how that was happening here. My only guesses would be either: 1. some very odd table-level corruption, leading to offset bits that could not be deleted via normal means (seems very unlikely to me, given multiple affected tables across a dummy and actual database), or 2. a character set conversion bug, where one or more non-Unicode characters were cast into Unicode, again leading to an offset issue... though that really doesn't make that much sense to me either.

[moke123]

Moke123 seemed to identify that this was the problem pretty quickly, though - so maybe this issue is more known than I'm aware of.

Nah, Moke's just a master Googler.

[skydivetom]

darkwind -- thank you for the additional research and providing your feedback/recommendation here. Very informative write-up. 'Much appreciated.

VR,
Tom

[orange]

Darkwind,

Thank you for explaining unicode compression, well done! I was not familiar with the property nor the different settings depending on how a table is generated/created.

[skydivetom]

Jack, Darkwind:

Again, the solution in post #28 works great and I have already implemented it into my application.

However, there's is another issue (a different issue) that requires solving. I have opened another post and would like to cross-reference it here (see below).
Existing VBA data import routine does NOT store date-time as "Date" (with milli-seconds) (accessforums.net)

Maybe you know of an easy solution how to fix **that** issue.

Cheers,
Tom

[skydivetom]

Darkwind:

Quick follow-up to my post #37. I made some progress on the new issue -- which could be intimately be related to your solution for TRIMMING data.

In the new post, I referenced modifying my existing VBA import routine. However, maybe... maybe it may not be necessary but tweaking your solution.

- Per your recommendation, the TRIM function did not seem to work due to unicode compression issue.
- Your work-around to a) create a new table and b) copy the data from imported tables into the new tables works great.
- So, would it be possible to slightly modify the TRIMFIELDS routine and also *transfer* the imported date-time (w/ milli-seconds) into new tables WHERE FIELD2 with have to be date-time/extended.
- Conceptually, that would solve the underlying problem.

It's only a matter of:
A. Setting FIELD2's data type to "Date/Time Extended".
B. Converting FIELD2 text value from "2018/10/29 14:08:04:092" into date-time of "2018/10/29 14:08:04.092" (that is, replace last colon with a period).

A & B (above) are a simplified scenario but it could work, right?

[moke123]

Tom,

Just a heads up. I've never used date/time extended but saw this:

The Date/Time Extended data type is supported in Microsoft Access 365 but is not compatible with non-subscription (perpetual) versions of Microsoft Access. As a result, if the data type is implemented within a local Access table and that Access database is shared with a non-subscription version of Access, you will not be able to open the database in your non-subscription version.

You may encounter Backwards compatibility issues, as well as limiting who in the forums will be able to open your examples.

[skydivetom]

Moke123 -- thanks for the heads-up. I appreciate it.

[darkwind]

In line with what Moke123 said, I would strongly recommend against using Date/Time Extended in practically any context:

1. Native Access calculation functions related to date/time don't work correctly with the format. You specifically state your purpose in converting to Date/Time is to enable date calculations, which will give flatly incorrect results using the date/time extended format in Access
2. Enabling and using the format completely prevents opening the database on older Access platforms
3. Even linking to tables (via e.g. ODBC) that include millisecond precision using date/time extended does not work properly and results in all records appearing as #Deleted in the relevant table (known bug).
4. You cannot create a Date/Time Extended data type field programmatically in Access. The support for it simply has not be implemented.

Overall, support and compatibility with the Date/Time Extended format is just severely lacking (incidentally, the same is true of the BigInt optional data type). There is a better solution... just store the Date/Time without milliseconds in a Date/Time field, and pull out the milliseconds into a separate field. Now native time calculations will work correctly, and if you need to care about e.g. the difference in time between two events in milliseconds, you can write custom function(s) as needed to accomplish that, something like:

Public Function DateDiffMs(ByVal StartDateTime As Date, ByVal EndDateTime As Date, ByVal StartMs As Integer, ByVal EndMs As Integer) As Long
   Dim lSecondsDiff As Long, lMsDiff As Long
   lSecondsDiff = DateDiff("s", StartDateTime, EndDateTime)
   lMsDiff = lSecondsDiff * 1000
   lMsDiff = lMsDiff + (EndMs - StartMs)
   DateDiffMs = lMsDiff
End Function

I've attached another version of the project with implemented changes. TrimData now checks for values in format ####/##/## ##:##:##:###* and converts those into two fields as described above, one Date/Time field and one integer field containing the Ms part of the date/time.

[skydivetom]

darkwind:

You make some extremely valid points wrt to, e.g.,
- "1. ... give flatly incorrect results using the date/time extended format..." ** This will be a show-stopper!
- "4. ... you cannot create a Date/Time Extended data type field programmatically in Access." ** Yes, I have NOT seen a property that would allow me to auto-insert the required field (w/ necessary DTE field type).

Thank you for providing an alternate solution. I like the idea of stripping out the milli-seconds and storing them separately in the "MS" field. Awesome!

Now, when executing the DB as-is, I get an error. See attached JPG. In the attached DB, I merely inserted the form that calls the function. Please ignore the option group for now (it this version, it has no functionality yet).

What might be causing this issue? At this moment, the default setting is set to TRUE (so that I can rename new tables/delete old tables).

[darkwind]

Bah, silly of me. Of course, you need to close the recordset before you can delete the table it's opened. Just add rs.Close right before DoCmd.DeletObject.

[skydivetom]

Darkwind:

thank you for the continued assistance. I appreciate it.

Ok, I added the rs.Close line but now get a different error (3144). Am I still missing something else?

[moke123]

Not to muddy the waters, but I'm curious as to the bloat created by creating and deleting the tables.

I would normally be importing the data to a temp table in a temp database and then clean it up when appending to my real tables.
When done I then delete the temp database.