Link Oracle table to Access, without DSN connection

[Guest]

I have an Access 2K database that is linked (using the Transferdatabase
method) to an Oracle 9i backend database.

Unfortunately, this appears to require a DSN connection, because I have been
unable to link the two without a DSN connection established on the PC running
Access. Am I missing something, or does this type of link require the DSN
connection. If it is required, is there a way to create the DSN connection
via code?

 

[Douglas J. Steele]

While the example is for SQL Server, you should be able to use the DSN-less
approach I outline in http://www.accessmvp.com/djsteele/DSNLessLinks.html if
you can determine the appropriate DSN-less connection string to use (see
http://www.able-consulting.com/ado_conn.htm for possibilities)

You'll have to change the line of code

tdfCurrent.Connect = "ODBC;DRIVER={sql server};DATABASE=" & _
DatabaseName & ";SERVER=" & ServerName & _
";Trusted_Connection=Yes;"

to whatever the appropriate connection string is.

And yes, it is possible to create DSNs through code. I have one link on that
page, but the thing to remember is that a DSN is strictly some entries in
the registry.

 

[Guest]

Thanks Doug,

I'll give it a try. The application I am working on allows users to link on
one of many schemas in the Oracle database, so I don't have the option of
starting out with the DSN connection. However, I do know the names of the
schema and the tables within the schema. I have always used the
transferdatabase method to do this linkage, so will try the CreateTableDef
method, and your code to see whether that technique will work. Will keep you
informed.

 

[Dale Fye]

Doug,

Worked like a charm. Thanks for the input.

Dale