Database

Private Sub ProcessHeadcountRecords()
    Dim Cnxn As ADODB.Connection
    Dim strConn As String
    Dim rstInputFile As ADODB.Recordset
    Dim cmdSQLInputFile As ADODB.Command
    Dim strSQLInputFile As String
    Dim rstHyperionMany As ADODB.Recordset
    Dim cmdSQLHyperionMany As ADODB.Command
    Dim strSQLHyperionMany As String
    Dim rstHyperionOne As ADODB.Recordset
    Dim cmdSQLHyperionOne As ADODB.Command
    Dim strSQLHyperionOne As String
    Dim rstQueryDef As ADODB.Recordset
    Dim strPassThroughQuery As String
    Dim strDBPath As String
    Dim strFileName As String
    Dim strMessage As String
    
    strDBPath = "J:\GELCO DATABASE\Headcount Database\Headcount Database.mdb"
    Set dbsHeadcount = OpenDatabase(strDBPath)
    Set Cnxn = New ADODB.Connection
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=" & strDBPath & ""
    Cnxn.Open strConn
    
    Set rstInputFile = New ADODB.Recordset
    strSQLInputFile = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _
        "[L_R_G], [REGION], [JOB_FUNCTION], [ACTUAL], [NUMINDEX] " & _
        "FROM TBLINPUTFILE"
    rstInputFile.Open strSQLInputFile, Cnxn, adOpenKeyset, adLockOptimistic
    rstInputFile.MoveFirst
    
    Set rstHyperionMany = New ADODB.Recordset
    strSQLHyperionMany = "SELECT [COUNTRY], [TYPE], [BUSINESS_UNIT], " & _
        "[L_R_G], [REGION], [JOB_FUNCTION], [NUMFOREIGNKEY] " & _
        "FROM [TBLHYPERIONMANY2] ORDER BY [NUMFOREIGNKEY]"
    rstHyperionMany.Open strSQLHyperionMany, Cnxn, adOpenKeyset, adLockOptimistic
    

    strFileName = "qryPassThroughQuery"
    txtMessageBoxText.SetFocus
    txtMessageBoxText.Text = "Processing all the records from the upload file " & _
        "to an updated file to be imported into Hyperion."
        
    Do Until rstInputFile.EOF
        With dbsHeadcount
            strSQLHyperionMany = "SELECT [COUNTRY], [TYPE], " & _
                "[BUSINESS_UNIT], [L_R_G], [REGION], [JOB_FUNCTION], [NUMFOREIGNKEY] " & _
                "FROM [TBLHYPERIONMANY2] " & _
                "WHERE [COUNTRY]='" & UCase(rstInputFile![COUNTRY]) & "' " & _
                "AND [TYPE]='" & UCase(rstInputFile![Type]) & "' " & _
                "AND [BUSINESS_UNIT]='" & UCase(rstInputFile![BUSINESS_UNIT]) & "' " & _
                "AND [L_R_G]='" & UCase(rstInputFile![L_R_G]) & "' " & _
                "AND [REGION]='" & UCase(rstInputFile![REGION]) & "' " & _
                "AND [JOB_FUNCTION]='" & UCase(rstInputFile![JOB_FUNCTION]) & "'"
            rstHyperionMany.Requery
            Set qdfNew = dbsHeadcount.CreateQueryDef(strFileName, strSQLHyperionMany)   // CREATES THE QUERY
            DoCmd.SetWarnings False
            
            Set rstQueryDef = New ADODB.Recordset
            strPassThroughQuery = "SELECT * FROM qryPassThroughQuery"
            rstQueryDef.Open strPassThroughQuery, Cnxn, adOpenKeyset, adLockOptimistic     // NEEDS TO USE THE QUERY ***HERE ***
            rstQueryDef.Requery
            
            If rstQueryDef.RecordCount = 1 Then
                rstInputFile![NUMINDEX] = rstQueryDef![NUMFOREIGNKEY]
            Else
                'MsgBox ("Record not found")
            End If
            prgProgressBar.Value = prgProgressBar.Value + 0.4
        End With
        dbsHeadcount.QueryDefs.Delete qdfNew.Name
        rstQueryDef.Close
        rstInputFile.MoveNext
    Loop
End Sub