Using MS Access in DataStage 8.x

[dvilmain]

I have a need to pull data from a MS SQL Server 2005 database and create/populate tables in a MS Access database. I know that I will be using the ODBC method for accessing the SQL Server data...but what/how can I setup access to the MS Access database as a target.

Any help/examples would be GREAT!

David.

[chulett]

From UNIX? You'll need to get your hands on ODBC drivers for Access, but at least SQL Server wire drivers ship with the product.

[John Smith]

I have a need to pull data from a MS SQL Server 2005 database and create/populate tables in a MS Access database. I know that I will be using the ODBC method for accessing the SQL Server data...but what/how can I setup access to the MS Access database as a target.

Any help/examples would be GREAT!

David.

Why bother using Datastage then? Why not use Microsoft DTS? Save yourself the hassle.With no disrecpect to the IBM product but it's really horses for courses. In your situation I would just use the Microsoft products as it is a better fit unless you have really pressing reasons to use Datastage.

[John Smith]

I have a need to pull data from a MS SQL Server 2005 database and create/populate tables in a MS Access database. I know that I will be using the ODBC method for accessing the SQL Server data...but what/how can I setup access to the MS Access database as a target.

Any help/examples would be GREAT!

David.

Why bother using Datastage then? Why not use Microsoft DTS? Save yourself the hassle.With no disrecpect to the IBM product but it's really horses for courses. In your situation I would just use the Microsoft products as it is a better fit unless you have really pressing reasons to use Datastage.

[vmcburney]

Microsoft SSIS is the ETL product in SQL Server 2005 and it replaced DTS. And it's good advice to use the windows based tool that you can add on to SQL Server instead of trying to move data from Windows databases via a Unix server. The only pain is having to learn SSIS. Have you looked into linked tables? You should be able to import the SQL Server tables into Access as linked tables so the data stays in SQL Server. You can then use Access queries and macros to move the data or run your Access reporting and forms off the linked tables.

Linked tables in Access have the added benefit of being more robust (with the SQL Server engines) than putting data into Access tables where locking and corruption is more prevalent.