How do I make a DSN-Less connection from Access to Oracle using DAO? 
I know the basic code, but I can't seem to get the connection string
right.

Set db = CurrentDb()
Set tdf = db.CreateTableDef("Test")
tdf.Connect = "ODBC;DRIVER=msdaora;SERVER=Orcl;UID=test;PWD=test;"
'This doesn't work!! I also tried Driver={Microsoft ODBC for Oracle}
tdf.SourceTableName = "Test"
db.TableDefs.Append tdf

Whatever I put in Driver or Server, I get 'Odbc connection to
DriverServer failed'.  I can link it manually though using a Data
Source.

---

Request for Question Clarification by mathtalk-ga on 21 Mar 2003 07:09 PST

Hi, ny_insomniac-ga:

Which version of Microsoft's ODBC driver for Oracle are you using?

Please give the error number as well as the text of the error message.

Also please review the Google Answers pricing guidelines here, so I am
clear about the amount of detail expected in an answer.  For the price
offered questions might normally be answered with a single link or
sentence:
 
http://answers.google.com/answers/pricing.html  
  
regards, mathtalk

---

Clarification of Question by ny_insomniac-ga on 21 Mar 2003 08:13 PST

The problem may be solved.

The driver is: Microsoft ODBC for Oracle 2.573.7713.00 MSORCL32.DLL
8/18/2001

When I use ODBC;DRIVER=msdaora;SERVER=Orcl;UID=test;PWD=test;, I get:

Run-time error '3151':
ODBC--connection to 'msdaoraOrcl' failed.

I just tried again using ODBC;DRIVER={Microsoft ODBC for
Oracle};SERVER=Orcl;UID=test;PWD=test; and it actually seems to work.

Please confirm that this is the proper format for the connect string.

Hi, ny_insomniac-ga:

The original error was caused by using 'msdaora' as the ODBC Driver
parameter.

MSDAORA.DLL is the Microsoft Data Access component that is an OLE DB
'provider' for Oracle, not an ODBC 'driver'.  In other words msdaora
is an ADO thing than a DAO thing.

Yep, it's confusing.

best wishes, mathtalk

Perfect.  Those links confirmed a lot of what I thought which was really helpful.

Hi, ny_insomniac-ga:

Congratulations, I think you've solved it.

Your syntax looks fine.  A discussion of techniques including DSN-less
connections for ODBC data access often centers on how an application
can be distributed to end-users, as here:

[Problematic or Programmatic ODBC]
http://www.databasejournal.com/features/mssql/article.php/1491051

(scroll to end to see the DSN-less example, similar to your version
except that it targets SQL Server rather than Oracle)

The same author (Danny Lesandrini) has created a tutorial on DSN-less
connections:

[ODBC DSN-Less Connection Tutorial]
http://www.databasejournal.com/features/mssql/article.php/1491011

For a collection of DSN-less ODBC connection strings suitable for ADO
(rather than DAO), see here:

[ODBC DSN Less]
http://www.able-consulting.com/MDAC/ADO/Connection/ODBC_DSNLess.htm

The Oracle examples (Microsoft new & old drivers, and Oracle driver)
are located slightly futher than halfway down the page.

My installed version of the Microsoft ODBC for Oracle driver
(MSORCL32.DLL) is about a year newer than yours (2.573.9030.00 dated
8/29/2002).

regards, mathtalk