Solved

Updating tables

Posted on 2000-03-23
10
135 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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

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
 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

820 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