I need some help w/ potentially modifying my "data extraction" approach.
Background:
- I have 2 tables with a 1:M relationship
- another office is NOT linked to our shared drive. To obtain their data input, we'd like to provide them with a data extraction method that easily generates a data "attachment". Unfortunately, sending MDB files via the pipeline does NOT work. (MDB files are being removed when attaching to email).
Current Approach:
- The "data extraction" process works (kinda) ok. In other works, the "mechanics" work fine. However, large records are being truncated (in Excel) when converting MDB table into Excel.
As pictures/samples speak "thousand words", I've attached a small sample db (pls change .txt to .mdb).
Process:
- if you feel comfortable opening the db, the startup form is "frmExportData"
- select "rptExportData" from 1st listbox; then select "mike.smith@work.com". This will enable the cmdButton "Email Data"
- once "Email Data" has been clicked, the form's function will generate an email (in Outlook) and convert the report "rptExportData" (which is linked via query to the two tables) into an Excel spreadsheet.
- If you save/open the XLS spreadsheet, you'll notice that (sample) text in the cells has been truncated.
More Info:
- while I like the XLS format -- it maintains the data integrity between "tblCTM" and "tblComments" via "Grouping" -- it obviously is a No-Go solution for the data extract.
- There's a very good chance that the majority of data entries for [[Observation], [Discussion], and [Recommendation] are more than 255 characters.
My Question:
- how can I modify the current process (another format other than XLS) while still maintaining data integrity between the 2 tables?
- I also tried export format "acFormatHTML" and "acFormatTXT". It's difficult to import that data format later on though.
Any recommendations as to how I can get this to work (For both sides, EXPORT and IMPORT process)?
Thanks,
EEH
Start Free Trial
