Database

Private Sub cmdOutputFilePath_Click()
   'The error message is a compiler error stating 'User-defined type not defined'

    Dim dlgLoadOutputFilePath As FileDialog
    Set dlgLoadOutputFilePath = Application.FileDialog( _
        FileDialogType:=msoFileDialogOpen)
    With dlgLoadOutputFilePath
        .AllowMultiSelect = False
        .Show
   End With

    If txtInputFilePath.Value = Null Then
        txtOutputFilePath = ""
        txtInputFilePath.SetFocus
    Else
        prgProgressBar.Value = 20
        txtMessageBoxText.SetFocus
        txtMessageBoxText.Text = "<Message>"
    End If
End Sub

Option Compare Database

Public dbsHeadcount As Database
Public Cnxn As ADODB.Connection
Public rstHyperionMany As ADODB.Recordset

Function ProcessHeadcountFile()
    On Error GoTo ErrorHandler

    Dim strConn As String
    Dim rstLoadFile As ADODB.Recordset
    Dim cmdSQLLoadFile As ADODB.Command
    Dim strSQLLoadFile As String
    Dim rstHyperionOne As ADODB.Recordset
    Dim cmdSQLHyperionOne As ADODB.Command
    Dim strSQLHyperionOne As String
    Dim cmdSQLHyperionMany As ADODB.Command
    Dim strSQLHyperionMany As String
    Dim strFileName As String
    Dim qdfTemp As QueryDef
    Dim qdfNew As QueryDef
    Dim strHoldRecString As String
    Dim strMessage As String
    
    Set dbsHeadcount = OpenDatabase("J:\<FILEPATH>\Headcount Database.mdb")
    Set Cnxn = New ADODB.Connection
    strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source=J:\<FILEPATH>\Headcount Database.mdb;"
    Cnxn.Open strConn
    
    Call LoadExcelIntoAccessTable
    Set cmdSQLInputFile = New ADODB.Command
    Set cmdSQLInputFile.ActiveConnection = Cnxn
    Set cmdSQLHyperionMany = New ADODB.Command
    Set cmdSQLHyperionMany.ActiveConnection = Cnxn
    strSQLHyperionMany = "SELECT [COUNTRY], [TYPE], [BUSINESS UNIT], " & _
        "[L/R/G], [REGION], [JOB FUNCTION], [09/12/2007 Reported] " & _
        "FROM [tblInputFile]"
    cmdSQLHyperionMany.CommandType = adCmdText
    cmdSQLHyperionMany.CommandText = strSQLHyperionMany
    Set rstHyperionMany = cmdSQLHyperionMany.Execute()
    rstHyperionMany.MoveFirst
    Call CreateIndexesForLoadedTable
ErrorHandler:
    If Err <> 0 Then
        MsgBox Err.Source & ":" & Err.Description, , "Error and Error #" & Err.Number
    End If   
End Function

Sub LoadExcelIntoAccessTable()
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, _
        "tblInputFile", "C:/Employees.xls", True
End Sub

Sub CreateIndexesForLoadedTable()
    Dim tdfInputFile As TableDef
    Set tdfInputFile = dbsHeadcount.CreateTableDef("tblInputFile")
    tdfInputFile.Fields.Append tdfInputFile.CreateField("Date", dbDate)
    dbsHeadcount.TableDefs.Append tdfInputFile

    ERROR: Here is where it is saying that tblInputFile already exists. How do I append the field called "Date" to the existing table called tblInputFile??????????? :confused::confused::confused:

End Sub

if @Filter = 'All' and @DateFrom is Null and @MatterRef = ''<br />
BEGIN<br />
select * <br />
from undertakings <br />
order by <br />
	case <br />
		when @sortBy = 'matterRef' then undertakings.MatterRef<br />
		when @sortBy = 'Date' then undertakings.DateTaken<br />
	end<br />
END<br />
if @filter = 'Active' and @DateFrom is Null and @MatterRef = ''<br />
BEGIN<br />
select * <br />
from undertakings <br />
where Discharged = 0 <br />
order by <br />
	case <br />
		when @sortBy = 'matterRef' then undertakings.MatterRef<br />
		when @sortBy = 'Date' then undertakings.DateTaken<br />
	end<br />
END<br />
if @Filter = 'Discharged' and @DateFrom is Null and @MatterRef = ''<br />
BEGIN<br />
select * <br />
from undertakings <br />
where Discharged = 1 <br />
order by <br />
	case <br />
		when @sortBy = 'matterRef' then undertakings.MatterRef<br />
		when @sortBy = 'Date' then undertakings.DateTaken<br />
	end<br />
END