Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Bypassing Oracle passwords

Posted on 2000-05-10
7
Medium Priority
?
350 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 

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 300 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

722 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