Connect Oracle Tables via ODBC and VBA

[Matthias Tolkmitt]

Hi,
i wrote a script to connect automatically Oracle-Tables via ODBC to my
Access-Frontend.
it works!
but: every time i start my frontend, i have to type in login and pwd of the
oracle server, that stores my tables. I cannot understand, because in my
script, that connects the tables, i have included login and pwd in the
connection-string (see script below). i fount out, that my linked tables do
not safe the login /pwd a gave them, when they were connected. but why? what
can i do, that my tables hold the login-information, so that my user do not
have to type them in every startup of the access-frontend?

thanks a lot!

matthias


Public Function Connect_tracle_Table()

Dim tdf As TableDef
Dim db As Database
Set db = CurrentDb
Set tdf = db.CreateTableDef("TAB_CUSTOMER")

tdf.Connect = "ODBC;DRIVER={Oracle in OraHome92};
DBQ=dwh_0.erlf;
Server=dwh_0.erlf;
UID=powertbe;
PWD=power;
DSN=Oracle_Powertrain"

tdf.SourceTableName = "POWERTBE.CUSTOMER"
db.TableDefs.Append tdf

End Function

 

[Stefan Hoffmann]

hi Matthias,

i fount out, that my linked tables do
not safe the login /pwd a gave them, when they were connected. but why?

Oracle credentials are not saved.

what
can i do, that my tables hold the login-information, so that my user do not
have to type them in every startup of the access-frontend?

Create and execute a passthrough query at the start of your application
using your connection string including your credentials.



mfG
--> stefan <--

 

[Matthias Tolkmitt]

hi Stefan,

thx for help.
when i connect the tables over the access-dialog-menu (right-click in
database-windows --> link tables) by choosing my oracle tables and checking
the field "save password", the oracle credentials are saved! so with this
laborious method i can save login/pwd-information in the connection-string of
my tables.
no possibility to do so with vba? ;-)

matthias

 

[Stefan Hoffmann]

hi Matthias,

when i connect the tables over the access-dialog-menu (right-click in
database-windows --> link tables) by choosing my oracle tables and checking
the field "save password", the oracle credentials are saved!

Correct. You can use DoCmd.TransferDatabase to achieve the same result,
but it has the same flaw: prompting for primary keys.


mfG
--> stefan <--

 

[Matthias Tolkmitt]

hi Stefan,

ich nehme mal an, du bist deutsch ;-)
also ich hab ne passthrough-abfrage erstellt und führe sie beim start aus.
allerdings kommt beim ersten zugriff auf die tabellen/abfragen wieder das
login-fenster!
das pt-abfrage ist fertig erstellt und wird beim start nur aufgerufen. diese
abfrage geht lustigerweise ohne eingabeaufforderung...

 

[Stefan Hoffmann]

hi Matthias,

ich nehme mal an, du bist deutsch ;-)

Yes, but the language of this ng is English, so be polite to the ng.

also ich hab ne passthrough-abfrage erstellt und führe sie beim start aus.
allerdings kommt beim ersten zugriff auf die tabellen/abfragen wieder das
login-fenster!
das pt-abfrage ist fertig erstellt und wird beim start nur aufgerufen. diese
abfrage geht lustigerweise ohne eingabeaufforderung...

Compare the .Connect strings of your passthrough query and your linked
tables. They must be the same (without the credentials).


mfG
--> stefan <--

 

[Martin]

Hi Matthias,

I gratefully used your script to link a couple of DB2 tables in Access.
The way to keep the user and password information is to specify them in the
connect string:
tdf.Connect = "ODBC;DATABASE=xxxxx;DSN=xxxxx;UID=userid;PWD=password"

For me it works fine. Hope this helps!

Regards,
Martin Maes