Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2003
    Posts
    14

    Question Unanswered: Oracle export into Access

    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?

    Please let me know...

    Thanks

    Sherriff

  2. #2
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Oracle export into Access

    Sounds like you might exceed the limits of Microsoft Access.

    Why not just create another instance in Oracle.

  3. #3
    Join Date
    Aug 2003
    Posts
    14

    Reason for 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.......

    Sherriff

  4. #4
    Join Date
    Apr 2003
    Location
    NY
    Posts
    208

    Re: Reason for Access

    Not sure, but I think that exceeds the limits.

    Why not try personal oracle inplace of Access

  5. #5
    Join Date
    Aug 2003
    Posts
    14

    No can do

    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..

    Thanks

    Sherriff

  6. #6
    Join Date
    Jun 2002
    Posts
    3
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •