Solved

Updating tables

Posted on 2000-03-23
10
134 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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 

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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
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.
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

813 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

19 Experts available now in Live!

Get 1:1 Help Now