Access VBA

css navigation by Css3Menu.com

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);"
'Many of my processes include a database history to see who is updating
    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)
'TableName is passed from another mod
    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.

© 2007-2017

Updated:  09/30/2017 19:48
This page added:  13 October 2007