Results 1 to 6 of 6
  1. #1
    Join Date
    Aug 2007
    Posts
    15

    Unanswered: Problem Connecting to Oracle From Access While Running Access Macro

    I have created a link table in Access to a table in Oracle. I have also created a macro in Access that uses a number of select queries that get records from the linked table. I would like to run Access and the macro from the command line. The reason for wanting to run Access from the command line is so I can ultimatly create a Windows Scheduled Task that will run Access and my macro once a day shortly after midnight.

    The problem I am having is that the first time the linked table is accessed from a query in the macro Oracle wants a username/password/hoststring. How do I tell Access what the login information for connecting to Oracle???

    Thanks very much any help!

  2. #2
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Long time ago

    I think it is /x macro_name

    so
    c:\data.mdb /x macro_name
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  3. #3
    Join Date
    Aug 2007
    Posts
    15

    No Help

    Using /x when envolking access will run the macro but this does not solve the problem. I am able to start access from the command and run the macro with the /x parameter. However, when the macro runs it calls queries that access the linked table which in turn askes for a user name and password by displaying a login dialog. How can I get around this? Is there a way of telling access to use a username and password I tell it?

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ya know... If you create a macro and call it "AutoExec" it will run on start up, so you can actually avoid using the "/x".

    Also, did you know you can tell access the username and password in the connection string to the database? http://www.connectionstrings.com/?carrier=oracle
    George
    Home | Blog

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,418
    Provided Answers: 7
    Try this drop one of the link tables

    then link it again I think there should be tick box that says "Save password" tick that
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Aug 2007
    Posts
    15

    Talking Good suggestion

    Sweet! This is exactly what I was looking for. I dropped the table. Recreated the table this time checking the box 'Save password' and the login dialog no longer appears.

    Now I have a new problem. Near the end of the macro I call SendObject to send an email. A security type dialog is displayed asking if you really want to send the email or not. I need to suppress this dialog. I looks as if MSOutlook is putting up this dialog. I may have to post something to the Outlook forum.

    Fix this last problem and I will be finished with this project...

Posting Permissions

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