ACC2000: CommandTimeOut Expired Error When You Try to Make a Design Change to an Access Project (243464)

SYMPTOMS

In an Access project, when you try to make a design change to a very large table (in most cases in excess of 1 million records), you may receive the following error message:

Errors were encountered during the save process. Some of the database objects on your diagram were not saved.

'<table name> Table
Unable to modify table.
ADO Error. Timeout Expired.

CAUSE

You encountered a built-in time-out before the command could be completed. If the command from the Access project takes over thirty seconds (the default value) for the server to complete, a time-out error occurs. This can happen, for example, when you perform DDL (Data Definition Language) operations on tables that contain large amounts of data. This can happen when you perform such design changes as adding a primary key or index to a table or when you change a field's data type or attributes. You cannot modify this time-out property within the Access user interface.

RESOLUTION

Create a Visual Basic for Applications procedure in the Access project that runs the ADO Command object. The ADO Command object can programmatically make changes to the design of a large table without error if the .CommandTimeOut property is set either to a large enough positive value or to zero (0), which means that is should never time out.

In the following example, the field BigID is added and is also set as the primary key in the table tblLarge:

Sub addPrimaryKey()
    
    Dim ADOCmd As ADODB.Command
    Set ADOCmd = New ADODB.Command
    
    With ADOCmd
         .CommandText = "ALTER TABLE tblLarge ADD " _
                        & "BigID INT IDENTITY " _
                        & "CONSTRAINT BigID_pk PRIMARY KEY;"

        .ActiveConnection = CurrentProject.Connection
        .CommandTimeout = 0
        .Execute
    End With
    
    Set ADOCmd = Nothing
    
End Sub

MORE INFORMATION

Steps to Reproduce the Behavior

In Access, open the sample Access project NorthwindCS.adp.

Create a sample table in that database by running the following code:

Note In the following sample code, you must change UID=<username> and PWD=<strong password> to the correct values. Make sure that the user ID has the appropriate permissions to perform this operation on the database.

Function MakeTestTable()
Const DB = "DRIVER={SQL Server};SERVER=Chriswy1;UID=<username>;PWD=<strong password>;DATABASE=northwind"
Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
cn.Open DB
       
On Error Resume Next
SQL = "DROP TABLE tblLarge"
cn.Execute SQL
On Error GoTo 0

SQL = "SELECT * INTO tblLarge FROM [ORDER DETAILS]"
cn.Execute SQL

SQL = "INSERT INTO tblLarge (OrderID, ProductID, " _
      & "UnitPrice, Quantity, Discount) SELECT " _
      & "[Order Details].OrderID , [Order Details].ProductID, " _
      & "[Order Details].UnitPrice, [Order Details].Quantity, " _
      & "[Order Details].Discount FROM [Order Details];"
   
For I = 1 To 500
        cn.Execute SQL
Next I

MsgBox "Finished Creating Table tblLarge."

End Function

In Access 2000, create an Access project that is based on this database.
In Design view of the tblLarge table, try to make the first field a primary key.
Save the changes and wait several seconds. Note that you receive the error message that is mentioned in the "Symptoms" section of this article.