Importing Excel to Access

[MattH1]

Good afternoon,
I have a master file that I want to run a VBA code from. I want to open up an existing database (lets called it Filter1.accdb) and import the new information into this database (call it Filter1Info.xlsx). This step alone I'm having trouble with because I feel like it will import the data into the master file which is not what I want.

Here's the code I was thinking of using, please help me in any way you can (advice, code, etc.)

Dim strDB As String
Dim appAccess As Access.Application
Constr strConPath = "C:\MH\Folder1\"
strDB = strConPath & "Filter1.accdb"
Set appAccess = CreateObject("Access.Application")
appAccess.OpenCurrentDatabase strDB
appAccess.DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, C:\MH\Folder1\" & Format(Date, "mm") & "." & Format(Date, "dd") & "\Filter1.xlsx"

The next (and last) step of my code would be to delete all duplicate columns based on their Signal ID, one of the unique columns in the database. Please let me know how I can create a code like this and have it run successfully, it would be very helpful!

 

[Joe4]

Do these two sets of data need to be sorted to properly update?

No. Order will not affect how these queries run.
The only time I have ever seen order used in update queries is when trying to compare records in a table with the previous record in the same table, or when updating data via Recordsets in VBA.

 

[MattH1]

No. Order will not affect how these queries run.
The only time I have ever seen order used in update queries is when trying to compare records in a table with the previous record in the same table, or when updating data via Recordsets in VBA.

Hey Joe,

Happy New Year! Hope you've been well. I'm now using an Update Query to update old cells (I didn't think I would need to, but added more data that would be updated to make my life a bit easier long-term.)
For example, I have a customer with a unique ID and date and yes/no column. When I import my new file, it'll have this old data entry and switched from NO to YES. It would be in the middle of the data set. Does this mean I have to sort the data to find it or would it match and update based on the unique ID automatically? I tried searching for this answer online but couldn't really get anywhere.

Thanks!

 

[Joe4]

Sorting plays no role here. The important piece is to match on the Unique ID. If you do that, it does not care where the record is found, just that it is found.

When doing Update Queries, I usually do the following:
- First create a Select query that only returns the records that need updating. So, you want to join your two tables on some key field(s), and check to see if a particular value has changed (using Criteria).
- Once you have the correct records being returned by your Select query, change the query type to an Update Query, and indicate which field is being changed to what.