MS-Access 2010 - Permanently set default to Leading Zeros in Dates - Is it possible?

[chmeee_ab_cdn]

I recently moved from Access 2003 to Access 2010. Frequently (about 20 times
per week) I import .CSV files from several different data sources. One of the fields


is a date, stored as text with a leading zero.

In Access 2003, I had no trouble as the text appended to an empty copy of the
table where all fields were defined as Text.

In Access 2010, even though the table has all fields defined as text, the
checkbox for Leading Zeros in Dates must be ticked EACH TIME or Access will drop
the zero.

I've looked through Options and browsed through the registry but have found
nothing that suggests a permanent default.

Has anyone discovered a way to set this ON permanently?

Thanks for your attention.
-C

Windows 8 Pro with Media Centre, MS-Access 2010 - 64 bit


(Screenshot.jpg)
---

[June7]

Maybe use an import specification http://office.microsoft.com/en-us/ac...101836402.aspx

[chmeee_ab_cdn]

Thanks, but I thought of that. The .CSV files have multiple
layouts and names change constantly. Unless I've approached
it all wrong, I would have to maintain many specs and blank
tables, then rename each table to match the filename after
each import. I will if I have to, but the method I've used has
worked since Access 97 (95?) and has only failed me now
with 2010.

Just looking for the most efficient way to get it working the
same way it did before.

Thanks again for taking time.
-C

[June7]

Confused, if the CSV files are constantly changing, how do you import to the same table?

[chmeee_ab_cdn]

Let's say last week I had a file "Week2.mdb" with several imports
in it. I would import a table (Definition Only) from the matching
type to my "Week3.mdb" file that matched the current CSV. I would
then rename it and import the CSV to append to the empty table.
In previous versions I would not need a spec, or have to check the
"Leading Zeros" box each time.

In 2010 it looks like I could keep a reference MDB with empty tables
& matching specs, and import each pair (table & spec) as I needed
them. It just seems like a whole lot of extra steps when it would
be so simple to have an option to set "Leading Zeros In Dates" to
be permanently ON.

-C

[June7]

There are always risks with upgrades. Far as I know, there is no way to modify any wizard defaults.

Still confused. If you are using established tables as templates for the CSV imports, then structure of the CSV files are known, it's the filenames that change. According to the referenced link, can select a different source file during the import operation.

There would be a one-time investment to create the various specifications. First need to verify that specification will save the checked Leading Zeros parameter.

[ssanfu]

I recently moved from Access 2003 to Access 2010. Frequently (about 20 times
per week) I import .CSV files from several different data sources.

If I was importing CSV filed that many times per week, I would set up the import specs and have VBA code to select the CSV file name, then do the import, using the import spec.

Or I would use VBA to select the CSV file, open the file, loop through the lines in the CSV file, parsing the fields as necessary.


My $0.02.....

[chmeee_ab_cdn]

Thanks Steve & June7 for taking time to look at this. As I
expressed before, I will try (again) to work out a system
using the specs to handle my workflow, but after doing this
for 18 years, I have yet to build a process for the diverse
bunch of files I get that produces results I trust.

I act as a service bureau for over 60 data collection
organizations to which the term "herding cats" would apply
very aptly. Also, our central SQL database (3rd party)
has export specs that vary from compliance with ISO 8601
date format to just about any date format you can imagine.
Standardizing this IS ongoing, but glacial in it's pace.

I repeat, I will try the specs again, but what I would really
prefer is an option (or registry hack) to permanently make
Access 2010 handle leading zeros in dates in the same way
it has for all it's previous versions.

Thanks again for your time.
-C