Automate Access Update data from Oracle.

Question

I have several access database that I run every morning. I have an odbc link to an oracle database and I run an append query to take the latest information from the oracle table and append it to the access table. I currently do this manually for each of the 5 database that I have created in Access. Is there a way for me to automate the append process so it happens in the morning before I get to work? What about the username and password I need to enter to gain access to Oracle database? There are also several prompts during the append that require me to answer YES to. Can that also be automated?

Thanks.

Answer 1

Hi pdcjlw,

>> There are also several prompts during the append that require me to answer YES to.

What are these prompts? Could it be avoided with vba code or could you use vba code to achieve your requirement without these prompts?

>> What about the username and password I need to enter to gain access to Oracle database?

With linking table, you could save the userid and password locally, you could refer the link below:
# How to store SQL database login IDs and passwords locally
https://support.microsoft.com/en-us/kb/209502

>> Is there a way for me to automate the append process so it happens in the morning before I get to work?
If there is a form which is always being shown in your application, you could use timer event.

>> have an odbc link to an oracle database and I run an append query to take the latest information from the oracle table and append it to the access table

Do you append data in access table or oracle table? In my option, the data in access and oracle would keep the same if you used linked table. There is no job in Access application, I think you could try whether this could be achieved by job in Oracle.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 2

The oracle database only keeps the current count. I copy the current count into an Access database each day so I can produce trend charts. That is why I need to run an update from oracle to Access.

Answer 3

Hi pdcjlw,

What do you mean with current count? Do you mean records in oracle table? If so, I suggest you try to link table from Access database to Oracle table.

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.

Answer 4

The Oracle database holds student records like what class they are enrolled. The database only holds the current status. It does not hold any history. So if I want to know how many students were registered for say Bio 1000 on a certain day, the oracle database does not have that information. It can only tell me the number of students that are enrolled in Bio 1000 today. So every day I pull the current enrollment data from Oracle and store it in an Access database. That way I can show trends over time on the number of students that have enrolled in Bio 1000. It is that daily pull of records from Oracle and recording them in Access I want to automate.

Answer 5

Hi pdcjlw,

With my first reply, could you link from Access database to table which stored the data you want to pull from Oracle?

Best Regards,

Edward

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.