Changing Field Lengths

[kohl.mike]

About Me: I have basic knowledge of Microsoft Access, and I am a
computer programmer (visual basic .net)

Situation: I have been given the task to look into a problem that our
data department is having. Some of the data files that they receive
have many fields(sometimes 60 to 70 columns) and all the field lengths
exceed the maxium exporting length. All tables in our Data Department
are ran through a few queries and then exported as .dbf files.

Question: What would be the best solution for this type of problem?
The data processors are spending to much time changing the name and
length of each column. And sometimes, the client sends the wrong data
file, so this can be a very time consuming task.

Pondering Ideals:
1. I thought about creating a module that process each field and
change the field lengths. But how would the user run the module? I
noticed that the run icon in the module list is disabled.
2. I thought about creating a dbf writer in visual basic that would
generate a new dbf file, but this would take awhile to program and
debug.
3. I thought about creating a macro that will run a module, but since
my knowledge of access is limited, I could not figure how to create a
macro that will run a test module.

 

[Guest]

What kind of data files are you receiving? Text, CSV, some other program?

 

[kingston via AccessMonster.com]

What are your input files like? Are they fixed in format and location? If
they are, I suggest you use the menu function File -> Get External Data ->
Link Tables... This will provide you access to the data files(s) for
manipulation. Next, use a Make Table query to copy the linked data into new
tables in Access. Open the tables in design mode and change the field
lengths to your specifications. Export the data from the tables or from a
query if you want to manipulate the data further. Create a Delete query to
clear the Access table prior to getting new data and change the Make Table
query to an Append query. Next time, your process will be something like:

Run Delete query
Run Append query
Export Table

This can be done in a macro. If you call the macro Autoexec, it will run
automatically. In fact, if you add a step to quit Access, your users would
simply double click your Access database and be done. Be sure to have the
database automatically compact when closing and it'll truly be maintenance
free.

If the input data files are not fixed in format, you'll need to approach this
differently.

 

[kohl.mike]

The incoming data files can vary, usually they come in as text files,
excel files, or CSV files. Just to let you know I am familiar with
viewing the microsoft access tables in design view and edit the field
names, types, and length. The data department usually uses the design
view to edit field informaiton. However, when you have 60 or 70
fields this can be a very time consuming task especially when the
client sends the wrong file and wants you to do it again.

For temperary usage, I plan on creating a visual basic application
that will import a data file, and then export as a new microsoft
access table. Using xml I will keep track of what fields the user
renames, types and lengths. This way when the user imports a new data
file, it will default to the field information that was used on a
previous file. If the field information is not found I will default
it to the length of 65.

However, I would like to create this type of functionality in
microsoft access. The data processors use acccess on every job, so it
would be more convienent to have this functionality in access, instead
of accessing an external application.

I also have another question, what is the max lengh of total field
lengths that can be exported from microsoft access? I noticed that if
all fields have the length of 255 they will not export.