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]

I'm starting to get a better understanding as I mess around with queries.

Could I just have a Master Access File open up each main database, import the new data into another table in the same database, and then run an unmatched query and copy over the data from the unmatched query to the bottom of the main database (and deleting the other tables to just have one table again)?

 

[Joe4]

Could I just have a Master Access File open up each main database, import the new data into another table in the same database, and then run an unmatched query and copy over the data from the unmatched query to the bottom of the main database (and deleting the other tables to just have one table again)?

If you want one Master file to control all, you will need to do the following:
- Link each and every table to be used to this database (so all 15 of your "Master" files" and any other files)
- Since your tables are of different structures, you will need to set-up 15 temporary tables, and then 15 sets of Update and Append Queries for each one
- You can then set-up a single macro to run all of your steps to process these queries
- If your Excel file names you are importing never change, you may not need VBA code. Otherwise, if the names can change, you will probably need to incorporate VBA.

Regarding your question about joining the tables on multiple fields, that can easily be done (it is a pretty basic Join feature).

If we can take a step back here for a second, what you are trying to do I would consider an "Intermediate" level Access program. However, it sounds like you are quite the beginner when it comes to Access. I would highly recommend educating yourself on Access a bit before proceeding too far. Access is not an intuitive program like Excel where you can often "wing-it on-the-fly". It is really important to understand the concepts of Relational Databases, and how to do basic things in Access, like create your own queries (and not just the simple ones where you are filtering one table, but rather ones that join multiple tables on multiple fields, do calculations, and Action queries).

While we can help you with specific questions, we really cannot effectively teach you Access. These type of forums are really conducive to that. Having a good basic foundation/understanding of Relational Databases and Access will go a long way in making this make sense to you, and save hours of frustration. I speak from experience - my first Access project was a complex one I jumped into without knowing much about Access or Relational Databases, and I spun my wheels for about 6 months before I took a step back and educated myself. It made a world of difference.

 

[MattH1]

I have definitely found this to be more of an intermediate task and am doing my best to understand those exact concepts you mentioned, as I know there is a lot I need to learn before I can properly tackle this kind of work. Thank you so much for your work and patience with me through this, I know I'm confusing at times but I guess the idea is very abstract to me since I have limited knowledge.

I'm confused as to why I would need an Update query. I feel like this process is being made more complicated than it needs to be. Also I don't know why I need to link them all to the master sheet. Can't I just use a VBA macro in the master sheet to open up the database I want to update? That's what my goal is in the end, to run my code from the master and update each individual database, but be sure that each database is separate and not combined so that multiple people can access them at once. Linking them has me worried the master file will be too large (I was hoping to accomplish the opposite), which is why I am separating them into many different databases.

Can I just open up the database file that I need to update, import the new excel sheet with the updated information, run an unmatched query to obtain the new data, and then copy that to the bottom of the original database info? (That last step I have no idea how to do, but that's what I'm working on now. I've gotten the rest of it if I do it manually, but I'm looking to automate this process so I run one macro and all 15 are cycled through and updated (aka import, unmatch query, copy records, delete the import since its no longer needed, save, close).

Also, the excel files will have the same name each time but I feel like VBA code would still be best so I can run them all at once (the folder name may change, I was thinking of making it dynamic and based on today's date but if that's too much then I think I can get rid of that idea...)

 

[Joe4]

Many of your comments are good example of why you need to further your education of Access, as most of these answers would be evident if you had that foundation.
For example:

I'm confused as to why I would need an Update query.

There are various Action queries in Access that perform different tasks.
If you want to Add new records, you would use an Append Query.
If you want to Update existing records, you would use an Update Query.
There is no query type that can do both at the same time.
If you do not need to update/make changes to any existing records, then you would not need an Update Query (I thought you mentioned that you do, though, in an earlier post).

Also I don't know why I need to link them all to the master sheet. Can't I just use a VBA macro in the master sheet to open up the database I want to update? That's what my goal is in the end, to run my code from the master and update each individual database, but be sure that each database is separate and not combined so that multiple people can access them at once. Linking them has me worried the master file will be too large (I was hoping to accomplish the opposite), which is why I am separating them into many different databases.

In order to "run your code from the master and update each individual database", you need to be connected to all that data. Linking to tables in other databases does not inherit the size of that database, so this master database which is linked to all this other tables would not be large at all, in size of memory. As matter as fact, this kind of uses a "best practice" of splitting your database, which says that all data tables should exist in a back-end database that contains the data and nothing else, and the front-end database should be linked to the data tables, and then contain all Queries, Forms, Reports, VBA, etc. Each user usually gets their own copy of the Front-End database to help protect against conflicts and database corruption: See: https://support.office.com/en-us/ar...database-3015ad18-a3a1-4e9c-a7f3-51b1d73498cc

Also, the excel files will have the same name each time but I feel like VBA code would still be best so I can run them all at once (the folder name may change, I was thinking of making it dynamic and based on today's date but if that's too much then I think I can get rid of that idea...)

You don't need VBA to run them all at once. If the file names do not change, you can use a Macro. The nice thing about that is you do not need knowledge of VBA. You just set up each step to run in a series of commands (most of which will be OpenQuery actions to run the queries that you have created). You will have a lot of steps (to import each field, run each query, etc), but you would have that regardless of whether it is a Macro or VBA. This just save you from having to write a lot of VBA code, which is probably a good thing if you are not experienced at it.

If you work through a basic Access introductory book, which goes through Tables, Relationships, Queries and Query Types, and Macros, most of what you want to do will be covered and make a lot more sense to you. If you do not have the time (or inkling) to do that, you may want to consider looking at hiring a Consultant to come up with a solution for you. They may even be able to come up with something slicker than this, using some advanced VBA programming.

 

[MattH1]

Hey Joe,

I have done (what I consider) ample research complete the task at hand. I have everything linked to one "Master" database. I have all my queries in my master and I have created VBA coding to run each query separately (and I can make the VBA coding to run them together if need be, it's just better separate for now.)

I am running into a last problem with a simple code that I can't seem to figure out. I want to create an IF statement that would end the sub if it fails to import the excel sheet. Here is one Sub I have, I figure the same code would apply to each Sub since it's repetitive and only changing the name of the document.

Sub FirstDatabase()
Dim strConPath As String
strConPath = "C:\FilePath\"
Dim strXL As String
strXL = strConPath & "FirstFile.xlsx"
DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Sheet1", strXL, True
DoCmd.OpenQuery "Unmatching FirstFile"
DoCmd.OpenQuery "Append FirstFile"
DoCmd.DeleteObject acTable, "Unmatched FirstFile"
DoCmd.DeleteObject acTable, "Sheet1"
End Sub

How would I enter the if statement to end the sub if it is unable to import the spreadsheet? Any and all help is appreciated once again Joe, I'm sure this line of code is very simple and I'm just using improper syntax of some sort!

 

[Joe4]

Do you get some sort of error when the import fails?
If so, you can use error handling to catch the error and exit the Sub Procedure.
Otherwise, you can do the following:
1. After the line of code where you (try to) import the data, add a line which counts the number of records in the table you imported to, using the DCOUNT function, i.e.

myCount = DCOUNT("*","TableName")

2. If that count equals zero, add a line that says "Exit Sub", i.e.

If myCount = 0 Then Exit Sub

 

[MattH1]

Do you get some sort of error when the import fails?
If so, you can use error handling to catch the error and exit the Sub Procedure.
Otherwise, you can do the following:
1. After the line of code where you (try to) import the data, add a line which counts the number of records in the table you imported to, using the DCOUNT function, i.e.

myCount = DCOUNT("*","TableName")

2. If that count equals zero, add a line that says "Exit Sub", i.e.

If myCount = 0 Then Exit Sub

It says Runtime Error '3011' aka it couldn't find the file name. I tried your second option and this error came up, I'm not entirely sure how an errorhandler would work here. Can I do like an IF(ISERROR(___)) Then Exit Sub Else REST OF CODE?

Thank you Joe, truly a life saver!!

Edit: SOLVED IT THANK YOU SO MUCH!!!!!!!!

 

[xenou]

THis will check if your file exists before running the other statements:

Sub FirstDatabase()
Dim strConPath As String
strConPath = "C:\FilePath\"
Dim strXL As String
strXL = strConPath & "FirstFile.xlsx"
[B]If CreateObject("Scripting.FileSystemObject").FileExists(strXL) Then[/B]
    DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Sheet1", strXL, True
    DoCmd.OpenQuery "Unmatching FirstFile"
    DoCmd.OpenQuery "Append FirstFile"
    DoCmd.DeleteObject acTable, "Unmatched FirstFile"
    DoCmd.DeleteObject acTable, "Sheet1"
[B]End If[/B]
End Sub

 

[Joe4]

Nice Micron!
Alway better to avoid errors if possible than to handle them after the fact!

 

[MattH1]

Wow Xenou that's actually exactly what I was looking for, I hate handling unnecessary errors and it may even speed my process up!!

I am working now with Access to try to get better at it and seeing if I can "update" my existing records. I know we spoke about this earlier in the thread Joe but I do have a question before I jump right into it and potentially "harm" the data I have.

If I run the match query between my imported data and my main table (the one that continues to update), the results will be just the items that are going to be updated (hence an update query follows.) Do these two sets of data need to be sorted to properly update? I don't know if I have to sort both the production table and the new import data by the unique identifier so that it will properly perform the Update Query or if I can just run it and it'll know to search by the unique identifier (or how to set it to do that. I've been using this to guide me but it doesn't state it anywhere what I will have to do: https://www.safaribooksonline.com/library/view/access-cookbook/0596000847/ch01s06.html

Thank you (again and again and again) for everything so far, I've learned more about this program in the past week than I thought I ever would and I'm actually starting to appreciate it for its capabilities.