Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    53

    Talking Unanswered: Linking to Oracle DSN automatically

    Hello Everyone,

    I'm truly stuck.

    My small Access database is linked to mulitple tables in my large Oracle database.

    I want to have the Oracle database update the Access Database on a daily basis.

    This works great for my needs, but I want to do it "behind the scenes" multiple times daily.

    I set up the Access Database to launch multiple times daily using the autoexec feature to set off the job.

    The problem I have is that it prompts me for my Oracle Database (DSN)Password each time it connects to Oracle.

    Is there a way I can code around this prompt. I have coded a module to run at autoexec that is connecting to my Oracle Database when the job runs, but I still am prompted for the password when the update query runs.

    I am using the following code in my module.

    Dim oConn as New Connection

    oConn.Open "DSN=mySystemDSN;" & _
    "Uid=myUsername;" & _
    "Pwd=myPassword

    (I have tested this by creating a recordset)


    Thanks for any help you can offer
    Jason
    Jason

  2. #2
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    If you are using Access 2000 and up, you can do this via an ADO connection. Not sure if this will work with ADO < 2.5.

    If you are connecting to multiple regions, you may need to use some table logic for storing them.

    You can use:
    Provider=MSDAORA;User ID=YourUID;Password=YourPass;Data Source=YourDataSource;

    (Data Source s/b what the DSN is using.)

    Set up a connection string that opens using the line above, then do whatever it is you do with your oracle data.

    You may want to put in some retries logic. What I did was create a retries constant, say 1000, and a counter. Then just before I tried to open the connection, set up a label, like 'Try_Again:'. This in tandem with an error handler that checks for 'Err.Number = -2147467259 And Err.Description Like "Oracle error occurred*"'

    I compare the counter to my constant, and if it's below, I increment the counter and resume at the label.

    HTH.
    All code ADO/ADOX unless otherwise specified.
    Mike.

  3. #3
    Join Date
    Jan 2004
    Posts
    53
    I tried using the ADO as well

    When the query begins it still prompts me for the password.

    This is what the prompt looks like.

    ORACLE 8 ODBC Driver Connect

    Service Name = xxxxx
    User Name = xxxxx
    Password = xxxxx
    Jason

  4. #4
    Join Date
    Jan 2004
    Posts
    1

    passthrough query

    I don't know if this is the best option, but I create a passthrough query that lets me store the connection password, and add it to my autoexec macro. This keeps it from prompting when using the odbc connection later in the db.

    Create a passthrough query in query design mode. (not in a code module) In query design mode, select query, sql specific, pass-through. It changes you design window to one like the sql view query design windo. Now write a short dummy query like "select * from dual." Select view properties and click on the elipse in the "odbc connection str" field it will let you specify the odbc connect graphically and display it in the field as text.

    Again I put run this passthrough query in my autoexec to prevent it asking for a password.

  5. #5
    Join Date
    Sep 2003
    Location
    T.O.
    Posts
    326
    This is the crux of what I use, where Nz(ctlRegion.ItemData(varItmRegion), "") pulls an appropriate connection string from a listbox linked to a table. I don't get the prompt for a password. Please keep in mind that this is on a form and not a macro. I don't really believe in macros. Try to cull the section that connects, then you can base a recordset on that connection which you can use to populate a table, if those are your needs.:

    Private Sub cmdRunScript_Click()
    On Error GoTo cmdRunScript_Err
    Const MAX_RETRIES As Integer = 10000
    Dim cnn As New ADODB.Connection
    Dim i As Integer, lngCount As Long
    Dim rst As New ADODB.Recordset
    i = 0
    ODBC_Try_Again:
    cnn.CursorLocation = adUseServer
    cnn.Open Nz(ctlRegion.ItemData(varItmRegion), "")
    ' DETERMINE IF THIS IS A SELECT STATEMENT OR AN EXECUTE STATEMENT
    strScript = rstScript(0)

    If Not IsNull(strScript) Then
    If Left(Trim(strScript), InStr(Trim(strScript), " ") - 1) = "SELECT" Then
    boolSelect = True
    Else
    boolSelect = False
    End If
    End If

    If boolSelect Then
    rst.CursorLocation = adUseServer
    rst.Open UCase(strScript), cnn, adOpenStatic
    If Not (rst.EOF And rst.BOF) Then
    strMsgScript = strMsgScript & strScript & ": " & rst(0).Value & vbCrLf
    Else
    strMsgScript = strMsgScript & strScript & ": 0" & vbCrLf
    End If
    rst.Close
    Else ' NOT A SQL STATEMENT BEGINNING WITH SELECT
    Dim lngAffected As Long
    cnn.Execute strScript, lngAffected
    strMsgScript = strMsgScript & strScript & ": " & lngAffected & vbCrLf
    End If

    cmdRunScript_Exit:

    Set cnn = Nothing
    Exit Sub
    cmdRunScript_Err:

    Debug.Print "Err: " & Err.Number & vbCrLf & "Desc: " & Err.Description
    If Err.Number = -2147217900 And Err.Description Like "ORA-00911: invalid character*" Then
    MsgBox "Invalid Character in SQL Script"
    ElseIf Err.Number = -2147467259 And Err.Description Like "Oracle error occurred*" Then ' RETRY
    If i = MAX_RETRIES Then
    MsgBox "Cannot seem to link after 100 tries..." & Chr(13) & "Exiting"
    Resume cmdRunScript_Exit
    End If
    i = i + 1
    Resume ODBC_Try_Again
    ElseIf Err.Number = -2147217873 And Err.Description Like "*unique constraint*" Then
    Resume Next
    Else
    MsgBox "Err: " & Err.Description & vbCrLf & "Num: " & Err.Number
    End If
    Resume cmdRunScript_Exit
    End Sub
    All code ADO/ADOX unless otherwise specified.
    Mike.

  6. #6
    Join Date
    Jan 2004
    Posts
    53
    Thanks so much to everyone, I ending up adding it to the actual DSN like this in the user name = "username/password"

    Surprisingly It works, I dont like to do things this way (much better coded) so intend to give both ideas a try,,

    thanks again to all Jason
    Jason

Posting Permissions

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