Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import tables using VBA (Access2003)

    I have a very big issue with a far-removed Server. When tables brought locally into my Access database - Report is running in 3 min.
    When I am hitting linked tables - it takes hours for the Report to run.
    Finally we had decided to write a code to Import tables into database. About 6 or 7 Oracle tables.

    Except I have no idea how to do this and if there any restrictions etc.

    Please, advise.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import tables using VBA (Access2003)

    Since you already have linked tables, you could create make-table queries or append queries to import the data.
    You can easily run make-table queries and append queries in code using DoCmd.OpenQuery.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import tables using VBA (Access2003)

    I can, can't I? Why haven't I thought of that? THANKS, Hans.

  4. #4
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import tables using VBA (Access2003)

    However if I wanted to Import the table, how difficult would that be?
    My Team wants to know. Thanks

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import tables using VBA (Access2003)

    You can create a user or machine DSN for Oracle in the Data Sources (ODBC) control panel.
    You can then use DoCmd.TransferDatabase to import an Oracle table:
    <code>
    DoCmd.TransferDatabase acImport, "ODBC Database", _
    "ODBC;DSN=dsnname;UID=username;PWD=password;SERVER=servername", _
    acTable, "oracletablename", "accesstablename"
    </code>
    Substitute the appropriate strings for the italicized words.

    If you leave username and password blank, you'll be prompted to log in.

  6. #6
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import tables using VBA (Access2003)

    It is good to know this way - just in case...
    So far your other advise worked just splendidly! Thanks

    I want to ask though...
    I have Oracle tables linked. I am running Make Table fine. Dtatbase residing on Share drive.
    However when I am asking my assiciate to run it - it is prompting them for a password.
    They do have permission. I would like them not to have to enter a password. Can I do anything about it? Thanks again.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import tables using VBA (Access2003)

    I cannot help you with that, I don't work with Oracle myself. Sorry!

  8. #8
    5 Star Lounger
    Join Date
    Jan 2005
    Posts
    614
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import tables using VBA (Access2003)

    No problem, thanks anyway. For your time.

    Happy Holidays!

Posting Permissions

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