Hi, I am trying to export our entire Oracle database into an Access database for testing purposes. I have been just linking our manditory tables in Oracle to Access and creating make-table queries, then setting up my indexes. This works ok for now except that I really need to get all 800+ tables into Access. Making all these queries would be very time consuming. Is there an Oracle database dump to Access?
We use a program that runs in Oracle but also has an export/import utility that we use for field staff that will only run Access. We can do a full export through this utility but it takes days and days to run......Our Oracle database runs about 6 gigs in size. Not sure about the limits in Access.......
Our software for allowing the users to look up records, assets etc. is only made for Access. Usually a user would only export specific items, maybe 10 records a day for their work, then import back to Oracle with this software. But some need the entire database on hand just for viewing. I found out the limits are 1 gig for Access 97 and 2 gigs for 2000. We are currently just under a gig so I guess we are pushing the limits and are out of luck..
The limit is 2gb per MDB file. To get around that limit, import your Oracle tables in logical groups to multiple MDB files, all under the 2gb size limit. Then, create a "master" MDB file and link all the tables from the multiple MDB files into that file. It will appear to your users, or the application, that all of the tables are in that one MDB file, but it will actually be quite small.
I create such an "offline" snapshot every night using DoCmd.TransferDatabase through a DSN-less ADO connection. There's probably many other ways to do the same thing -- this works and is reasonably fast.