Solved

Updating tables

Posted on 2000-03-23
10
133 Views
Last Modified: 2010-05-02
I have this issue,I have an Oracle DB and i want to update every day a Access DB with this Oracle DB,how do you recommend me to do it?
0
Comment
Question by:enryo
  • 4
  • 3
  • 3
10 Comments
 

Author Comment

by:enryo
ID: 2649415
The Oracle DB is on the server side an the Access DB on the client side
0
 
LVL 12

Expert Comment

by:mark2150
ID: 2649431
Run ODBC SQL query on oracle table and transfer data to Access table. Difference is in OpenDatabase statement. Have two databases, one local, one remote. Then you can pull a recordset from one and loop thru it inserting into the other. Not particularly elegant but should work well.

You might be able to do an Insert Into from the Oracle into the access and have it happen in one go. Never tried it tho.

M
0
 

Author Comment

by:enryo
ID: 2649450
Do you know about a instruction that can make an automatic comparation of two db or tables , what can you comment about Update method?
0
 

Accepted Solution

by:
Salva earned 200 total points
ID: 2649547
What about an ODBC table "vinculation?" with ACCESS.
 
Create a new db and click with the right button over the db window and select the option above "import" (i don't know how to say it in English, maybe "link or vinculate table"). With this option you can have an always update view of your Oracle DB.

I hope it will help you.

0
 
LVL 12

Expert Comment

by:mark2150
ID: 2649609
Automatic comparisions... Hmm. You can get into pretty deep mud fairly quickly. How many records are you talking about here? If its relatively few (no more than several thousand) then just drop the table and refill every time. Is much simpler and more reliable than having a "last update time" field and having to run a comparision.

Salva, I think he wants to do this programmatically and not from the menu. I'm also fairly sure that you can't use the Access menu system to import from an external ODBC database. Programmatically you should be able to do it but the access development environment doesn't seem to have a method to connect to Oracle.

M
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 

Author Comment

by:enryo
ID: 2649666
Im talking about 1500 records the first time and aprox. 100's after;I was thinking on making replicas and synchronizing them ,let me know your comments.
0
 

Expert Comment

by:Salva
ID: 2649690
to mark2150,

I know he want to do it programmatically, but you only need to create de "linked access db" once, and then use it from vb.

Access will read the data from the Oracle server thru ODBC System DSN connection, and it will be always updated.

Sorry but the menu option i refer to is the one BELOW the "import" option. In spanish is "Vincular tablas".




0
 
LVL 12

Expert Comment

by:mark2150
ID: 2649691
For 1500-2000 records just drop the table and refill. It'll be faster and easier than trying to keep in sync. The INSERT INTO should work so you can refill in a single command.

M
0
 

Author Comment

by:enryo
ID: 2649825
I appreciate your comment Salva but i really dont think that will work for me because the two tables that i want to synchronize dont have the same structure.
0
 

Expert Comment

by:Salva
ID: 2649963
Ok. Then, create a local view joining the linked table and your local table.

Linked tables are not in your local machine it's only a "snapshot" of the remote db.

I dont't know if this helps you but her in my company we often use this kind of things with little local applications.

0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now