Re: OS Authentication and ODBC

Ed Stevens <Ed.Stevens_at_nmm.nissan-usa.com> wrote:

>Is anyone using MS Access to front end an Oracle database using
>Oracle's OS authentication? I finally got all the pieces is place to
>do a successful OS authenticated logon to the Oracle database, using
>SQL*Plus. The next step of this particular assignment is to do it from
>MS Access. Whenever I try to create the link from Access97 to Oracle,
>the ODBC pops up asking for userid and password. This happens with
>either the Oracle or the MS supplied ODBC driver. I've been tasked
>with finding a way to bypass this.

Ed, that's the default way an ODBC connection works - popping up that dialog box. Have not used ODBC for many years now, but thinking back of what I did in VB3, you can make a manual ODBC connection in Visual Basic. In such a case you call the ODBC logon or ODBC connect API call directly and thus bypass the auto logon screen. Not sure how you would do this in MS Access though.

In Delphi for example, you have a boolean property called LOGINPROMPT for a database connection (which can be a BDE driver or ODBC driver). You simply make this false and manually specify the connection settings in the PARAMS (strings) property.

A word of warning on MS Access though. Access generates huge amounts of network overhead traffic. Or used to back with Access 97 and previous versions. So bad in fact, that the network manager at one place I contracted at, banned users from using Access as front-ends to any database server. At the time, the sniffs we did on network traffic showed that a Delphi III application only generates 4 frames for a connect, SQL select, and disconnect fron a SQL-Server database. A Visual Basic 5 application used 19 frames doing the same. Almost a 400% larger network load. Both applications used the same Microsoft ODBC driver. MS Access shares/uses the same client database engine (jet engine or whatever they call it) as Visual Basic.

From sniffs that I have personally run, Access generates a lot of junk traffic. After the first connect for example, Access used to run a SELECT statement on a Access system table. Which of course does not exist in the database (e.g. Oracle, SQL-Server) that it is connecting too. Lots of crap like that...

OK, these results and experiences were from two and more years ago - however knowing Microsoft's capability of creating bloatware, I doubt that they have addressed these problems to any significant extent.

regards,
Billy Received on Thu May 04 2000 - 00:00:00 CDT