Creating a package for secured Access mdb

[dBjason]

SQL Server 7 / Access 97

Hello,

My question has 2 parts. I'm trying to create a stored procedure which will tag an Access 97 database and import a table.

The first problem I'm having is with security issues; when I try to run the DTS import/export wizard it won't let me continue after selecting the mdb file (says "cannot continue -- workgroup file is missing"). The Access db is secured and has an ldb file associated with it, although I don't know how to point the DTS wizard to open the db file and the ldb file.

Secondly, how do I save this "package" so a stored procedure can call it? I have to write a Crystal Report which is fed from a stored procedure, which needs to call DTS to import the table from Access 97, then process the data and kick out the report.

This is my first experience working with DTS, so maybe I'm barking up the wrong tree. Any help would be greatly appreciated.

Thanks,
Jason

 

[arst06d]

On the connection object for your Access database, have a look under the Advanced button. You will see space to specify the Jet OLEDB: System Database (*.mdw) file.

Save the DTS package.

In your stored procedure you will need to use the System Stored Procedures spOA_Create, spOA_Method etc to create an instance of the DTS package and run it. Look in books online for examples of this.