Windows 11 
Access 2019
Good morning:
I kindly request some assistance with reviewing a *fully functioning* VBA data import routine (both in Access and Excel). The VBA data import routine was originally developed in Excel; however, for efficiency purposes, it was translated in Access format.
At the present time, there is only one (1) issue with the >> DATA TYPE << ('short text' vs. 'date-time') in the Access version. Please allow me to start out with some background first.
GENERAL BACKGROUND:
- Attached .zip files contain 3 *sample* .LOG files (in subfolder).
- For illustration purposes only, the number of syslog messages are limited to only {2, 3, 4}, respectively.
- As these messy syslog formats need to be analyzed (actual logs have hundreds/thousands of messages), they require transformation into matrix format.
- Please see attached spreadsheet "Background.xlsx" (tabs 'Before' and 'After') wrt to parsing, splitting, concatenation, and moving content.
- The **key element** -- and reason for opening this post -- is the concatenation of the separate "date" and "time" (in log files) into a single date-time-stamp (on which calculations can be performed).
... so far so good (I hope). Now, for additional background purposes, it is advisable to review the attached Excel file "Import Syslogs.xlsm" (macro).
Excel Version (with working VBA):
- Upon opening "Import Syslogs.xlsm", click on the red command button "Import Syslogs" (in column A).
- Navigate to the subfolder "Syslog_Samples" and select either one (1) of the three .LOG files.
- Click "Open" will import the .LOG file into the correct and desired format (as illustrated in "Background.xls").
- Again, please note that values in column Charlie (C) are in the required Excel format which allows to perform calculations (e.g., subtract one data from another).
- Also, please note that the Excel VBA transformed the .LOG time from, e.g., "14:08:06:112" (hh:mm:ss:000) into Excel date-time format (mm/dd/yyyy hh:mm:ss.000) where Excel's milli-seconds are preceded with a "." (vs. ":").
- **The latter comment referring to "milli-seconds" is the underlying issue in the Access VBA-transformed version**.
... now, let's review the Access VBA routine:
Access Version (with overall working VBA):
- File "Import Syslogs.accdb" opens the form by default.
- Click command button "Import Syslogs" and navigate to the subfolder containing the sample log files.
- Select/open the subfolder and click "Ok" on the browse dialogue box.
- This imports **all 3 files ** at once (vs. in Excel where the import routine allows to process only one file at the time).
- Based on the existing sample data all date-time stamps are inserted into [FIELD2]. (Note: I have another process which adds proper field names but that's not pertinent for this question/post)
... finally, here's the underlying issue:
- [FIELD2] imported the concatenated (DATE) & (TIME) as-is (with the ":" preceding the milliseconds with data type = "Short Text".
- Therefore, in contrast to the Excel's VBA routine, I cannot perform any calculations on this field.
My question:
- How should the Access VBA routine be modified to ensure proper importing of the date-time-stamp content into date format (including milliseconds)?
Overall, I hope the VBA won't have to be fully restructured to accomplish it. Instead, I am hopeful it only requires some slight "tweaking" of the existing routine.
Thank you for your help in advance,
Tom
Reply With Quote
Originally Posted by skydivetom
