Database

Private Sub ProcessHeadcountRecords()
    Dim dbsHeadcount As Database
    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 qdfNew As QueryDef
    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"
    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)
            DoCmd.SetWarnings False
            'If rstInputFile.RecordCount = 1 Then
                rstInputFile![NUMINDEX] = QueryDefs![strFileName]![NUMFOREIGNKEY]  /// I DO NOT KNOW THE SYNTAX HERE TO ADDRESS THE QUERYDEF OBJECT!!! PLEASE HELP!!!
            'Else
                'MsgBox ("Record not found")
            'End If
            'prgProgressBar.Value = prgProgressBar.Value + 1
        End With
        dbsHeadcount.QueryDefs.Delete strFileName
        rstInputFile.MoveNext
    Loop
End Sub