We have done a refactor of an access db to a sql db.
I need to somehow keep the data from access in sink with sql, however one of
the problems is that the tables in access are single fields ie.
category->shoes.
During our refactor we made these table have pk so the are now CategoryID->1
and CategoryName->Shoes.

The access table for a product stores shoes for the categoryID.
The SQL table for a product stores 1 for categoryID

Is there a method when pulling over the access info to find out what numeric
ID should go into the SQL table based on the text stored in the access
category.

Hope that all made sense.

Ok figured this much out.
I should be using the Lookup transforms.
I just keep having problems as the access db is as DB_WSTR and the sql is
DB_STR.
so it moans about data type being incorrect.


"Wayne Van Rooyen" wrote:

Hi,

Thank you for your reply and the detailed additional feedback on how you
were successful in resolving this issue. This information has been added to
Microsoft's database. Your solution will benefit many other users, and we
really value having you as a Microsoft customer.

If you have any other questions or concerns, please do not hesitate to
contact us. It is always our pleasure to be of assistance.

Have a nice day!

Charles Wang
Microsoft Online Community Support

================================================== ====
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from this issue.
================================================== ====
This posting is provided "AS IS" with no warranties, and confers no rights.
================================================== ====

Wayne,

You can use a derived column transform to change the data type of
before the lookup.

Have you thought about using SQL Server as the back end for Access? The
last time I looked there was still a wizard that would do this for you
quite easily.


Joe

Wayne Van Rooyen wrote: