MS Access data into Oracle db solutions?

[geralf]

Hi,

I need some advice transfering data from MS Access to an Oracle db. The two databases are not on the same networ, since this is two different companies. The Access data should also be automated into the Oracle db. I have no knowledge of Oracle db's, so I would like to get some point of views on how to do this, if it's possible.If there are other suggestions on how to do this, I'd be most grateful for any advice.

Thanks in advance.

 

[Pat Hartman]

Without the two db's being on the same network, I'm not sure how you would automate the process. With the Oracle db available from the same network as the Access db, the best solution is to actually link the Oracle tables and update them directly. A disconnected update is more difficult because your update process will need to be able to distinguish between add, change, and delete actions. It could also be a problem if there is more than a single remote process updating the Oracle db. Which update takes presidence?

I think we need more info for a good solution.

 

[geralf]

Hi Pat!

I know it might be difficult to automate the process entirely, but it would be great to know if it's possible. The data I want to make available for the other company is metal analysis data. This data will only be 'sent' from the Access database to the Oracle database. I wondered if there could be a possibility to publish the Access db to the Internet, and the Oracle db could access the data. If the Oracle db could be automated to look for new data, based on a time scheme or time interval to access the web page the Access data is located, would be great to know. The Oracle db does not need to update, delete or add records, only read the data. The Access db is the only db creating the data. The data does never change except for new records beingadded when analysis is done.

The Oracle automation bit won't be my job. My job would be to make the Access data available.

Another idea could be to automate an e-mail message with the data attached in a file of suitable format for the Oracle db. Can the Oracle db be automated to look for specific mails, and access the data in the attachement?

I appreciaye your help very much, and hoope I have given some more helpful details.

Thanks for your reply!

 

[Pat Hartman]

So your Access data just needs to be appended to the Oracle tables? How often? What happens to the data in the Access db after it is "sent" to the Oracle db? Who is writing the Oracle piece? Will it also be written as an Access application? Is there any error checking? What will happen if duplicate records are sent? Would you even be able to identify a duplicate "send".

You have a substantial amount of analysis yet to do. Access can handle both sides of the activity with two separate databases. The "sending" db can extract data periodically (use the windows scheduler) and FTP or email the text file to the other site. At that location an Access db can be scheduled to run (use the windows scheduler) at specific times to pick up the text file and import it. The receiving db can be linked to the Oracle tables and so it is a simple append query to get the text file data into the Oracle tables.

 

[geralf]

>So your Access data just needs to be appended to the Oracle tables?
Yes

>How often?
Will probably happen after each analysis is run, since there are other events happening in the Access 'send' db that can trigger the transfer process. An alternative could be on a periodic interval which could be once to 4 times a day. I find the 'event-triggering' the most souitable.....I think.

>What happens to the data in the Access db after it is "sent" to the Oracle >db?
Nothing, it will just be stored in the tables. No updates or deletions.
They must be stored in case of some future reference or use,

>Who is writing the Oracle piece?
The receiving company will handle that. As mentioned, I do not know Oracle db's. Is Oracle a BE/FE database or does it only handle the data?

>Will it also be written as an Access application?
I doubt it. What can it be written in?

>Is there any error checking?
If possible, yes.

>Would you even be able to identify a duplicate "send".
No, not a duplicate send, but duplicate records can be identified. There should not be a duplicate send, but this is no sure case. Therfore the best solution here willo be to have the 'receiving db' check the data for dupes.

>What will happen if duplicate records are sent?
Well, if they exists in the Oracle tables, don't append them.

Thanks Pat

 

[Pat Hartman]

Oracle is just a database. The fe could be developed in anything, including Access. Since you are not responsible for building the Oracle piece, why not ask them how they would prefer to receive the data. Fixed or delimited text files, and spreadsheets can be exported from Access without coding. Look up TransferText and TransferSpreadsheet. Both have a Method (VBA) and Action (Macro) entry. Although you should read the Action help entries because they contain information not included in the Method help entries, you should use the VBA solution rather than a macro. Macros should be avoided at all costs since there is no error checking available. As I said, the export can send email or write a file to a remote site where the client can pick it up.

Make sure that your table has a unique primary key. An autonumber would be perfect. Include this autonumber field in any export. That will make duplicates very easy for the client to identify. This is especially important since you will probably be exporting the data manually. If the data exchange is critical enough, you might want to develop some sort of audit trail such that each time you export, you log the first and last autnumbers and the number of rows. You can then export the audit record as an additional file. That way the client can make sure that he has received the correct file and not missed any. Don't forget that autonumbers may not be contiguous so don't base any audit on expecting specific autonumber values. Min, Max, and count will suffice.

 

[geralf]

Hi Pat!
Thank you very much for your time and advice in this matter.

I'm having a meeting with the people who will receive the data next week. 'll see what we fall down on. Foe me it is important to know if it's possible to do, so I don't go down a dead end.

I share your view when it comes to macros. I seldom use them, or I convert them to VBA.

I have one issue left. I see from my thread title that the data comes from a Access table, but that isn't so. The analysis data is output to a dBase IV table. I have linked to this table from my Access app. I can't edit the structure of the dBase table, since this is done by the spectograph software. I've tried importing the dBase table, make the necessary changes (add primary key) and exported back to dBase format. When running new analysis the data get's corrupted, so I can't do it that way. The dBase table has a unique key, but it's not defined as a primary key. How can I go about this?

Sorry for not being clear on this.

Thanks again for your help. Much appreciated.