Word Doc, Excel with Access

[Guest]

Is it possible to use whether a Word doc or Excel 2003 spreadsheet to update
an Access DB 2003? If Yes, can you please let me know how?

Thanks a lot!

Jean.

 

[Rick B]

Not sure what you mean. You can import data from Excel into Access. I
believe you can even link Access to an Excel spreadsheet.

What are you trying to accomplish? Perhaps we can offer some suggestions.

 

[Guest]

I think what Jean is trying to ask is, is it possible to take an excel or
word document, and have access update its current database with the data in
the word or excel file.

I am also trying to do something similar with a project and am very
interested to know if this is possible, and if so, how it is done. Thanks.

 

[Rick B]

As I stated, yes it is possible. You can import data from Excel or link to
an excel spreadsheet. This is done using File/Get External Data.

But, the question is still "What specifically are you trying to do?"
Do you have users that don't have Access, that need to get data into your
database? There are other ways to deal with this.
Do you get data from a vendor, customer, or other third-party source as an
Excel file and you need to import that data? If so, how often? What type
of data, etc.
Do you have a spreadsheet out there that is regularly updated by employees
in your organization and you need to link to it and get up-to-the-minute
data?

Using "Get External Data" then link or import is the short answer to your
question, but we might have some hints that will help you do even more. The
more information you give us on what you are trying to accomplish, the more
likely we will be to give you the best answer for your specific situation.
We've seen lots of questions where we can answer the actual question, but
often the user is overlooking an easier or better way.

For example, many people ask how to build a table to store passwords. If we
dig further and find that they are trying to create a complex security
scheme for their Access users, we can instead point out that Access has
built-in security features that does not require the developer to create
login forms or store data in a complex security table.

Hope that helps. We're not being nosy, just want to get the full picture so
we can give the BEST answer for the circumstances.

 

[Guest]

Hi Rick!

thanks a lot for your reply. Actually, what I am trying to accomplish is
have users input their issues, data in a Word or Excel form which will update
automatically the database (add new records to the database). I know this is
feasible with InfoPath, but not every MS Office user has InfoPath. Does that
help?

Thanks, waiting for your comments/solution

Jean

 

[Rick B]

That helps. I don't know a way to do this, but that information should help
you get a better answer.

I tried to develop a similar system a couple of years ago when we wanted our
employees to have some limited access to a database, but we did not want to
license Access for 30 users or purchase the developer edition. At that
time, I was told we were pretty much out of luck. The answer seemed to be
to buy the developer edition, or to import data from an Excel spreadsheet
each evening (which would work).

Hopefully an MVP will jump in with some comments since your desire is
clearly stated in your most recent post.

 

[Norman Yuan]

It is fairly easy to udate database, be it *.mdb or other database (SQL
Server, MySQL...), from Word/Excel by wirting some VBA code inside Word or
Excel.

The common approach would be building a UserForm in Word/Excel to allow user
to enter data in controlled fashion. Once user click "OK" button, the data
entered into the UserForm would be updated to *.doc/*.xls file and to the
backend database.

If you search NG for Word/Excel, there would be a lot information on similar
topics. The bottom line is you know how to program Word/Excel VBA.

 

[Dave Emmert]

Sure, with creativity. But you need to think about whether you are going to
push or pull (whether word or excel pushes the data to access or access
pulls the data) and about the format that the data is stored in.

I think that it is easier to pull the data from an excel file. I have used
both vba to create a recordset from the spreadsheet and pulled the desired
data and inserted into the table with a SQL statement. If this is a one
time action, you can use import a spreadsheet as a table. Additionally, if
you want to maintain the data in the spreadsheet you can just link to the
table.

As for Word, I've used a VBA routines (inside of word) parsing the data
between key terms and then sent it to a recordset. But I was working with
formatted reports. You could open the document inside of access (using the
word object) to do the same. A lot depends on how the data is stored in the
word document and it will require a bit of coding in vba.

Dave Emmert