Solved

Bypassing Oracle passwords

Posted on 2000-05-10
7
344 Views
Last Modified: 2008-03-03
I have an Access database with link tables through ODBC to an Oracle database. Whenever a user runs a query that uses the link tables, they are prompted for the Oracle password.

A problem that I believe to be related (since neither was happening when I began working with this database) is that the error message, "Invalid procedure call or argument" pops up when I try to open the Linked Table Manager.

Any idea what the causes and/or solutions to these problems might be?

0
Comment
Question by:ckjim
  • 4
  • 2
7 Comments
 
LVL 10

Expert Comment

by:paasky
ID: 2798033
Hello ckjim,

Seems that linked tables have lost their connect username/password information. If you can't use linked table manager wizard, you could try refreshing links with code. Here's an example (this is air-code and I can't test this with Oracle DB right now, but the important thing is the idea) function. Copy it into module or form and execute with correct connect string. It will loop all linked ODBC tables and refresh connection.

Public Function RefreshODBCLinks(ConnectString As String)
Dim tdf As TableDef
   
    On Error GoTo LinkErr
   
    For Each tdf In CurrentDb.TableDefs
        If tdf.Attributes = dbAttachedODBC Then
            tdf.Connect = ConnectString
            tdf.RefreshLink
        End If
    Next
   
    Set tdf = Nothing
    RefreshODBCLinks = True
   
   
LinkExit:
    Exit Function
   
LinkErr:
    MsgBox Err.Description
    Resume LinkExit

End Function

Usage:
Success = RefreshODBCLinks("ODBC;DATABASE=database;UID=user;PWD=password;DSN=datasourcename")

Example:
Success = RefreshODBCLinks("ODBC;DATABASE=TEST;UID=scott;PWD=tiger;DSN=TEST")

Hope this helps,
Paasky
0
 
LVL 10

Expert Comment

by:paasky
ID: 2799054
I made some enchangements to previous code and tested if with ODBC linked Oracle tables. Here's the 2nd version of function which will recreate the link and saves username and password information to linked table.

Public Function RefreshODBCLinks(ConnectString As String)
Dim tdf As TableDef
Dim newtdf As TableDef
Dim tablename As String
     
    On Error Resume Next
     
    For Each tdf In CurrentDb.TableDefs
        If tdf.Attributes And dbAttachedODBC And Right(tdf.Name, 4) <> "_tmp" Then
            tablename = tdf.Name
            tdf.Name = tdf.Name & "_tmp"
            Set newtdf = CurrentDb.CreateTableDef(tablename, dbAttachSavePWD, tablename, ConnectString)
            CurrentDb.TableDefs.Append newtdf
            If Err.Number = 0 Then
                Debug.Print "Table " & tablename & " relinked."

                ' delete old link
                CurrentDb.TableDefs.Delete tablename & "_tmp"
            Else
           
                ' errors encountered, restore original link
                tdf.Name = tablename
                Err.Number = 0
            End If
        End If
    Next
     
    Set tdf = Nothing
     
End Function

Regards,
paasky
0
 

Expert Comment

by:Sladey
ID: 2800104
I would presume you checked the save password box when you attached the tables? - If not re-attach and save the password

One other solution I used on Windows 95 was when the system DSN was activated on the users PC was to edit the registry with the Oracle Pasword, so the were not asked for it.

Start Regedit

HKEY_LOCAL_MACHINE
SOFTWARE
ODBC
ODBC.INI
Select the DSN xxxxxx
Select the PWD field and add the password?

Close Regedit

I had a similar problem with the Linked Table Manager on one database, but no others are affected.



0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:ckjim
ID: 2801467
paasky: Thanks for all the code, but I don't know where to put it. I haven't been able to find any startup sequence, although I know it has to be somewhere. Can you tell me where to look for that?

Sladey: You were right about the check box; I figured it out about 2 minutes after writing the question. As for editing the registry, there are too many machines accessing the database to go about it like that. Unless I'm misunderstanding (that's likely), it seems like I'd have to go to each machine and change its registry.

Still no luck figuring out why I can't use my Linked Table Manager for this database either.

0
 
LVL 10

Accepted Solution

by:
paasky earned 75 total points
ID: 2801504
ckjim, copy the above code into clipboard, then select Modules page from the database window and press New button. Paste the function into Module window and save it using name such as "General functions".

To run it immediatly, select from menu View | Debug Window (or press Ctrl+G) and enter to appearing box:

? RefreshODBCLinks("ODBC;DATABASE=TEST;UID=scott;PWD=tiger;DSN=TEST")

(remember to change the DSN and other connect information to match your enviroment)

You can also add a macro which executes this function. Set the macro action to RunCode and enter the Function name field

RefreshODBCLinks("ODBC;DATABASE=TEST;UID=scott;PWD=tiger;DSN=TEST")

HTH,
Paasky
0
 

Author Comment

by:ckjim
ID: 2801922
Thanks for all the help.
0
 
LVL 10

Expert Comment

by:paasky
ID: 2801935
I hope my suggestion was useful. Good luck with your project!

Best regards,
Paasky
0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

680 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question