By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,920 Members | 1,660 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,920 IT Pros & Developers. It's quick & easy.

Bypass entering password to ODBC data sources

P: n/a
Hi everyone,

I was hoping somebody could assist me in this issue. I am quite a
newbie to ODBC connections and was struggling to search for related
topics. Here is my situation:

I have a Access 2000 .mdb file which has several linked tables to a
ODBC data source, namely an Oracle back-end. I have already setup the
connctions to these back-end tables in the ODBC Administrator, and it
works fine. When I view one of these tables for the first time when
opening the .mdb, there appears a form entitled "Oracle ODBC Driver
Connect", where the following fields are:

Service Name: blablabla
User Name: yours truly
Password: must be entered each time I start the .mdb new and open a
linked table/query

Before I distibute my application, I want to create a generic User Name
and Password on the back-end. But what I want to know is: How do I
automate this procedure above in Access VBA, so that I don't have to
enter the password each time I start the .mdb file? In other words, how
do I bypass the "Oracle ODBC Driver Connect" form with a hard-coded
username and password?

Please help, as I am a bit lost at the moment. Even a few pointers as
to where I can find some useful resources on the Net will be much
appreciated.
Kind Regards,

J

Nov 13 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
Jean wrote:
Service Name: blablabla
User Name: yours truly
Password: must be entered each time I start the .mdb new and open a
linked table/query

Before I distibute my application, I want to create a generic User Name
and Password on the back-end. But what I want to know is: How do I
automate this procedure above in Access VBA, so that I don't have to
enter the password each time I start the .mdb file? In other words, how
do I bypass the "Oracle ODBC Driver Connect" form with a hard-coded
username and password?


It sounds like you are using linked tables. Just delete those links and
reconnect.

Click New table

on the new table screen, pick link table

in the link file selection dialog, select files of type -> ODBC

On the select data source dialog pick your DSN (note - I usually install
DSNs as machine level, not user, in case other people are logged into a
machine) and click OK

The Oracle ODBC connect dialog (similar to what you described) appears -
enter your password and click OK.

Now, here's where you need to pay attention - a link tables dialog with
all the Oracle tables for the user name you're using appears. THERE IS
A SAVE PASSWORD CHECK BOX - MAKE SURE YOU TICK IT. Select your tables
and click OK and now you've got what you want.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #2

P: n/a
Yip, I have gone and done that...so far it is what I need. Thanks for
your input, it confirmed

Was just wondering what other ways there are. Anyhow, I would probably
have to document in my Support Manual this way of doing it! :-)

Nov 13 '05 #3

P: n/a
Jean wrote:
Yip, I have gone and done that...so far it is what I need. Thanks for
your input, it confirmed

Was just wondering what other ways there are. Anyhow, I would probably
have to document in my Support Manual this way of doing it! :-)


The only other way I practice is saving the connect string in a standard
module as a public constant. I do this with pass through queries and if
the password is for an Oracle user with privileges other than select
only, I ensure the mdb is an mde before distributing it.

For example the following sub creates or replaces a passthrough query.
The function and connect string, which includes the pasword, is held in
a standard module.

Option Compare Database
Option Explicit

'*******Connect String*************
'The connect string can be copied from the creation of a PTQ from the
'Connect property.

Public Const cTmarConnect =
"ODBC;DSN=TMA;UID=tmar;PWD=myPASSWORD;DBQ=tma;DBA= W;APA=T;PFC=1;TLO=0;DATABASE="
Public Sub sCreatePT(strSql As String, strQryName As String)

'Takes an Oracle SQL string (strSql) and a query name (strQryName) and
either creates a new PT query or, if
'it exists already, changes the SQL string

Dim qdf1 As DAO.QueryDef
Dim dbs As DAO.Database

Dim booFound As Boolean

Set dbs = Access.CurrentDb

booFound = False

For Each qdf1 In dbs.QueryDefs

If qdf1.Name = strQryName Then

booFound = True

Exit For

End If

Next

If booFound = False Then 'querydef does not exist, so create it

Set qdf1 = dbs.CreateQueryDef(strQryName)

Else 'already exists

Set qdf1 = dbs.QueryDefs(strQryName)

End If

'Now assign characteristics

With qdf1

.Connect = cTmarConnect

.SQL = strSql

.ReturnsRecords = True

End With

Exit_Proc:

qdf1.Close
Set qdf1 = Nothing

dbs.Close
Set dbs = Nothing

Exit Sub

Err_Proc:

End Sub

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #4

P: n/a
Jean wrote:
Yip, I have gone and done that...so far it is what I need. Thanks for
your input, it confirmed

Was just wondering what other ways there are. Anyhow, I would probably
have to document in my Support Manual this way of doing it! :-)


Wouldn't be necessary if you're distributing this in a single
environment. You make the connections at your desktop and then everyone
else's will be the same.

If you're making an app for distribution to clients outside of an
organization, they are going to want to not have to go through making up
a DSN, linking, etc.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.