Change data type with a Macro

[CoyoteTraveler]

I have several macros I run all the time to import tables from excel, but
unfortunatly I have to go in manualy and change the data types when I import
a new table from Oracle. Oracle lets me save it at as excel spread sheet and
then I import it as well as several others to access with a macro. For some
reason I can't get the data type to change until I do it manualy in access.
Oracle keeps giving me text and I can't change it in excel. I tried to do an
edit table in the macro and the macro ends with the table open for edit but I
want to have it change the data type for me, like from text to number or
date. Any ideas?

 

[Clifford Bass]

Hi Coyote,

Why not import directly from Oracle? That should preserve the field
types and sizes.

Clifford Bass

 

[CoyoteTraveler]

Cliff,

I wish I could, the program that uses the Oracle data base is a third party
software made by Intercim in Washington State. The program will not let you
save it in access, you have to save it to excel first. If I could get in to
the raw Oracle data base I could do that. The main problem is the date
reversal, Oracle saves it as text in a YYYYMMDD format and once in excel you
can't turn it around, you have to put it in access.

 

[Clifford Bass]

Hi Coyote,

In that case, I would suggest linking to the Excel document instead of
importing it. Then running an append or make table query, depending on your
needs. In that query you would convert the text date to an actual date:

Date_Converted: DateSerial(Left(The_Date, 4), Mid(The_Date, 5, 2),
Right(The_Date, 2))

Hope that helps,

Clifford Bass

 

[CoyoteTraveler]

The macro imports three tables and runs a query but then I have to go back
into the tables and flip the dates. Maybe I can put the date converted in
THAT query, like in the WHERE total, what do you think?

 

[Clifford Bass]

Hi Coyote,

Without more details, I cannot be totally sure, but it sounds like
putting the date-converted calculation into a query that is based on the
import tables should do the trick. From there you could use it as needed as
a date. Which would include using it as a condition. Or for further
calculations. Or for storing as a date field in a make table query.

You are welcome!

Clifford Bass