An Access 2007 database exists that includes a large number of tables each of which may have one or more MEMO fields. The contents of the MEMO fields are created by direct entry, cutting-and-pasting from documents and / or web pages and they can include a variety of visible and non-visible characters as well as HTML formatting commands, single and double quotes.
When using the standard EXPORT commands within Access 2007 to create CSV files suitable for uploading into a MYSQL database Access truncates the MEMO fields that are output into the CSV in seemingly arbitrary ways at normally about 500 characters. This is a known bug within Access products and it seems to relate to CR / LF characters and other hidden characters. (How Microsoft allows this bug to remain is extraordinary but it does and I have been unable to locate a solution on the Web.)
I need a solution to this and it may be that a Microsoft patch exists to get around the issue. You will be paid the same if this is the case and you find an existing solution.
If an existing solution is not available I require a VB MACRO compatible with Access 2007 which:
a) Displays a dropdown of all the tables in the active database ??" and a browse function allowing the user to specify where the exported CSV file(s) are to be placed. The default will be the directory where the database .mdb file is. BRWOSE button.
b) When one or more tables are highlighted the user can click on a RUN button and the macro will write out one (or more) CSV file(s) containing the contents of all the fields in the tables in CSV format with no truncation. There will also be a CANCEL button if the user decides to do nothing. Existing files with the same name are overwritten. The names of the output files are nameofable.csv.
c) Upon completion the VB macro will provide a listing of the locations where the CSV file(s) have been written and their names.
d) If CSV files can not be written an error message giving the reason will be displayed.
Within the VB macro comments will be included explaining the process.
Documentation on how the VB macro is to be installed and run within Access 2007 will need to be provided.
TEST DATA THAT WILL PROVIDED
A test copy of the MDB file will be available. Upon the completion of the project this data must be permanently deleted.
Examples of the CSV currently generated by Access 2007 showing the truncation of the MEMO fields.
Full payment following a 48 hour test period.
* * *This broadcast message was sent to all bidders on Monday Jan 4, 2010 3:37:17 PM:
I've attached a ZIP file containing a raw data MDB file and some CSV files that highlight the issue. I've also added a short MS Word document giving examples of table, records and fields where the issue can be seen. It might be that global edits within MS Access might fix the problem. Thanks, Alan