Results 1 to 5 of 5
  1. #1
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Run query at start, passing user id and password (A2003 SP-2)

    Hi,
    I have a database, which links to an Oracle database through ODBC.
    Every morning, when I don't forget, I open the Access db and refresh a number of tables, using a number of queries, based on content in the linked Oracle tables.
    When I select the first query I am asked for my Oracle user id and password. The query and any subsequent queries then run and return my data.
    All of the queries are accessed through a number of command buttons which group like queries together, as certain groups of data need to be refreshed at different intervals.
    What I'd like to be able to do is to automatically pass the Oracle user id and password, and the procedure which houses the grouped queries when I open the Access db.
    How would I be able to cater for the different refresh intervals.
    Would batch files, which I could schedule using the NT scheduler, be able to contain the required connection string, procedure, queries etc. for the different intervals?
    Our OS is Win XP Pro under Novell.
    Any suggestions welcome.
    Zinger

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    Manassas, Virginia, USA
    Posts
    363
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run query at start, passing user id and password (A2003 SP-2)

    When you create a link to the Oracle table there should be a checkbox asking if you want to remember the password. I was having this same problem until someone through the lounge gave me this info. Before that when I linked I never even paid attention to this being an option. I went back and created new links and indicated I wanted Access to remember the password and now everything works automatically.
    Judy Jones, Computer Training Specialist
    Manassas, Virginia

  3. #3
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run query at start, passing user id and password (A2003 SP-2)

    Thanks Judy,
    Yes, there is a check box at the Oracle login id and checking that does remember my credentials.
    The other part to the question is how do I automatically run a procedure, which only contains a number of update queries, whenever I, or now anyone, starts the Access db?
    Zinger

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

    Re: Run query at start, passing user id and password (A2003 SP-2)

    There are two possibilities:

    1) Change your procedure from a Sub to a Function. It doesn't need to return a value.
    Create a macro named AutoExec with one action of type RunCode, and specify the function name followed by () in the Function argument, for example
    <code>
    MyFunction()
    </code>
    The AutoExec macro will be executed each time the database is opened.

    2) Set a form to be opened automatically:
    - Select Tools | Startup...
    - Select the form in the Display Form/PAge dropdown list.
    - Click OK.
    Call the procedure you want to execute in the On Open or On Load event of the startup form.

    Note: if you want to suppress execution of the AutoExec macro or opening of the startup form, hold down the Shift key while opening the database.

  5. #5
    2 Star Lounger
    Join Date
    Feb 2003
    Location
    Melbourne, Victoria, Australia
    Posts
    139
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Run query at start, passing user id and password (A2003 SP-2)

    Thanks Hans
    I've followed your suggestion, and have attached all queries to the macro, immaterial of their frequency.
    Regards
    Zinger

Posting Permissions

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