• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 236
  • Last Modified:

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

0
jammin140900
Asked:
jammin140900
  • 3
  • 2
2 Solutions
 
Paul_Harris_FusionCommented:
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


0
 
peter57rCommented:
The answer to this, like so many similar import questions, is to import to a 'holding' table first before appending the data to the final target table witha standard append query.  You can then use normal Access functions to look at the data before doing the append.

0
 
jammin140900Author Commented:
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?

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Paul_Harris_FusionCommented:
Try

WHERE  DateDiff("d", ttmpTrafficTravisPositions.[Date Checked]), Date() )  > 0  ;

0
 
jammin140900Author Commented:
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  ;


0
 
jammin140900Author Commented:

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. :)
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now