I have a table in Oracle that is updated with about 1,000 rows each day. Currently I link this table to Access so I can run several reports using tables in Access that tie back to this Oracle table.
Two problems with this method:
1) When I am offline from my company intranet the database is pretty much useless as it cannot connect to the Oracle table - would like to be able to play around with the database whenever I want
2) Not sure I agree with some of the format output on the Oracle table, would like to convert text to date.
What is the simplest way to import the data from this table on a daily basis? I only want to pull forward new records from the Oracle table, behind the scenes and automated if possible.
The only two methods I see available are:
1) Import the whole table one time only
2) Link to the table
The first option allows me to work offline but I can't get new records appended to the table
The second option is what I am currently doing
Help?
Hi shadestreet,
Maybe I'm not understanding the issue you're facing. If you make a copy of the table (and keep that in your Access database), why can't you maintain the "up-to-date-ness" of that copied table with an append query while the master table (from Oracle) is available?
Cheers,
Docendo discimus.
Please consider:
- Thanking those who helped you. Click the reputation icon
in the contributor's post and add Reputation.
- Cleaning up when you're done. Mark your thread [SOLVED] if you received your answer.
Thanks, just a bit of warning, I am a complete novice with Access. I know the basics of what is possible but not the "know how" to achieve what I want to do.
In this case I thought of using an append query but got stuck pretty early on this step.
I know the SQL should look like this:
INSERT INTO TBL_ORACLE_COPY (Column1, Column2,...ColumnN)
SELECT Column1, Column2, ... ColumnN
FROM TBL_ORIGINAL_ORACLE_TABLE
WHERE date = sysdate-1 {whatever syntax to get me previous date}
HOWEVER, I don't know how to define the Oracle table to point Access in the right direction. Do I need to put the server name? DSN name? Not sure how to tell Access to connect to the Oracle database through SQL.
Also, suppose I get this to work (and that would be great), can this be easily automated to run in the background say every morning at 6 a.m.?
Hey i m telling you about a single module about how to connect Access with Oracle database by using VBA code:
First of all you'll have to create an ODBC connection to your Oracle database using the {Microsoft ODBC for Oracle} driver.
To do this, go to the "Data Sources ODBC" icon under the Control Panel and create a new Data Source using the {Microsoft ODBC for Oracle} driver.
SEE THE Attachment:
Then Set up ODBC connection.
Here is the example with the particular details you can change according to your settings.we've setup the Data Source with a name of AAAA, with a user name of BBBB, and an Oracle server called CCCC. You'll need to configure the ODBC connection.
See the attachment
Then create a module and usr this code:
Try this Hope it helps.Code:Function OracleConnect() As Boolean Dim ws As Workspace Dim db As Database Dim LConnect As String On Error GoTo Err_Execute 'Use {Microsoft ODBC for Oracle} ODBC connection LConnect = "ODBC;DSN=AAAA;UID=BBBB;PWD=DDDD;SERVER=CCCC" 'Point to the current workspace Set ws = DBEngine.Workspaces(0) 'Connect to Oracle Set db = ws.OpenDatabase("", False, True, LConnect) db.Close OracleConnect = True Exit Function Err_Execute: MsgBox "Connecting to Oracle failed." OracleConnect = False End Function
Last edited by ExlGuru; 04-29-2009 at 02:42 AM.
ExlGuru
ExclGuru - thank you for the time you spent helping me. I have the ODBC conncection setup and added that module to my DB, but not sure where to go from here. I wasn't exactly clear what this module will do for me, haven't used them yet. Right now I have the module in my DB and when I click on it I get the design view.
next step?
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks