troubleshooting Question

creating and searching for multi column indexes

Avatar of developingprogrammer
developingprogrammer asked on
Microsoft OfficeMicrosoft Access
15 Comments1 Solution738 ViewsLast Modified:
hi all! i read this question insert / update record answered by Jim and really like the solution Jim gave us. i need to search for a multi index column though. i came across this on the allenbrowne website and i think it's fantastic, but how do i search for the index once i've created it? right now i'm using findfirst instead of indexing it. i want to index it though!! cause it's what the pros do!!! = ))

the other question is, how do i refactor my code? it's below allen's code!

so 2 questions  (oops became 3 questions!!)
1) how do i create multiple indexes (allen's way and jim's way is a bit different, jim uses index method whilst allen uses createindex - is there a different?
2) how do i search for a multiple column index after creating it? (essentially i want to modify my code to be more like jim's! = )   )
3) how do i refactor my code (at the bottom) into a function?

Function CreateIndexesDAO()
    Dim db As DAO.Database
    Dim tdf As DAO.TableDef
    Dim ind As DAO.Index
    
    'Initialize
    Set db = CurrentDb()
    Set tdf = db.TableDefs("tblDaoContractor")
    
    '1. Primary key index.
    Set ind = tdf.CreateIndex("PrimaryKey")
    With ind
        .Fields.Append .CreateField("ContractorID")
        .Unique = False
        .Primary = True
    End With
    tdf.Indexes.Append ind
    
    '2. Single-field index.
    Set ind = tdf.CreateIndex("Inactive")
    ind.Fields.Append ind.CreateField("Inactive")
    tdf.Indexes.Append ind
    
    '3. Multi-field index.
    Set ind = tdf.CreateIndex("FullName")
    With ind
        .Fields.Append .CreateField("Surname")
        .Fields.Append .CreateField("FirstName")
    End With
    tdf.Indexes.Append ind
    
    'Refresh the display of this collection.
    tdf.Indexes.Refresh
    
    'Clean up
    Set ind = Nothing
    Set tdf = Nothing
    Set db = Nothing
    Debug.Print "tblDaoContractor indexes created."
End Function



Private Function ImportRawProcessedToFinalDestination_1()
    Dim rsimportRecon1RawProcessed As DAO.Recordset
    Set rsimportRecon1RawProcessed = CurrentDb.OpenRecordset("importRecon1RawProcessed", dbOpenDynaset)
    
    Dim rsdataAWF As DAO.Recordset
    Set rsdataAWF = CurrentDb.OpenRecordset("dataAWF ", dbOpenDynaset)
    
    'loop through source table
    rsimportRecon1RawProcessed.MoveFirst
    Do Until rsimportRecon1RawProcessed.EOF
        'find record in destination table
        rsdataAWF.FindFirst "ATMLocation = '" & rsimportRecon1RawProcessed![ATMLocation] & "' AND " & _
            "TransactionDate= #" & Format(rsimportRecon1RawProcessed![TransactionDate], "m-d-yy") & "# AND " & _
            "Amount= " & rsimportRecon1RawProcessed![Amount] & " AND " & _
            "CreditDebit= '" & rsimportRecon1RawProcessed![CreditDebit] & "'"
        'add / edit record
        If rsdataAWF.NoMatch Then
            rsdataAWF.AddNew
        Else
            rsdataAWF.Edit
        End If
        'field to add / edit
            rsdataAWF![ATMLocation] = rsimportRecon1RawProcessed![ATMLocation]
            rsdataAWF![TransactionDate] = rsimportRecon1RawProcessed![TransactionDate]
            rsdataAWF![Amount] = rsimportRecon1RawProcessed![Amount]
            rsdataAWF![CreditDebit] = rsimportRecon1RawProcessed![CreditDebit]
        rsimportRecon1RawProcessed.MoveNext
    Loop

    CurrentDb.Execute "DELETE importRecon" & ReconFactorySetupNumber & "RawImported.* FROM importRecon" & ReconFactorySetupNumber & "RawImported;"
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "importRecon" & ReconFactorySetupNumber & "RawImported", PathOfSourceFileProcessed, True

End Function
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 15 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 15 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros