Always connect to Oracle ODBC 1

[TudorSmith]

Hi

I have an Access DB which is using 6 linked tables to an Oracle DB.

I have the user opening a form which makes a comparison of the forms record with a value in one of the Oracle tables. When the form opens the user has to put in the UserID & Password for the Oracle ODBC to connect.

I did this before in a VB applicaton but I've forgotton the syntax. Is there a line or two of code I can put in the intial form that will connect to the Oracle DB by way of passing in the UserID & Password at the off-set? This way the user can open the form and the connection has already been made so they will not need to enter the Password details

Thanks

birklea

birklea ~©¿©~ <><
Dim objJedi as Jedi.Knight
Set objJedi[skyWalker].Aniken = FatherOf(useThe.Force(objJedi[skyWalker].luke))

 

[Bennie]

This is the code that I use
'Put in at the module level
Private Type GlobalDataBase
wrkSpace As Workspace
gdbsDb As Database
ProductionRun As Integer
Location As String
End Type
Public DBGlobals As GlobalDataBase


'this is the function to logon
Function LogonToOracle() As Boolean

On Error GoTo LogonToOracle_Err


Dim strSzConnect As String
Dim strRoleString As String
'this is required for security by the DBA
strRoleString = &quot;Set role xxxxxxx identified by xxxxxxx&quot;


Set DBGlobals.wrkSpace = DBEngine.Workspaces(0)
strSzConnect = &quot;ODBC;DSN=DSNAME;UID=xxxxxx;PWD=xxxxxx; &quot; & strRoleString
Set DBGlobals.gdbsDb = DBGlobals.wrkSpace.OpenDatabase(&quot;&quot;, False, False, strSzConnect)

Set DBGlobals.gdbsDb = CurrentDb()
LogonToOracle = True


LogonToOracle_Exit:
Exit Function

LogonToOracle_Err:

MsgBox Err.Number & Err.Description
LogonToOracle = False
Resume LogonToOracle_Exit
End Function

 

[TudorSmith]

Hi

This is close

Problem is it is still showing the Oracle Login screen although now the UID and password are already filled in.

Is there any way to make it connect without the user seeing the login screen?

Thanks

Tudor

birklea ~©¿©~ <><
Dim objJedi as Jedi.Knight
Set objJedi[skyWalker].Aniken = FatherOf(useThe.Force(objJedi[skyWalker].luke))

 

[daveonion]

Hi tudor, did you find a way to bypass the login screen as I have the same problem connecting to sql,
thanks

Dave

 

[TudorSmith]

No...no reply at all Mr. Onion

I guess this will be a no goer!

birklea ~©¿©~ <><
Dim objJedi as Jedi.Knight
Set objJedi[skyWalker].Aniken = FatherOf(useThe.Force(objJedi[skyWalker].luke))

 

[daveonion]

oh well,
hmmm

 

[evalesthy]

I don't work with Oracle but I have saved some article regarding Access/Oracle in case I ever need them - take a quick look at these and see if any help.

Microsoft Knowledge Base
Q167225, Q174679, Q174981, and Q175018

Tek-Tips Threads
thread705-487287
thread705-427532
thread700-402428
thread705-287837
thread181-216778

and possibly

http://osi.oracle.com/~tkyte/ResultSets/index.html

maybe you can glean some helpful code from one of these.

 

[BillPower]

Hi,

Don't know if this will help.

To get your current connection string to Oracle or any other ODBC connection, this will print to the Immediate Window, Ctl+G while in the Visual Basic Editor:

Dim dbs As DAO.Database, tdf As DAO.TableDef
Set dbs = CurrentDb
For Each tbl In dbs.TableDefs
If tbl.Attributes And dbAttachedODBC Then
Debug.Print tbl.Name & &quot;, &quot; & tbl.Connect
End If
Next
Set dbs = Nothing

To reconnect our ODBC tables:

