VBA connect string to Oracle in default workspace

[SeanR01]

Hi all,

Hope this is the right forum to post to.

I've got a relatively simple connect string problem. I'm using Access 2000 as a sort of report writer on an Oracle 8i database, and I'm linking to quite a few Oracle tables.

Currently the Oracle logon is only triggered when users run a query/report that uses one of the linked tables, and it takes quite a while as it takes the NT logon name to the Oracle server first, and then prompts for uid and pwd (as set up in the File DSN) on failure. All future accesses then go straight in with no delay or prompting. I want to cut out the initial delay by writing a connect string which connects in the background at application startup, or possibly prompts for uid & pwd, and immediately connects to Oracle. I've constructed such a command as follows:

Set wrk = OpenDatabase ("Prod", dbDriverNoPrompt, True, "ODBC; DSN={Microsoft for Oracle ODBC};UID=user;PWD=pass&quot

which gets me in alright, but when I go on to run a query/report, the first data access still pops up the Microsoft ODBC driver prompt. How do I make a quick connection in VBA that then makes all future accesses seamless to the users?

Thanks in advance,
Sean

 

[hayo]

Hi Sean !

Please have a look at the following code.

In this sample, I use "DSN=inv_oradb". So I have to create an ODBC datasource before named "inv_oradb" (or any other name), which points to the Oracle database.

This DSN is then used here to connect to Oracle. You may add a userid, then only the password is prompted for. If you also specify the password here, the database is connected without any prompt.

If the Oracle DB is connected (after execution of this code), then this connection is used for all objects (tables, queries,..), which are defined using this DSN !

Hope this helps !

Hayo



===============================================
Public Function OpenDB()

Dim db As Database
Dim constr As String

On Error GoTo Err_OpenDB

constr = "ODBC;DSN=inv_oradb;UID=;PWD="

Set db = OpenDatabase("inv_oradb", _
dbDriverCompleteRequired, True, constr)

MsgBox "Oracle database connected"

Exit_OpenDB:
Set db = Nothing
Exit Function

Err_OpenDB:
Set db = Nothing
MsgBox Err.DESCRIPTION
Resume Exit_OpenDB

End Function
==============================================