473,320 Members | 1,724 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Bypass entering password to ODBC data sources

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
4 41255
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: SkunkDave | last post by:
Is there a script anyone has that will automate the addition of an access database to the OBDC datasources in control panel. Thanks
1
by: Jon Brunson | last post by:
I'm writing a program that allows users to import their data into our system. The data can come from any datasource installed on the users PC, eg Access, Excel, SQL, Text Files, etc.. Is there a...
5
by: Landley | last post by:
Hi All, Is there a method that I can call to return a list of available database drivers? If so, is there then a way to get the list of fields that are required for those the database...
1
by: Frederick Page | last post by:
Hi everybody, we are using DB2 UDB 7.2 FP 11 on WinNT 4.0 (SP5) machines, LDAP works quite fine. As I already googled, there is however the question on how to apply e.g. "db2 catalog system odbc...
2
by: Wayfarer | last post by:
I am new to Access - classes were all in Oracle. Now I am trying to actually real world set up and run an Access database for a club I belong to. I have a couple of books on Access but unable to...
3
by: abcd | last post by:
I want to get the list of data sources on the server machine using C# and ASP.NET any clues how to get that...I have done this using VB using SQLDataSources function from ODBC32.dll thanks
0
by: BillE | last post by:
I am using Visual Studio 2005 to create a web site which includes reports. The web site has several sub-folders. When working with reports I can't get the website data sources (classes and...
2
by: JeffN825 | last post by:
Hello, Thank you in advance for any assistance. I am trying to set up a mail merge from VB6/VBA code with Word 2000 using an ODBC data source (SQL 2000). I have successfully gotten the...
1
by: steven_nospam at Yahoo! Canada | last post by:
I am a UNIX person (not much experience with MS Access) who during a recent upgrade on an IBM RS/6000 server had to convert our system due to an upgrade to a new software revision. The old...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.