Linking Excel t2007o Access 2007

[mhemat]

I made a 8 sheets template on excel 2007. This workbook is used to input new patient info and is called WBA.The user plugs in info such as last name, first name, birthday, race, drug of choice, etc...the cells on this template. Then the workbook will be saved under the patient name in a folder. How can I link or transfer this data automatically to Access 2007 database so I can make a query in access? When responding please explain it so I can understand. I know some with Excel but not that much with Access. Your help is greatly appriciated.

 

[Norie]

Do you have to have the data spread over multiple files and folders?

That's not really conducive to doing what you want to do.

 

[mhemat]

Let me explaine what I want to do. I work in a rehab and when we bring in a new patient, the basic demographic, health, legal, family and social information need to be input in a userform or a template. Then the template will be saved under the patient name in a folder called "Clients". Evantually, there would be hundreds or thousands of files in that folder. There are about 10 users who use this template to do what I just described above. I like certain information from this template (with 8 tabs or sheets) to be registered or exported to a database with aquery option to run reports. For example: 1- "Bed List" will show each bed number and the patient's name that sleeps on that bed, the Counselor's name, and the date of admission. My question is Can I do this with using Excell 2007 to creat the template and how to export data from Excell to Access 2007, to use as database? I am sorry for asking too much.

 

[Joe4]

Sounds like what you really need is a Database. You can do that in Access. Create Forms which are linked to Tables, and you won't need to transfer any data at all. I would take Excel right out of the equation altogether.

 

[mhemat]

Thank you. I guess that is the best option. My question is, if I use access 2007 and than later I update to MS 2010 or newer, is that going to cause trouble with the database or the tables?

 

[Joe4]

It really isn't much different to Excel in that respect. Just like witg=h Excel, newer versions of Access usually do not have much trouble running stuff created on earlier versions (true with most Office programs). Of course, there are a few exceptions as occassionally Microsoft does take functionality away. But I would not say it is any more frequent with Access than it is for Excel.

Ironically, one of the biggest "takeaways" in the past decade that was the ability to update Excel data that is linked to Access from Access. Before Excel 2002, you used to be able to do that. But Microsoft lost a copyright/patent lawsuit, and you can no longer do that.

My personal philosophy is if it isn't necessary to use both Excel and Access together on a single project, then don't. If you can use one for the whole project (without giving up much of anything in doing so), then I think that is the way you want to go.