asked on

Number Compression on Import into MS Access

I am importing data from an Excel spreadsheet into Access using VBA. One of these fields contains a serial number value. This value may be numeric or alphanumeric. For a value that looks like a large number both Excel and Access compress the value into something such as "1.32156E+15"

How can I stop this happening so that the full string is available?

Many Thanks

ASKER CERTIFIED SOLUTION

Gustav Brock

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

crystal (strive4peace) - Microsoft MVP, Access

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Jeffrey Coachman

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

crystal (strive4peace) - Microsoft MVP, Access

unfortunately, the import wizard does not respect format of cells and the logic to interpret data type kicks in and converts things anyway. I find it easiest to enter a dummy row in Excel and then delete it later. Just another method. How often will this will be done? If it is just one or a few times, it is probably quicker to do a dummy row. If more, than setting up an import spec makes sense.

The real test is how much needs to be fixed in the table design after importing, which should always be done. As a minimum... text data types will need to have "@" removed from the Format property, Unicode Compression set to True, and Size should usually be modified. Number data types will often need to be corrected for Size (Long Integer, Integer, Double Precision, etc) and possibly changed to Currency, Text, or something else

jay_waugh

ASKER

Thanks

Frank Helk

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.