Using Excel to Update Access Tables

[aznbozo]

I am currently using Windows 7 and Office 2007.

I am working with a database in Access that has outdated data. There are multiple tables with different sorts of data that are not linked by any relationships.

I am hoping that I can create an Excel sheet to allow a user to easily update the data but I want to use one Excel sheet to be able to update all the different tables in Access. If possible I would like this Excel sheet have the abilty to add data as well. Basically a user friendly excel sheet that can add, update, data in already created Access tables.

I am not familar with Access very well and have no experience in VB so I am not sure if there is a code out there that can help me. I have looked into action queries but they dont see to serve the purpose I am looking for, or maybe I just dont understand action queries all that well.

Thank you!

 

[Hermanito]

What you wish to do is certainly possible, but when you say you are not very familiar with Access and have no experience in VB(A), it becomes a whole different story.
To be honest, I think it's a bit too ambitious, and it would basically come down to someone else doing the whole thing for you...

About the action queries: you would need those (INSERT and UPDATE queries) to add new records and to change existing records.

You might wanna google for CRUD (Create/Read/Update/Delete) for more information on the four basic database operations.

My advice would be to first try to create this functionality in Access itself, leave Excel out of it for now. With some trial and error, a lot of googling, and a good dose of patience, you will get there soon enough... and of course you can ask help here if you encounter any problems (which you surely will)...
If you have everything working in Access, creating an Excel frontend for it will be a lot easier...

good luck!

 

[aznbozo]

I agree it is too ambitious but I would like to try. I also really want it to have an Excel frontend and leave the Access doing its thing in the background.

To create the query in access:
From what I know about queries you have to create them in access and they only work when you click "run". Is it possible to assign a macro for that? I would prefer the user just have a very simple pllace to input the data and click one button in access to have all the information updated.

To create an excel spreadsheet as frontend:
I am envisioning just an excel sheet where the user can fill in all the information and then they would click a button which allows that data to be added/updated in the access database of information

Ultimately: The user does not have knowledge of Access and I am hoping that the user will not need to in order to use this database.

Thanks!

 

[Joe4]

A few comments:

From what I know about queries you have to create them in access and they only work when you click "run".

That is not correct. Then can be run automatically from Macros (see OpenQuery command), command buttons, VBA, specified events, etc.

Ultimately: The user does not have knowledge of Access and I am hoping that the user will not need to in order to use this database.

Any user can use a well-designed Form driven Access database. We have many people in our office who no higher education and who don't know the first thing about Access who use these databases without even realizing it.

IMO, a well-designed Access database is actually easier to use than Excel (for the users), as you can steer them in the direction you want and control most everything with button clicks.

 

[Joe4]

I moved your new question out to its own thread.

If you have a new question that is not closely related to your first one, it is best to start a new thread for it. Then it appears as a new unanswered question for people to see.

General rule of thumb:
- If you have a directly related follow-up, clarification, or "bump" to your original question, post it back to the original thread.
- If you have a new, unrelated question, post it to a new thread.

And be sure not to post the same question multiple times in different threads (many noobies do this when they don't get quick answers, and these duplicates will be locked or deleted).

 

[aznbozo]

Thank you for moving the post.

In regards to this thread issue it seems that I should be attempting to design a user friendly form through Access and imbed certain buttons with update querys and add querys.

I am hoping to have two different forms for updating the data and another form for adding the data. Is it as simple as adding macros to buttons?

In regards to these macros do I just use the Access incorporated system where there are drop down menus? Or is it easier to find a VB code online?

Thanks,
Betty

 

[Joe4]

Betty,

If you are going to tackle a project like this, I would strongly recommend educating yourself a bit on Access first. In order to create a well-designed database, besides knowing a bit about Access, it is also important to understand how relational databases work, and concepts of data normalization too.

I would recommend picking up an introductory book on Access to work through it. For some information on relational databases and concepts of data normalization, here is a good place to start: http://www.deeptraining.com/litwin/dbdesign/FundamentalsOfRelationalDatabaseDesign.aspx

I strongly caution people about diving into a big Access project with little or no knowledge on these topics. I speak from experience. I toiled along for three months on my first project before educating myself, scrapping everything, and starting over.