How do I link a changing daily Excel work sheet to an existing Access DB and be ableto write in both?
I have an existing Access 2000 DB. Everyday I'm sent an Excel sheet with available appointments for patients. I've linked the Excel to the DB but can't update from Access. If I update from Excel it shows the update in Access but shouldn't I be able to write in the linked table from Access? It seems to be a read only. It won't let me write from Access. What am I doning wrong?
Thanks for your help.
Angie
Thanks for your help.
Angie
ASKER
Hi Patrick, thanks for responding
I work in an office that coordinates care for patients (setting up appointments w multiple offices). The office that supplies us with appointments is completely separate from us and they send us their available appointments every morning via e-mail on an Excel sheet. They don't use Access at all. Our whole system is Access.
We have 6 end users and I want us to be able to keep up with what appointments that are being given away in real time (instead of having to ask each other have you used this appt time yet).
I am open to suggestions if you have one. You should know I don't know how to write code, doesn't mean I can't with a little help, just means it's unfamiliar and scary.
Angie
I work in an office that coordinates care for patients (setting up appointments w multiple offices). The office that supplies us with appointments is completely separate from us and they send us their available appointments every morning via e-mail on an Excel sheet. They don't use Access at all. Our whole system is Access.
We have 6 end users and I want us to be able to keep up with what appointments that are being given away in real time (instead of having to ask each other have you used this appt time yet).
I am open to suggestions if you have one. You should know I don't know how to write code, doesn't mean I can't with a little help, just means it's unfamiliar and scary.
Angie
Sounds to me like shared calendars in Outlook might be a better fit...
ASKER
Their appointments come out of their own appointments program which is not Outlook driven and typying 10 dates and times on an Excel sheet is a simple way for them to covey the information to me.
What if I link one specific Excel to our front end DB which lives on our server (we each have a copy of that front end on our desktops) and then daily I can copy paste the new appt dates and times from the e-mail they send me into that existing linked Excel? I don't need the prior days information because I will create a reoprt from that daily and distribute it.
However, I still need to be able to write in Access and have it update the Excel, which was the original problem. But what do you think?
Angie
What if I link one specific Excel to our front end DB which lives on our server (we each have a copy of that front end on our desktops) and then daily I can copy paste the new appt dates and times from the e-mail they send me into that existing linked Excel? I don't need the prior days information because I will create a reoprt from that daily and distribute it.
However, I still need to be able to write in Access and have it update the Excel, which was the original problem. But what do you think?
Angie
ASKER
Or if someone could just tell me how to make this linked Excel, write "able".
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Another option may be to simply IMPORT the data from Excel to a database table, manilate as required and then simply export the manipulated data you need from the table to a new excel spreadsheet.
Leigh
Leigh
ASKER
HI Guys,
Originally I was referencing Shelly Cashman from "Access 2003 Comprehensive Concepts and Techniques" which was an old text book of mine and in that she states "When an Ecxcel worksheet is linked, for example, the worksheet is not stored int the database. INstead Access simply establishes a connection t the worksheet so you can view or edit teh data in either Access or Excel. Any change mad in either one will be immediately visable in the other." and it goes on to give examples. But this is for 2003 and I work in 2000 so maybe there is still a way?
Angie
Originally I was referencing Shelly Cashman from "Access 2003 Comprehensive Concepts and Techniques" which was an old text book of mine and in that she states "When an Ecxcel worksheet is linked, for example, the worksheet is not stored int the database. INstead Access simply establishes a connection t the worksheet so you can view or edit teh data in either Access or Excel. Any change mad in either one will be immediately visable in the other." and it goes on to give examples. But this is for 2003 and I work in 2000 so maybe there is still a way?
Angie
What version of Excel are you using?
ASKER
Access 2000 file format
ASKER
No thats wrong, it's 2003
What version of Excel is installed on your system and what is the workbook file format?
You have already stated that you are using Access2000. I'm asking about the Excel side of the problem.
You have already stated that you are using Access2000. I'm asking about the Excel side of the problem.
ASKER
It's Excel 2003. But you now have me asking myself about the actual Access version. It is also in 2003 when I check "about" when I open Access but at that top of the page it says "Access 2000 file fomat" and maybe that's because that was the original version but now it's running in 2003? Is that right?
How do I tell what workbook file format Excel is in?
How do I tell what workbook file format Excel is in?
It probably doesn't matter, since your are using Office 2003 products. With the post-lawsuit service patch, the updating of Excel (from Access) via the ODBC connection is not possible.
Hi Angie
If I recall correctly, the disabling patches were applied to Office XP (2002) SP3 and Office 2003 SP2. If you are using Access 2003 you will certainly be affected by this restriction, no matter what the format of your MDB file.
The book to which you refer would have been written before all this became a problem.
My advice is to look for another solution, such as the ones I have suggested.
--
Graham
If I recall correctly, the disabling patches were applied to Office XP (2002) SP3 and Office 2003 SP2. If you are using Access 2003 you will certainly be affected by this restriction, no matter what the format of your MDB file.
The book to which you refer would have been written before all this became a problem.
My advice is to look for another solution, such as the ones I have suggested.
--
Graham
Hi,
Did you see Leigh's suggestion above?
"Another option may be to simply IMPORT the data from Excel to a database table, manilate as required and then simply export the manipulated data you need from the table to a new excel spreadsheet."
In fact, do you even need to feed the data back into Excel at all?
Did you see Leigh's suggestion above?
"Another option may be to simply IMPORT the data from Excel to a database table, manilate as required and then simply export the manipulated data you need from the table to a new excel spreadsheet."
In fact, do you even need to feed the data back into Excel at all?
ASKER
What I ended up doing is:
I copy the Excel they send in an e-mail everday and paste it in our folder on the server.
I created a short cut to that Excel and kept it on the server as well.
We have 5 end users. I put that short cut on their desktops and that way they can access the Excel thru the short cut and see what appts are available.
They choose an available appt, enter the info, close the Excel and it's updated for the next person who needs to sched a pt.
That seems to be working fine. Its not what I wanted but it's working. And the frustrating thing is that it should work according to the books I have but I didn't know anything about the legal issues.
Thanks for all your advise.
Angie
I copy the Excel they send in an e-mail everday and paste it in our folder on the server.
I created a short cut to that Excel and kept it on the server as well.
We have 5 end users. I put that short cut on their desktops and that way they can access the Excel thru the short cut and see what appts are available.
They choose an available appt, enter the info, close the Excel and it's updated for the next person who needs to sched a pt.
That seems to be working fine. Its not what I wanted but it's working. And the frustrating thing is that it should work according to the books I have but I didn't know anything about the legal issues.
Thanks for all your advise.
Angie
Hi Angie
I can see that the Excel method would have many drawbacks. What happens if two users want to add an appointment at the same time? What about the time when one user opens the Excel and then gets a phone call or goes off to lunch?
I think you would have a much more robust system if you import the daily Excel into a shared Access back-end and provide your users with a simple front-end to view and add appointments. This would be a real multi-user solution.
--
Graham
I can see that the Excel method would have many drawbacks. What happens if two users want to add an appointment at the same time? What about the time when one user opens the Excel and then gets a phone call or goes off to lunch?
I think you would have a much more robust system if you import the daily Excel into a shared Access back-end and provide your users with a simple front-end to view and add appointments. This would be a real multi-user solution.
--
Graham
ASKER
There is seldom more than 2 or 3 people working in this office at one time. If someone is in the Excel, entering an appt and someone else opens it up, it comes up read only. It just takes a moment for that person to enter the Name DOB and their initials. It works great so far.
`However, just to clarify, what you are suggesting is creating an entirely new DB (Frontend-Backend) just for the appointments?
Angie
`However, just to clarify, what you are suggesting is creating an entirely new DB (Frontend-Backend) just for the appointments?
Angie
Hi Angie
<<However, just to clarify, what you are suggesting is creating an entirely new DB (Frontend-Backend) just for the appointments?>>
No, my understanding is that you already have all this data in Access as part of a larger system:
<<I have an existing Access 2000 DB>>
I understand that you were linking the Excel spreadsheet to this database but could not update it:
<<It seems to be a read only. It won't let me write from Access>>
I also understand that all the users in your office/department use and are familiar with Access:
<<The office that supplies us with appointments is completely separate from us and they send us their available appointments every morning via e-mail on an Excel sheet. They don't use Access at all. Our whole system is Access>>
So, what I'm suggesting is that you import the daily appointment data that you receive every morning into your main Access back-end, and that you give your users the ability to directly view and edit the appointments, either by:
a) adding that capability to the front-end your users already use, or
b) creating a very small, cut-down front-end to provide this capability
--
Graham
<<However, just to clarify, what you are suggesting is creating an entirely new DB (Frontend-Backend) just for the appointments?>>
No, my understanding is that you already have all this data in Access as part of a larger system:
<<I have an existing Access 2000 DB>>
I understand that you were linking the Excel spreadsheet to this database but could not update it:
<<It seems to be a read only. It won't let me write from Access>>
I also understand that all the users in your office/department use and are familiar with Access:
<<The office that supplies us with appointments is completely separate from us and they send us their available appointments every morning via e-mail on an Excel sheet. They don't use Access at all. Our whole system is Access>>
So, what I'm suggesting is that you import the daily appointment data that you receive every morning into your main Access back-end, and that you give your users the ability to directly view and edit the appointments, either by:
a) adding that capability to the front-end your users already use, or
b) creating a very small, cut-down front-end to provide this capability
--
Graham
With respect, trying to do two-way updates like this is not a good strategy for success.
Why are you getting your updated info via Excel? What process generates that information? Can that process not simply work off of Access?
Patrick