Add an Index
We wrote an application that we needed to rebuild a number of tables each week. If we had used linked tables, we would have had to deal with ODBC issues for a dozen people spread out all over the state.
DoCmd.SetWarnings False
DoCmd.OpenQuery "Rebuild Names_Addresses", acViewNormal
DoCmd.RunSQL "INSERT INTO Database_History ( DB2_ID,UserID, End_D, MachineID,BuildType )" _
& " VALUES('Addresses','" & Environ("USERNAME") & "',#" & Now() & "#,'" & Environ("COMPUTERNAME") & "',9);"
DoCmd.RunSQL "CREATE INDEX PeopleID ON Names_Addresses (PART_ID_I);"
DoCmd.RunSQL "CREATE INDEX DateID ON Names_Addresses (CLINICID_I);"
DoCmd.SetWarnings True
Here is another version
Public Sub CurrentProject_Execute(TableName As String)
Dim strSQL As String
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
strSQL = "CREATE INDEX " & TableName & " PART_ID_I"
cnn.BeginTrans
cnn.Execute strSQL
cnn.CommitTrans
End Sub
It is a long process, generally begin the process and go to lunch. But the system has been running for 5 months without failure.
|