Excel Import into Access

I think you need to import to an existing Access table. In this case the table will have all the column with pre-defined types and nothing will be changed

open the excel file and do a sort in the Date column so that the first row will have value, save and do your import

Right now it deletes the table and recreates it. Is it easy to code it so that it just deleted that contents of the table and not the whole table?

Capricorn1: I've got 6 date columns and the top row will never have all 6 dates filled in :-( otherwise I would do that.

to delete the content from tables use TRUNCATE Table1 syntax. I guess you use DROP Table1 syntax to delete the tables. As you can see the syntax is not much bigger to clear the content, but provides much better options in terms of data integrity

> Is it easy to code it so that it just deleted that contents of the table
> and not the whole table?

You need two queries. One to delete all records in the table, the other to append your Excel data to the table.
However, the data fields may still be text. If so, add another field of data type Date and run an update query where you update that field using the expression:

CVDate([YourTextDateFieldname])

like this:

UPDATE
    tblYourTable
SET
    [YourDateFieldname] = CVDate([YourTextDateFieldname])

/gustav

< I've got 6 date columns and the top row will never have all 6 dates filled in >

In that case, sorry to say but you will need a vba solution to get this done correctly while importing the  content of the excel file.

So I normally write a query in query wizard and then traslate it to VB Code. I don't know how to update a format in just the straight query design.

Here is what happens:

Private Sub ImportPSExcelFiles()
    Dim db As Database
    Dim rs As Recordset
    Dim sSQL As String
    Dim sFile As String
    Dim sTable As String
    Dim sProgress As String
   
    sSQL = "SELECT DataDirectories.Directory, "
    sSQL = sSQL + "DataFiles.FileName, DataFiles.TableName, DataFiles.Hidden "
    sSQL = sSQL + "FROM DataDirectories INNER JOIN DataFiles ON "
    sSQL = sSQL + "DataDirectories.DirectoryID = DataFiles.Directory "
    sSQL = sSQL + "WHERE (((DataFiles.FileType)="
    sSQL = sSQL + "'" + "Excel" + "'"
    sSQL = sSQL + ") AND ((DataFiles.Status)="
    sSQL = sSQL + "'" + "A" + "'"
    sSQL = sSQL + "));"
   
   
    Set db = CurrentDb
    Set rs = db.OpenRecordset(sSQL)
   
    With rs
        Do
            sFile = .Fields("Directory") + .Fields("FileName") + ".xls"
            sTable = .Fields("TableName")
            '  Delete existing table if it exist
            Call DeleteTable(sTable)
            '  Update progress display
            sProgress = BlankSpace + BlankSpace + BlankSpace + "Deleting Data in " + sTable
            Call RefreshProgress(sProgress)
            sProgress = BlankSpace + "Importing " + sFile
            Call RefreshProgress(sProgress)
            '  Import spreadsheet
            Call ImportSpreadsheet(sFile, sTable)
            '  Update FileDate attribute in the DataFile table
            Call UpdateFileDate(sFile, sTable)
            '  Hide the table
            '  If record is marked to hide the table, hided it
            If rs.Fields("Hidden") = -1 Then
                Call MakeHiddenTable(sTable)
            End If
            .MoveNext
        Loop Until .EOF
    End With
   
    Set rs = Nothing
    Set db = Nothing
   
End Sub

So that deletes and imports the table called 'Dates'

After it is created I was thinking that I could call a function like this:
Private Sub UpdateDates()
    '  Procedure is used to update the Dates Table
    Dim sSQL As String
   
    DoCmd.SetWarnings False
    DoCmd.RunSQL (sSQL)
       
    '  Update the fields in Dates so they are the correct format
    sSQL = ""
    sSQL = sSQL + " "
    sSQL = sSQL + " "
    sSQL = sSQL + " "
    sSQL = sSQL + " "
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
    sSQL = sSQL + ""
       
    DoCmd.RunSQL (sSQL)
    DoCmd.SetWarnings True

End Sub

But I don't know what to put on the lines to update the fields to dates. These are the fields:

ID = Text
Rcd# = Number
Brcd# = Number
Hire Date = Date
Last Asgn Start = Date
Rehire Dt = Date
Service Dt = Date
Return Dt = Date (this is always blank I've never seen it populated but it should be a date format.
Term Date = Date
Probtn Dt = Date
SPO Print = Test
Last Date = Date
Eff Date = Date
Indicator = Text

Right now depending on what row is on top of the excel file when it imports date formats are coming in as text so I need to have the UpdateDates convert it so they are date......Thoughts?

I use a query to retrieve the data from Excel as clean as possible, then another query to transform, filter, concat, split, and convert as needed. This way is quite fast for try and error which often is needed initially.

/gustav

OK.... so how does that codeing look? I am going on vacation next week and am trying to make this as neat for the person doing it as possible. Which means she pushes button and it goes......... The code that I put above is just one piece of a larger one. After all the excel files automatically inport into access there are sub routines that create more tables from the excel files. Then about 300 report automatically print out.

So right now I am trying to code the update to the dates table because up to this point I was halting the process before the print. Manually changing the field types, saving the file and then printing the reports. Which I coude write into a step by step for her, but at this point she is already getting frustrated with the whole thing so I am trying to fins a solution that does not involve manual intervention.

I did some of what you suggested I think. I had the excel import as normal (Dates2). Then I just created an insert query to take the data from the Excel Import and put it into a table (Dates) that had the correct formats and is used in all my reports. It has worked 3 times in testing it so I think that I am fixed. Is that what you were trying to say above?

    sSQL = "INSERT INTO Dates ( EmplID, [Empl Rcd#], [Ben Rcd#], [Hire Date], [Last Asgn Start], "
    sSQL = sSQL + "[Rehire Dt], [Service Dt], [Return Dt], [Term Date], [Probtn Dt], "
    sSQL = sSQL + "[SPO Print SSN], [Last Date], [Eff Date], Indicator) "
    sSQL = sSQL + "SELECT Dates2.EmplID, Dates2.[Empl Rcd#], Dates2.[Ben Rcd#], "
    sSQL = sSQL + "Dates2.[Hire Date] ,Dates2.[Last Asgn Start], "
    sSQL = sSQL + "Dates2.[Rehire Dt], "
    sSQL = sSQL + "Dates2.[Service Dt] , Dates2.[Return Dt], "
    sSQL = sSQL + "Dates2.[Term Date], "
    sSQL = sSQL + "Dates2.[Probtn Dt] , Dates2.[SPO Print SSN], "
    sSQL = sSQL + "Dates2.[Last Date],  Dates2.[Eff Date], Dates2.Indicator "
    sSQL = sSQL + "FROM Dates2; "

Yes, exactly. Your setup will of course differ, as no two scenarios are equal. Neither have I had the requirement to generate 300 reports.

/gustav

It is for our data entry double check. We call it Error Check they don't all print. But a good number of them do! Thanks for your help!

You are welcome!

/gustav