Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Updating tables

Posted on 2000-03-23
10
Medium Priority
?
144 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 

Accepted Solution

by:
Salva earned 400 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
 

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

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
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…
Suggested Courses

578 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