Hi guys, I have an issue whereby my code does not detect new data when i import the excel file to access. I have posted this question in another forum, unfortunately i cannot get much help from there. I hope i will be able to get helps and solve this issue here! Any help offers is appreciated! Thank you!
LINK: http://www.accessforums.net/showthread.php?t=68899
I use date for my dlookup because the data will be updated every night thus, i use date to indicate new data.
My code for the import button on my form:
Private Sub cmdImport_Click()
Dim filepath As String
filepath = "C:\Users\User\Desktop\FabricPO.xlsx"
If FileExist(filepath) Then
DoCmd.TransferSpreadsheet acImport, , "TempFromExcel", filepath, True
If IsNull(DLookup("[Date]", "NewFabricPO")) Then
MsgBox "No new data to add"
Else
DoCmd.OpenQuery "qryappend", acViewNormal
End If
Else
MsgBox "File not found. Please check filename or file location."
End If
Dim SQLDelete As String
SQLDelete = "delete * from TempFromExcel"
DoCmd.RunSQL SQLDelete
End Sub
Function FileExist(sTestFile As String) As Boolean
'this function does not use DIR since it is possible that uou might have
'been in the middle of running DIR against another directory in
'an attempt to match one directory against another
'it does not handle wildcard characters
Dim lSize As Long
On Error Resume Next
'Preset length to -1 because files can be zero bytes in length
lSize = -1
'get the length of the file
lSize = FileLen(sTestFile)
If lSize > -1 Then
FileExist = True
Else
FileExist = False
End If
End Function
I use table "TempFromExcel to store duplicate data.
NewFabricPO is actually a unmatched query, and based on this table i would be able to add data that are not found in NewFabricPO table.
What is the SQL of the query "qryappend"? - this is to append all my data from NewFabricPO to my main table.
SQL for query "NewFabricPO"
SELECT TempFromExcel.[PO Type], TempFromExcel.Season, TempFromExcel.[Style NO], TempFromExcel.[GL Lot], TempFromExcel.Name, TempFromExcel.[Supplier ID], TempFromExcel.PO, TempFromExcel.[User ID], TempFromExcel.Name1, TempFromExcel.Line, TempFromExcel.Release, TempFromExcel.Date, TempFromExcel.Classification, TempFromExcel.Content, TempFromExcel.[Material Name], TempFromExcel.[Yarn Size and Type], TempFromExcel.[Fabric Weight], TempFromExcel.[Fabric Cuttable Width], TempFromExcel.Reference, TempFromExcel.Fabrication, TempFromExcel.Color, TempFromExcel.[Our Qty], TempFromExcel.UOM, TempFromExcel.[Supplier Qty], TempFromExcel.UOM1, TempFromExcel.[Unit Price], TempFromExcel.[Currency Code], TempFromExcel.Company, TempFromExcel.Approve, TempFromExcel.Canceled, TempFromExcel.[PO Date], TempFromExcel.[Garment Del Date], TempFromExcel.FOB
FROM TempFromExcel LEFT JOIN FabricPO ON TempFromExcel.[Date] = FabricPO.[Date]
WHERE (((FabricPO.Date) Is Null));
SQL for query "qryappend"
INSERT INTO FabricPO ( ID, [Date], PO, [Style NO], [GL Lot], Fabrication, [Fabric Cuttable Width], Color, [Our Qty], [Supplier Qty], Approve )
SELECT FabricPOQuery.ID, FabricPOQuery.Date, FabricPOQuery.PO, FabricPOQuery.[Style NO], FabricPOQuery.[GL Lot], FabricPOQuery.Fabrication, FabricPOQuery.[Fabric Cuttable Width], FabricPOQuery.Color, FabricPOQuery.[Our Qty], FabricPOQuery.[Supplier Qty], FabricPOQuery.Approve
FROM FabricPOQuery INNER JOIN FabricPO ON FabricPOQuery.ID = FabricPO.ID;
LINK: http://www.accessforums.net/showthread.php?t=68899
I use date for my dlookup because the data will be updated every night thus, i use date to indicate new data.
My code for the import button on my form:
Private Sub cmdImport_Click()
Dim filepath As String
filepath = "C:\Users\User\Desktop\FabricPO.xlsx"
If FileExist(filepath) Then
DoCmd.TransferSpreadsheet acImport, , "TempFromExcel", filepath, True
If IsNull(DLookup("[Date]", "NewFabricPO")) Then
MsgBox "No new data to add"
Else
DoCmd.OpenQuery "qryappend", acViewNormal
End If
Else
MsgBox "File not found. Please check filename or file location."
End If
Dim SQLDelete As String
SQLDelete = "delete * from TempFromExcel"
DoCmd.RunSQL SQLDelete
End Sub
Function FileExist(sTestFile As String) As Boolean
'this function does not use DIR since it is possible that uou might have
'been in the middle of running DIR against another directory in
'an attempt to match one directory against another
'it does not handle wildcard characters
Dim lSize As Long
On Error Resume Next
'Preset length to -1 because files can be zero bytes in length
lSize = -1
'get the length of the file
lSize = FileLen(sTestFile)
If lSize > -1 Then
FileExist = True
Else
FileExist = False
End If
End Function
I use table "TempFromExcel to store duplicate data.
NewFabricPO is actually a unmatched query, and based on this table i would be able to add data that are not found in NewFabricPO table.
What is the SQL of the query "qryappend"? - this is to append all my data from NewFabricPO to my main table.
SQL for query "NewFabricPO"
SELECT TempFromExcel.[PO Type], TempFromExcel.Season, TempFromExcel.[Style NO], TempFromExcel.[GL Lot], TempFromExcel.Name, TempFromExcel.[Supplier ID], TempFromExcel.PO, TempFromExcel.[User ID], TempFromExcel.Name1, TempFromExcel.Line, TempFromExcel.Release, TempFromExcel.Date, TempFromExcel.Classification, TempFromExcel.Content, TempFromExcel.[Material Name], TempFromExcel.[Yarn Size and Type], TempFromExcel.[Fabric Weight], TempFromExcel.[Fabric Cuttable Width], TempFromExcel.Reference, TempFromExcel.Fabrication, TempFromExcel.Color, TempFromExcel.[Our Qty], TempFromExcel.UOM, TempFromExcel.[Supplier Qty], TempFromExcel.UOM1, TempFromExcel.[Unit Price], TempFromExcel.[Currency Code], TempFromExcel.Company, TempFromExcel.Approve, TempFromExcel.Canceled, TempFromExcel.[PO Date], TempFromExcel.[Garment Del Date], TempFromExcel.FOB
FROM TempFromExcel LEFT JOIN FabricPO ON TempFromExcel.[Date] = FabricPO.[Date]
WHERE (((FabricPO.Date) Is Null));
SQL for query "qryappend"
INSERT INTO FabricPO ( ID, [Date], PO, [Style NO], [GL Lot], Fabrication, [Fabric Cuttable Width], Color, [Our Qty], [Supplier Qty], Approve )
SELECT FabricPOQuery.ID, FabricPOQuery.Date, FabricPOQuery.PO, FabricPOQuery.[Style NO], FabricPOQuery.[GL Lot], FabricPOQuery.Fabrication, FabricPOQuery.[Fabric Cuttable Width], FabricPOQuery.Color, FabricPOQuery.[Our Qty], FabricPOQuery.[Supplier Qty], FabricPOQuery.Approve
FROM FabricPOQuery INNER JOIN FabricPO ON FabricPOQuery.ID = FabricPO.ID;
