[Collect / DataGarage] Configuring an MS Access Data Source

Versions: All released versions of Collect and DataGarage

The following steps are a guide on the basics of how to set up a Microsoft Access data source for use with Collect or DataGarage.

Create an ODBCAccess connection type.

1. Go to Collect/Setup/Connection Types.
2. Click on the Sources icon next to the SQLSERVER Target connection record.
3. Add a new record using the following information: 
   
   Source Connection Type:  ODBCAccess  
   ODBC driver: Driver={Microsoft Access Driver (*.mdb)}
   
   
4. Save record.

Next, copy the Data Types from SQLSERVER to ODBCAccess using SQL Server Management Studio.  

1. Execute the following statement to extract the necessary records to modify and copy.
   
   USE [DataGarage]
   
   SELECT *
   INTO [Temp_ztTargetSourceConnectionTypeDataConversion]
   FROM [ztTargetSourceConnectionTypeDataConversion]
   WHERE TargetConnectionType = 'SQLSERVER' and SourceConnectionType = 'SQLSERVER'
   
   
2. Execute the following to update the temp table with the ODBCACCESS source connection type.
   
   USE [DataGarage]
   
   UPDATE [Temp_ztTargetSourceConnectionTypeDataConversion]
   SET SourceConnectionType = 'ODBCACCESS'
   
   
3. Execute the following to update the original table with the new values.
   
   USE [DataGarage]
   
   INSERT INTO [ztTargetSourceConnectionTypeDataConversion]
   SELECT * FROM [Temp_ztTargetSourceConnectionTypeDataConversion]
   
   
4. Delete the temporary table.
   
   USE [DataGarage]
   
   DROP TABLE [Temp_ztTargetSourceConnectionTypeDataConversion]

Finally, add and run a Manual Assemble (CranPort) package from Collect (DataGarage).  It must be a Manual Assemble / CranPort package.