Results 1 to 13 of 13
  1. #1
    New Lounger
    Join Date
    May 2002
    Location
    Durham, NC
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ODBC Connection to Oracle (Access 2000)

    I recently migrated a table from Access 2000 to an Oracle 8.1.7.0.0 database and would like to keep my Access 2000 front end but I'm having trouble configuring it correctly. With my code I can get into oracle but it returns empty ADO recordsets (my sql statement will run in SQL*Plus without a hitch). My main goal is to somehow work it out that the user doesn't have to enter a password (the way they do if you use linked tables) to access information. The code follows:
    Dim sql As String
    Dim rst As ADODB.Recordset

    Set rst = New ADODB.Recordset

    If Me.chkVer = True Then
    sql = "A.seq_verif is not null"
    ElseIf Me.chkVer = False Then
    sql = "A.seq_verif is null"
    End If
    If Me.chkIn = True Then
    sql = sql & " AND A.inhouse is not null"
    ElseIf Me.chkIn = False Then
    sql = sql & " AND A.inhouse is null"
    End If

    ''Oracle Version
    sql = "SELECT A.IMAGE_cloneID, A.known_gene_description, A.rearray_clone_collection, " & _
    "A.rearray_plate, UPPER(A.rearray_row)||" & _
    "LPAD(A.rearray_col, 2, '0') AS position, " & _
    "A.seq_verif, A.inhouse, A.screened FROM mgc.mgc_master_list A" & sql

    ''Access Version
    'sql = "SELECT A.IMAGE_cloneID, A.known_gene_description, A.rearray_clone_collection, " &
    ' "A.rearray_plate, UCase(A.rearray_row) & " & _
    ' "Right(0 & A.rearray_col,2) AS position, " & _
    ' "A.seq_verif, A.inhouse, A.screened FROM mgc_master_list A" & sql

    With rst
    .ActiveConnection = "DSN=mgcDSN; Password=mgcpass;"
    .CursorLocation = adUseServer
    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
    .Source = sql
    .Open
    End With

    Set Me.fsubItemLookup.Form.Recordset = rst

    Any help would be appreciated!

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: ODBC Connection to Oracle (Access 2000)

    Hi Erica,
    You appear to be missing the WHERE keyword from your SQL.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  3. #3
    New Lounger
    Join Date
    May 2002
    Location
    Durham, NC
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Connection to Oracle (Access 2000)

    It's in there, I just forgot to paste that part in the post. Thanks

  4. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: ODBC Connection to Oracle (Access 2000)

    Are you getting any errors or simply not getting any records returned? (Incidentally, you can save the password when you link the tables to your database if that helps?)
    Regards,
    Rory

    Microsoft MVP - Excel

  5. #5
    New Lounger
    Join Date
    May 2002
    Location
    Durham, NC
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Connection to Oracle (Access 2000)

    I've tried this several different ways...if i use a non-system DSN then I get this invalid character error message. Saving the password for the linked table would be great. I've done that before but I can't figure out how to do that with an Oracle link. Can you tell me how?

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: ODBC Connection to Oracle (Access 2000)

    Assuming you've got your ODBC DSN set up already, in Access you would choose File-Get External Data-Link tables, change file type to ODBC Data Sources and choose your DSN. When you've entered the password, you should get a dialog with the available tables and in the bottom right corner should be a Save Password checkbox.
    The alternative is to use OLEDB (I say that because I've never figured out ODBC connection strings in code properly!) which is, in my experience, faster than ODBC unless you need to create or use local tables as well.
    Hope that helps.
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    New Lounger
    Join Date
    May 2002
    Location
    Durham, NC
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Connection to Oracle (Access 2000)

    That's the way I did it in this instance but it requires me to enter the oracle password every time I open the access database and I can't figure out how to get around it. Not familiar with OLE DB but I'll try to check it out.

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: ODBC Connection to Oracle (Access 2000)

    In case it helps, this is a snippet of the code I use to access our Oracle database using OLEDB:
    <pre> Set conn1 = New ADODB.Connection
    Set rst1 = New ADODB.Recordset
    conn1.Open "Provider=MSDAORA;Data Source=DSNname;User ID=user;Password=pwd"
    With rst1
    .CursorLocation = adUseClient
    .Open strSQLstatement, conn1, adOpenStatic, adLockReadOnly, adCmdText
    </pre>

    Hope that's useful.
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: ODBC Connection to Oracle (Access 2000)

    It's just occurred to me that you may need to use a client side cursor - have you tried changing that in your existing code?
    Regards,
    Rory

    Microsoft MVP - Excel

  10. #10
    New Lounger
    Join Date
    May 2002
    Location
    Durham, NC
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Connection to Oracle (Access 2000)

    thanks for all your help. I tried both of your suggestions. I had tried the OLEDB connection before (I'm not the greatest with computer terminology) but I couldn't ever get it to work b/c oracle throws an error "ORA-00911:invalid character" but if I copy the select statement from the immediate window at that point I can run the query in SQL*Plus.

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: ODBC Connection to Oracle (Access 2000)

    Does this work:
    <pre>Dim sql As String, strWhere As String
    Dim rst As ADODB.Recordset

    Set rst = New ADODB.Recordset

    If Me.chkVer = True Then
    strWhere = "A.seq_verif is not null"
    ElseIf Me.chkVer = False Then
    strWhere = "A.seq_verif is null"
    End If
    If Me.chkIn = True Then
    strWhere = strWhere & " AND A.inhouse is not null"
    ElseIf Me.chkIn = False Then
    strWhere = strWhere & " AND A.inhouse is null"
    End If
    If len(strWhere) <> 0 Then strWhere = " WHERE " & strWhere

    ''Oracle Version
    sql = "SELECT A.IMAGE_cloneID, A.known_gene_description, A.rearray_clone_collection, " & _
    "A.rearray_plate, UPPER(A.rearray_row) || " & _
    "LPAD(A.rearray_col, 2, '0') AS position, " & _
    "A.seq_verif, A.inhouse, A.screened FROM mgc.mgc_master_list A" & strWhere


    With rst
    .ActiveConnection = "DSN=mgcDSN;PWD=mgcpass"
    .CursorLocation = adUseClient
    .CursorType = adOpenStatic
    .LockType = adLockOptimistic
    .Source = sql
    .Open
    End With

    Set Me.fsubItemLookup.Form.Recordset = rst
    </pre>

    If not, what error do you get?
    Regards,
    Rory

    Microsoft MVP - Excel

  12. #12
    New Lounger
    Join Date
    May 2002
    Location
    Durham, NC
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ODBC Connection to Oracle (Access 2000)

    OK so I messed with some of the settings under Access options removing any kind of locking and tried all of my old connection strings and the OLEDB and it seems that "DSN=mgcDSN; PWD=mgcpass" is now working <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>. I don't know exactly what did the trick, but it seems to be working fine for now. Thank you very much for all of your help!!

  13. #13
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: ODBC Connection to Oracle (Access 2000)

    Glad you got it sorted out! <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    Regards,
    Rory

    Microsoft MVP - Excel

Posting Permissions

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