Dim dbs As DAO.Database, tdf As DAO.TableDef
Dim strUID As String, strPWD As String
strUID = Me!txtUID
strPWD = Me!txtPWD
Set dbs = CurrentDb()
For Each tdf In dbs.TableDefs
If tdf.Attributes And dbAttachedODBC Then
If Len(tdf.Connect) > 0 Then
'This is the string printed to my immediate window
'tdf.Connect = &quot;ODBC;DRIVER={Microsoft ODBC for Oracle};SERVER=Finance;UID=BILL;PWD=1234&quot;
'This is the string edited to log the current user in to Oracle
tdf.Connect = &quot;ODBC;DRIVER={Microsoft ODBC for Oracle};SERVER=Finance;UID=&quot; & strUID & &quot;;PWD=&quot; & strPWD
tdf.RefreshLink
End If
End If
Next tdf
Set dbs = Nothing

Change Me!txtUID and Me!txtPWD to your control names. You will also need Microsoft Data Access Objects 3.x installed in your References.

This method has always worked seamlessly for me in logging users on to 3rd Party ODBC software from Access. Let me know if it works for you.

Bill

 

[BillPower]

My 1st function should have read:

Dim dbs As DAO.Database, tdf As DAO.TableDef
Set dbs = CurrentDb
For Each tdf In dbs.TableDefs
If tdf.Attributes And dbAttachedODBC Then
Debug.Print tdf.Name & &quot;, &quot; & tdf.Connect
End If
Next
Set dbs = Nothing

Bill

 

[InterGeorge]

Phuu... My problem too, but with Firebird ODBC driver.
(Gemini)

If I link the table with the manager, there is on the right lower corner a check box, remember password.

If I check this, the password will be remembered' If I don't
click on this box next time somebody tries to access, the username and password box shows up.

Somewhere this password must be stored!!!

Bill - If you still with us and you so a knowledgeable guy, one *BIG* question.

I don’t store any data in Access, all are coming from Firebird/Interbase, one (97 Tables) from our accounting system, another one (17 tables from me) for an application to integrate PDA’s and PocketPC in our company for our 30 technicians. Job history, work- and time sheets.

If I need to change one of the SQL table, ether alter a field or add one, this change is never reflected in the links to Access (link??). As I have all relations made in Access (I have to, the links aren't imported from Firebird) it’s a pain to delete the table, delete all relations and, link the table again and make all relations from the scratch again (…and never make a mistake)

Is there a way to “refresh” those links or are they graved in stone?

Any help will be appreciated

George

 

[BillPower]

Hi InterGeorge,

I don't really understand. How are you connecting to Firebird at present. Manually or with code, if code can you post the code, if manually can you describe exactly how you log on.

Have you tried getting the connection string as I suggested above.

Bill

 

[InterGeorge]

Dear Billpower

By hand - I'm just a beginner!!

I think the password storage is a feature of the Gemini driver.

http://www.ibdatabase.com/

I'm afraid to enter the code - what's when after ketchup coming out of my A drive?? Belive me one thing: I'm the born beta tester within.

I wrote my question at 2.00am, at 4am I found the answer:
Tools / Database Utilities / Linked Table Manager
I tried, works fine on two tables, the third one where shredded to bits

But still, when I link the table by enable the &quot;remeber password&quot; only then the user never will asked again for the pwd. If I enter the user/pwd only in the DSN settings, the user has to enter the pwd on every new session.

I use the same DSN settings for both, so somewhere must be code?! ...and where could be this code?

I upload a screen print from the link table windows
you could find it under

http://electec.info/LinkTable.jpg

George

 

[daveonion]

Billpower, where about in access do you insert the code you supplied,
thanks
Dave

 

[BillPower]

In the On Load event of the Switch Board or Menu or 1st Form that loads at startup. The form obviously mustn't be bound to a linked table.

Bill

 

[daveonion]

Excellent got it working, the problem was I had the form loading up first that was connected to the recordset, this obviously (now anyway) prompts for the login before the code has had time to load,
Birklea, was this your problem too?

Dave

 

[TheRealTiger]

Try this, it works for me.

1. Navigate to the following Key in the Registry: \Software\ODBC\ODBC.ini
2. Open the key that corresponds to the problem connection and add two “string values”:
a. Password
b. Autologin
3. Set the value data of the Password key to the password.
4. Set the value data of the Autologin key to “T”, without the quotes.