Private Sub btnImport_Click_Click() If MsgBox("Do you want to Import and Delete data?", vbYesNo) <> vbYes Then Exit Sub End If CurrentDb.Execute "delete * from [tblImportToAccess]" Dim strName As String Dim xlApp As Object Dim xlWB As Object Set xlApp = New Excel.Application With xlApp .Visible = False Set xlWB = .Workbooks.Open("C:\Users\tjohnson\Documents\Bomin\ImportToAccess.xlsx", , False) strName = "ToImport" End With Set xlWB = Nothing Set xlApp = Nothing DoCmd.TransferSpreadsheet acImport, , "tblImportToAccess", "C:\Users\tjohnson\Documents\Bomin\ImportToAccess.xlsx", True, strName & "!" MsgBox "Data has been imported. The table will now appear and need to check for accuracy.", vbInformation DoCmd.OpenTable "tblImportToAccess" On Error GoTo EH EH: If Err.Number = 2450 Then Exit Sub 'resume next 'don't know what this is for. ElseIf Err.Number <> 0 Then MsgBox "Error " & Err.Number & ": " & Err.Description End If End Sub
The question is, what are you doing with it once you get it into Access, your code doesn't give any indication of that.
Generally, when I link the spreadsheet from Excel rather than importing it, and name it XLImportToAccess
Then I run several queries against the data that was import to make sure that all required fields have data and that the data is of the appropriate values. If not, I present that information to the user and have them fix it, and try again. Sometimes I allow them to fix it in Access, other times I require them to fix it in Excel, before importing to Access. For repeatability, fixing it in Excel is preferable.
Then I import the data into my production table. Since you don't want to import records which already exist, your query might look something like:
INSERT INTO tblImportToAccess (field1, field2, field3, ... ) SELECT XL.Field1, XL.Field2, XL.Field3, ... FROM XLImportToAccess as XL LEFT JOIN tblImportToAccess as I2A ON XL.[Trade #] = I2A.[Trade #] AND XL.[Buy CP] = I2A.[Buy CP] AND XL.[Quantity BBLS] = I2A.[Quantity BBLS] AND XL.[Batch] = I2A.[Batch] WHERE I2A.ID IS NULL
This is what is known as a frustrated outer join. It lets you identify records in XLImportToAccess which are not in tblImportToAccess, and assumes that tblImportToAccess contains an identity field (autonumber) named ID.
Disclaimer: Always work with a copy of your database when developing, so that you don't overwrite critical data inadvertently.
Thank you for your advices John.
Dale:appreciate your response. I might link it. I will try to test later tonight. Thank you
Hi Dale, I am working on your suggestion. I have developed the query. Its pretty neat and surprised I got as far as I did.
I just need to confirm what the query is doing:
tblImportToAccess is my table I am working with (think its called production table as you say)
I do not need to append records from XL where there is a match between XL and tblImportToAccess on those various fields (bc that means its already in my production table and do not want dupes). I should only be appending records where there is not a match. Is this what the query is doing?
One other fact is that my XL will not have an ID autonumber because the excel file is just the raw data (assume this is ok). My tblImportToAccess does have an ID as PK and autonumber.
here is my code: (I might not be able to respond until tomorrow though)
INSERT INTO tblImportToAccess (ID,[Trade #], [Buy CP], [Quantity BBLS], [Data], Batch, [Origin / Deal], [Date], Grade, State, [Trade #1], [Sale CP], [Operation #], WorkingDate, SentAwayDate, Notes) SELECT XL.ID, XL.[Trade #], XL.[Buy CP], XL.[Quantity BBLS], XL.[Data], XL.Batch, XL.[Origin / Deal], XL.Date, XL.Grade, XL.State, XL.[Trade #1], XL.[Sale CP], XL.[Operation #], XL.WorkingDate, XL.SentAwayDate, XL.Notes FROM XLImportToAccess as XL LEFT JOIN tblImportToAccess as I2A ON XL.[Trade #] = I2A.[Trade #] AND XL.[Buy CP] = I2A.[Buy CP] AND XL.[Quantity BBLS] = I2A.[Quantity BBLS] AND XL.[Batch] = I2A.[Batch] AND XL.[Date] = I2A.[Date] WHERE I2A.ID IS NULL
Hi Dale...thank you for the response and advices.
One thing I didnt mention was that if there is a match between XL and tblImportToAccess on the fields i would need to use the XL.[Date] in every case in tblImportToAccess...much like overriding the tblImportToAccess.Date to the value in XL.Date. I am not sure if that is what the code is doing now as stands. Sincerely appreciate your expert assistance.
Its also possible that I remove only the initial match criteria on XL.Date and tblImportToAccees.Date and have a separate query to update to use the XL.Date. I hope I am not confusing this. So sorry if I am. Grateful for your help.
Not quite sure I understand what you are saying in these last two. Might be helpful if you could provide an example.
If you want to import records where there is no match across all 5 fields, then the SQL I provided should work.
If you want to update some fields in a record based on some other combination of fields, you will need another query.
Hi Dale, just letting you know I read your message and once I get a second I will provide a more detailed example. Possibly later on tonight
<If you want to import records where there is no match across all 5 fields, then the SQL I provided should work.
Sorry but i DO NOT want to import those records to tblImportToAccess where there is a match because those recods would already be in tblImportToAccess. I need to import the records that areNOT IN or essentially the records where there is not a match. Sorry for any confusion. Let me know if that changes the code.
The query I provided will add records to tblImportToAccess that are in the XL spreadsheet, but which are not already in tblImportToAccess.
Dale, I think that looks good. I have added records and seems to be doing what I want it to do! Yes! I do have a tweak on it though on the [DATE]. I planned to take it in steps and I will post another question.
glad to help.
1. Continue to use this code and just delete the "excessive" records
2. "Continue" the part of the Excel Automation to iterate cell- by cell and get what you need. Take a look here:https://www.access-programmers.co.uk/forums/threads/simple-loop-through-excel-cells.261290/