Oracle and Access Data Issue

[moepower1]

I have a need to import data from an Oracle table into an existing Access table with duplicate structure. I don't want to use the link feature because I want to protect the Oracle DB from Access users and I need the Oracle data to merge with existing data in Access. Everytime I do a "Get External Data", Access creates a new table. Is there anyway around this?

I'm new to Access so please bare with me if this question is too simple.

Thanks

 

[TheInsider]

Hi,
The only way to do this is to link the table, then revoke privilege from it for anyone who shouldn't have access to it. You would do this from the menu Tools->Security->User and Group (Permissions/Accounts). Rob Marriott
robert_a_marriott@yahoo.com

Hire me! Full-time, contract, whatever...shhh don't let my current employer know I said that.

 

[moepower]

Rob,

If I use the "link" option, 1) could I differentiate what data was entered in Access and what data came from Oracle?

2) If I have new data from Oracle, can the link update the Access table without overwriting the data entered manually thru Access?

3) Is it possbile to create a duplicate table in Access and write a query to pull the information from Oracle and insert them into the Access table?

Thanks a million,

 

[PaulF]

I Link to Oracle in several of my databases... I use an Update query to capture the lastest data from the Oracle database and update the tables in Access. If you set your queries and forms up properly, you can use both, and limit what fields can be edited (use the Lock Property for the textbox in a form), or use a subform for the Oracle data and set AllowEdits, AllowAdds, AllowDeletes, and DataEntry to No or False. You can set the Hidden Attribute for the Oracle table so it doesn't appear in the Database Window (Unless someone goes to the Options window and selects to show Hidden Objects). And you can limit access to the table using Security. But if you want to import the data instead of Linking.... You can send a DoCmd.DeleteObject acTable, "TableName"
command prior to running the Import.. This will delete the existing table.

HTH
PaulF

 

[moepower]

Thanks, I'll try your suggestion.