asked on

How do I import an Excel file but first check the table that it hasn't been imported yet?

I've used the Transferspreadheet function before and this seems to work correctly for me. I now want it to do a check on the entries already in the table and where the field "DateChecked" = today, then I don't want to duplicate the data. The 'datechecked' field will be the same date according to the days batch that is imported it. It is found in the table "tblTrafficTrafficPositions".

Could you please help me put the criteria in? Is it some form of IF statement before the DoCmd?

Private Sub Command0_Click()
If MsgBox("Would you like to upload latest Traffic Travis Positions Data?", vbYesNo + vbExclamation, "Traffic Travis Upload File") = vbNo Then
Cancel = True
Else
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTrafficTravisPositions", "C:\Documents and Settings\My Documents\Business\eBusiness\Input\Positions.xls", True, "Positions!"
End If
End Sub

Open in new window

Paul_Harris_Fusion

Include the following function...

Public Function IsUpToDate() As Boolean
Dim lastCheck As Date
Dim rs As Recordset
Dim iAge As Integer

Set rs = CurrentDb.OpenRecordset("Select max(DateChecked) from tblTrafficTrafficPositions")
lastCheck = CDate(rs.Fields(0))
iAge = DateDiff("d", lastCheck, Now)

IsUpToDate = (iAge <= 0)

End Function

Then modify your existing code to:

Private Sub Command0_Click()
If IsUpToDate() Then
MsgBox ("Date is up to date")
Cancel = True
ElseIf MsgBox("Would you like to upload latest Traffic Travis Positions Data?", vbYesNo + vbExclamation, "Traffic Travis Upload File") = vbNo Then
Cancel = True
Else
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel9, "tblTrafficTravisPositions", "C:\Documents and Settings\My Documents\Business\eBusiness\Input\Positions.xls", True, "Positions!"
End If
End Sub

SOLUTION

peter57r

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jammin140900

ASKER

Thanks guys. I'm not sure what happened Paul but the code produced errors. I went on to using Peter's suggestion. I created a temporary table, which would check via a query before appending the details into the actual table.

I have the following SQL:

SELECT ttmpTrafficTravisPositions.Keyword, ttmpTrafficTravisPositions.[Search Engine], ttmpTrafficTravisPositions.Current, ttmpTrafficTravisPositions.Previous, ttmpTrafficTravisPositions.Top, ttmpTrafficTravisPositions.[Best page], ttmpTrafficTravisPositions.[Date Checked]
FROM ttmpTrafficTravisPositions
WHERE (((ttmpTrafficTravisPositions.[Date Checked])<>Date()));

I have a issue with the date. The criteria doesn't work using the date function. I think because its bring in date and time? Any suggestions please?

ASKER CERTIFIED SOLUTION

Paul_Harris_Fusion

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jammin140900

ASKER

I get the message "Syntax error (comma) in query expression DateDiff("d", [ttmpTrafficTravisPositions].[Date Checked]), Date()) > 0 ;"

I have:
SELECT ttmpTrafficTravisPositions.Keyword, ttmpTrafficTravisPositions.[Search Engine], ttmpTrafficTravisPositions.Current, ttmpTrafficTravisPositions.Previous, ttmpTrafficTravisPositions.Top, ttmpTrafficTravisPositions.[Best page], ttmpTrafficTravisPositions.[Date Checked]
FROM ttmpTrafficTravisPositions
WHERE DateDiff("d", [ttmpTrafficTravisPositions].[Date Checked]), Date()) > 0 ;

jammin140900

ASKER

Got it!

It's

INSERT INTO tblTrafficTravisPositions ( Keyword, [Search Engine], [Current], Previous, [Top], [Best page], [Date Checked] )
SELECT ttmpTrafficTravisPositions.Keyword, ttmpTrafficTravisPositions.[Search Engine], ttmpTrafficTravisPositions.Current, ttmpTrafficTravisPositions.Previous, ttmpTrafficTravisPositions.Top, ttmpTrafficTravisPositions.[Best page], ttmpTrafficTravisPositions.[Date Checked]
FROM ttmpTrafficTravisPositions
WHERE ((DateDiff("d",[ttmpTrafficTravisPositions].[Date Checked],(Date()))>0));

Thanks a lot for your help. :)