ACC2:"Invalid Argument" Error Msg Exporting Table to SQL Server

• Microsoft Access version 2.0 ---------------------------------------------------------------------

SYMPTOMS

Advanced: Requires expert coding, interoperability, and multiuser skills.

When you export a table that contains zero-length strings to Microsoft SQL Server, you receive the error message "Invalid Argument," and the Export command is not successful.

RESOLUTION

To work around this problem, do one of the following:

• Use Microsoft Access Upsizing Tools version 1.0 to transfer the table to Microsoft SQL Server.
• Before exporting the table, update the fields that contain zero-length strings so that they contain null values.

CAUTION: Following the steps in this example will modify your table. You may want to back up your table, or perform these steps on a copy of your table.

1. Open the database containing the table you want to export.

2. Create a select query based on the table you want to export. Add all the table's fields to the QBE grid and set Show to True for all the fields.

3. Add the following new field to the QBE grid, replacing <Field2> with the field name in your table that contains zero-length strings:

         Field: Expr1:Len([<Field2>])
            Criteria: 0
   
      Note that when you run the query, a zero (0) is displayed in the new
      field for each record in <Field2> that contains a zero-length string.
   
   

4. Change the select query to an update query, and then type "null" (without the quotation marks) in the Update To row of <Field2>.

5. Run the update query. Note that all the zero-length strings in <Field2> are changed to Null values. You can now export the table to Microsoft SQL Server.

STATUS

Microsoft has confirmed this to be a problem in Microsoft Access version 2.0. This problem no longer occurs in Microsoft Access version 7.0.

REFERENCES