Changing Field types

  • Thread starter Thread starter Chris
  • Start date Start date
C

Chris

I link a couple of .csv files to a database. I then use make table queries
to make tables. My problem is that I can not get certain field types to
stick ie date/time. Some I can go back into the field and change the field
properties. Other times I get the message that an error has occured in
trying to change the fields. I have read on this and from what I understand
is that you can only change so many record sets and that if you have the
knowledge you can go into the Windows registry and fix this. (I dont) Is
there an append query that I could do to help with this?? Thank you very
much.
 
Chris said:
I link a couple of .csv files to a database. I then use make table queries
to make tables. My problem is that I can not get certain field types to
stick ie date/time. Some I can go back into the field and change the field
properties. Other times I get the message that an error has occured in
trying to change the fields. I have read on this and from what I understand
is that you can only change so many record sets and that if you have the
knowledge you can go into the Windows registry and fix this. (I dont) Is
there an append query that I could do to help with this?? Thank you very
much.
Click to expand...

I have a couple of suggestions, depending on how often you have to do
all this.

One idea is that, while you're defining field specifications in the
Import Wizard, click on the "Advanced" button, finish specifying field
parameters, then click on "Save as ..." and give your specification a
name. The next time you import a file of that type, instead of
re-specifying each field, you can click on "Specs..." and choose the one
you saved.

Another idea: Just import the entire contents of the file into suitably
sized Text fields in a new Table, then run a Make-Table Query on the
contents of that Table to format the imported fields as you'd like them,
such as converting text-string date values to Date/Time field values.
You can then delete the newly imported Table, keeping the one the
Make-Table Query created. (Don't delete the Make-Table Query.) If this
works well enough, you may be able to combine several steps (importing,
Make-Table, deleting) into a Macro to run whenever you need to import a
file.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.
 
Thank you for your help. I have to do this once a day when the new file runs
in the morning. Once again thank you. I will try what you described.
 
Chris said:
Thank you for your help. I have to do this once a day when the new file runs
in the morning. Once again thank you. I will try what you described.
Click to expand...

Then, having to do this every day, maybe I'd lean toward the "another
idea" of importing everything into text fields in a temporary table. It
will take slightly more work to set this up, but assuming that you can
depend on the text file's not having many mistakes, you can probably
automate almost everything and do the entire conversion just by running
your Macro. But you may want to try using both, to see which would be
easier to use on a daily basis.
 
Back
Top