how to import and convert data between 2 Access files

[Xavier]

Hello,

About a year ago, I wrote for our company a timesheet management
application. This was my first Access application and of course, even with
careful thinking, some design decisions proved to be unefficient. The
application is split in two access files. The first one contains the UI and
the logic, while the second one, residing on a server, only contains tables
and records.

I wrote a v2.0 of the UI without problem. I also wrote a v2.0 of the data
file and there I had to modify the data type used by some records in a
couple of tables. Everything now works as excepted but I face the following
migration problem: I need to get the timesheet history in my v2.0
application. Therefore I need to be able to map some fields that were purely
text data type in v1.0 and that should now be Currency data type.

Is there a way to import data and apply some conversion filters, or do I
have to copy the data over and then go back in design mode and change the
data types. I googled the subject but couldn't find any relevant
information.

Any help or pointer would be appreciated.

Thanks,

Xavier

 

[Brendan Reynolds]

In the new data file, either import or link the tables from the old data
file. Access will add numbers to the ends of the names of the imported or
linked tables so that they do not conflict with existing tables. That is to
say, if you have a table named Employees and you import or link a table that
is also named Employees, Access will name the imported or linked table
Employees1.

Now you can create an append query to append the data from the old, imported
or linked table, to the new table. Use the CCur() function to convert your
text to currency. This assumes, of course, that your text field does not
contain any values that can not be converted to currency. The likelihood is
that it does, and you will have to clean up those values before running the
append query.

When you've finished, you can delete the imported or linked tables from the
new data file.

 

[Xavier]

Thanks for the input. I'll give it a try.

Xavier