Solved

Updating tables

Posted on 2000-03-23
10
132 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
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…
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…

757 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

21 Experts available now in Live!

Get 1:1 Help Now