Link Oracle table to Access, without DSN connection

  • Thread starter Thread starter Guest
  • Start date Start date
G

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?
 
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.
 
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.
 
Back
Top