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!

 

[MattH1]

Hey everyone I'm still working on this and any help REALLY would be appreciated.

 

[Joe4]

I think it might be easier to control from Access instead of Excel.

If the issue is that your file has duplicate records (that is, records that already exist in your Access table), then import it into a blank table whose structure is a copy of your production table first, and then do an Unmatched query between that table and your production table to identify all new records, and use an Append Query to write just those records to your production table. Then clear out that copy table you are importing into in order to prepare for the next round (this could also be done as the first step instead the last step).

 

[MattH1]

I think it might be easier to control from Access instead of Excel.

If the issue is that your file has duplicate records (that is, records that already exist in your Access table), then import it into a blank table whose structure is a copy of your production table first, and then do an Unmatched query between that table and your production table to identify all new records, and use an Append Query to write just those records to your production table. Then clear out that copy table you are importing into in order to prepare for the next round (this could also be done as the first step instead the last step).

I would control this from Access, I have a master file that I would be running this from. I want the code to import an Excel file that I have and add it to a current database (not the master) and then delete all the duplicate records. This will happen 10 times in that code, one for each database that has to be updated. (closing the database each time after deleting all duplicate rows since it's 10 different databases I'm updating with 10 different excel files.)

For example:
Original Database:
ID|Status|Name|ID2|Date
1000|Cleared|John|5248|12.22
1001|Cleared|Matt|5374|12.22
1002|Not Cleared|Mike|5984|12.23
1003|Cleared|John|2491|12.23
1004|Cleared|Mike|3671|12.23

I would then add the newest file (an import from Excel) with this information:

ID|Status|Name|ID2|Date
1002|Not Cleared|Mike|5984|12.23
1003|Cleared|John|2491|12.23
1004|Cleared|Mike|3671|12.23
1005|Cleared|Matt|6573|12.23
1006|Cleared|John|9482|12.24
1007|Cleared|Mike|8974|12.24

When I put them together I would need the original database to contain:

ID|Status|Name|ID2|Date
1000|Cleared|John|5248|12.22
1001|Cleared|Matt|5374|12.22
1002|Not Cleared|Mike|5984|12.23
1003|Cleared|John|2491|12.23
1004|Cleared|Mike|3671|12.23
1005|Cleared|Matt|6573|12.23
1006|Cleared|John|9482|12.24
1007|Cleared|Mike|8974|12.24

The red would be the deletion of the duplicate rows.
It would search by ID and/or ID2, whichever is easier (ID is in order, ID2 is not.)
Please keep in mind I'm running this from a master Access database, as I am going to want to run this macro and it do this process 10-15 times.

Joe, I know I've seen you around the Excel Questions and I'm usually trying to help there, but Access VBA is a foreign language to me so any start/tips/work you can give me I would truly appreciate. Thank you!!

 

[Joe4]

I would control this from Access

OK. It looks like the VBA code you included is Excel VBA code, not Access code, as you are creating an Access application object (which you would not need to do if you were in Access VBA).

So, it looks like your data contains new data AND changes to existing data. So, you would still try to follow the same steps that I laid out in my previous post, and a few more:
1. Create a temporary table whose structure is a copy of your Production table, but contains no data (only need to do this one-time, and then not again)
2. Import your Excel file into a temporary table
3. Perform a query to match all records found in both your temporary and production tables
4. Run an Update Query to update all the existing records in your production table with the new information from the temporary table (using the information from the matching query you just created)
5. Do an Unmatched Query between the temporary table and production table to identify new records
6. Use an Append Query to write the new records from the temporary table to the production table (writing the new records located in the unmatched query you just created)
7. Clear out the temporary table for next use

Once you have this process set, you really only need to run steps 2,4,6,and 7 each time. You could use an Access Macro to do this, so you can just run all these steps by clicking on the Macro. If you need to make it more dynamic, there is a feature that will let you "Convert Macros to Visual Basic". Then you can go in and edit the VBA code (but it will give you most of what you need, sort of like the Macro Recorder in Excel).

 

[MattH1]

Hey Joe,
I know it's been a while since you posted this but I've been working on it diligently trying not to ask for much help and figure this stuff out. However I've encountered a new problem as I failed to mention this before.

Each of these tables I'm updating have different columns and different column headers (the last five or so at least are different, the first 20 are the same.) Would this mean that I have to make a temporary table for each one of them?
Furthermore, I want to run all of this from that one master file, even though there are many different temporary tables if I have to make them all. How do I ensure that the Query is run on the temporary tab if I'm running it/storing it in the main file? Or would I have to make the query in each temporary table?

Once again thank you for all of your help so far and hope you had a great holiday!

 

[Joe4]

Each of these tables I'm updating have different columns and different column headers (the last five or so at least are different, the first 20 are the same.) Would this mean that I have to make a temporary table for each one of them?

So, what is supposed to happens with these column headers that are different?
Are they supposed to end up in your final table?
If so, where do they go?

 

[MattH1]

So, what is supposed to happens with these column headers that are different?
Are they supposed to end up in your final table?
If so, where do they go?

Hey Joe, reading that quote definitely helped me recognize that I was very confusing in that statement. Here's an example of what I have in hopes that it will help.

Fifteen Excel files --> Each file has unique columns that are unlike any of the others.
Fifteen Access databases --> Each database has unique columns matching the excel file it is imported from (so file 1 from excel has the same headers as database1 in access)

I want to update all 15 of these files with the NEW data in the Excel file (the Excel file will have some old data that is already in the access DB and some new data that needs to be added). Each file/database is unique by only a few columns, but I believe that is unique enough to need their own "temp" sheet, correct? If so I'll start working on making this work, though I'll have to do some background research into all those queries you made note of. I also wanted to be able to run one item once (a macro) and have ALL 15 be updated (running from the master file).
Would the queries be in each temporary file or in the master file? I don't really know how to run those kinds of things from a VBA code, as I'm used to Excel where it is vastly different.

 

[Joe4]

If they are different, then yes, they will each require queries. You can use one as a "template" (or starting point) to build the rest.

When you say 15 different Access databases, do you really mean different databases, or different tables?
Do they all reference some single, central "master file", or does each have their own "master file"?
If they are different databases, then they are not really related to each other, they are in their own "separate" Access container.
That will affect how you set-up this process to run.

 

[MattH1]

If they are different, then yes, they will each require queries. You can use one as a "template" (or starting point) to build the rest.

When you say 15 different Access databases, do you really mean different databases, or different tables?
Do they all reference some single, central "master file", or does each have their own "master file"?
If they are different databases, then they are not really related to each other, they are in their own "separate" Access container.
That will affect how you set-up this process to run.

They each need to be their own DATABASE, aka have their own file to open. This is why it's tough for me to picture the setup, I am looking to have one "Master" file where I run everything from and that file would open each database and update it. The master file will have no data on it so that it doesn't take 1000 years to open.
Let me know if this didn't make any sense. I still am having trouble even starting something of this caliber, I feel a bit out of my league when it comes to Access and queries like appending the files together are tough for me. I also wanted to append it if two of the columns matched up, though I have no idea how to go that advanced. I'm sure it seems simple to you though haha.

E: If Columns J and Columns S are an exact match, then don't add it since it's a match. If they do not match exactly, add it since it's a new piece of data (J and S are unique identifiers, the others are not.)