DSN-less Oracle connection

[revlisj]

Hi,

I looked all over for a concise example of a DSN-less Oracle connection. I found many code snippets, some short, some quite lengthy, but nothing seems to work. I tried DAO, ADO, etc. I tried searching through Microsoft’s web sites and Access/Oracle user forums, but to no avail.

What I’m trying to do is implement “DSN-less” connections to multiple Oracle databases from a single Access 2010 database (32 bit). The Access database is on a Windows 7 machine (64 bit). The database I’m trying to modify is linked to several Oracle (11g) tables, in several databases. The Access database works fine with named DSN’s created via the “ODBC Data Source Administrator.” The DSN’s are using the following driver: Oracle in OraClient11g_home1, SQORA32.DLL, version 11.02.0001.

The database contains a query that pulls data from four different tables, from four different databases. The query works fine aside from the fact that the user is prompted to log-in four (4) times, once for each DSN/database.

I would like the end user to enter their user ID and password “just once.” I created a form for the users to enter their log-in credentials. When they click a button on the log-in form, I want Access (via VBA code) to log into the four databases using the user supplied log-in credentials (with no prompts displayed). Our environment is such that each user has the same Oracle log-in credentials for each database they have access to.

Is this possible? Thanks in advance for your help.

Cheers,
Jeff

 

[jdraw]

I haven't worked with Oracle for many years. The person on various forums that knows dsn-less connections is Doug Steele. Here is one link to some info.

Google this for more "doug steele dsnless connections"

Good luck.

 

[Rx_]

Just by chance do you have a SQL Server Database available?
I created a Linked Server to Oracle from SQL Server.
Then Access just connects to SQL as normal - and SQL gets the data from Oracle. It is nice from a one login point of view.

 

[revlisj]

Thanks all for your responses. I think my ignorance of Access database connections may have led me to misstate my question. Although the response I received regarding DSN-less connections is worthy of a second look (for a more overarching/long term database connection solution), my real need is to simply automate the ODBC connection process using the existing DSN’s. I found a simple solution (see code snippet below) that works quite well. The only hurdle remaining relates to the handling of passwords containing special characters, especially semicolons. The password in the code below causes an ODBC prompt to display because it thinks I supplied an incorrect password (i.e., “Test” instead of “Test;;1”). I’ll do some more investigating on how to “escape” special characters in the connection string.

Dim WgtDB As DAO.Database
Dim strConnect As String

Set WgtDB = CurrentDb()
strConnect = "ODBC;DSN=myDSNname;UID=myUserID;PWD=Test;;1"
Set WgtDB = OpenDatabase("", False, False, strConnect)
WgtDB.Close

 

[GregoryWest]

Your code looks like that is what I need also. I have DSN entries on every conputer, but with access there is a little bit of a headache with linking to the tables I need. What I would like is to have access link all the external tables I need at the beginning.

Was doing:
Sub createLinkedTable_PostgreSQL(PostgreSQL_tableName As String)
Dim cdb As DAO.Database, tbd As DAO.TableDef
Set cdb = CurrentDb
Set tbd = New DAO.TableDef

tbd.Connect = "ODBC;Driver={PostgreSQL};Server=10.10.10.200;Port=5432;Database=dbName;" & _
"UID=*********;PWD=**********;"
tbd.SourceTableName = PostgreSQL_tableName
tbd.Name = Replace(PostgreSQL_tableName, ".", "_", 1, -1, vbTextCompare) ' e.g. "public.table1"->"public_table1"

cdb.TableDefs.Append tbd
Set tbd = Nothing
Set cdb = Nothing
End Sub

But all I get is a 3151 error. Have a dozen or so databases that need to be linked on the workstation.

 

[revlisj]

Follow-up: It appears that semicolons CANNOT be part of your log-in credentials, as it relates to an ODBC connection string. I tried enclosing the password in double quotes, single quotes, nothing works. Unless I’m missing something, it appears that semicolons are always interpreted as a parameter delimiter. Oh well, I’ll stop using semicolons in my Oracle log-in credentials.

 

[GregoryWest]

Found one of my errors. With windows 7 64 bit you can NOT use the control pannel to get to the ODBC to get drivers etc. Intead you have to use the program:
C:\Windows\SysWOW64\odbcad32.exe
Looks like I am linking tables now. One down one to go

For the record, the error I found was the driver name, it should be {PostgreSQL ANSI} the whole thing is very picky so it only works if you get it exactly right,

 

[revlisj]

Success! I finally stumbled upon the correct "connection string" syntax. I can connect from my Access 2010 database to an Oracle 11g database without using a DSN. Here is some sample code.

Sub MakeConnection()
Dim db As Database
Dim td As TableDef

Set db = CurrentDb

'create link to groups def
Set tdf = db.CreateTableDef("Tbl_LinkedTable")
tdf.Connect = "ODBC;DRIVER={Oracle in OraClient11g_home1};HOST=myHostName;PORT=MyPort;DBQ=MyServiceName;SID=mySID;UID=myUserID;PWD=myPassword"
tdf.SourceTableName = "mySchema.myTable"
db.TableDefs.Append tdf
db.Close
Set db = Nothing

End Sub

 

[GregoryWest]

Congrates on getting it to work with Oracle. I have it working with PostgreSQL with one issue. I have the tables open, but open in read-only form. There is not a lot of updating of the master tables I have to do, but there is a little. Anyone know how to get DSN-Less connections to be read/write?

Sub createLinkedTable_PostgreSQL(PostgreSQL_tableName As String)
Dim cdb As DAO.Database, tbd As DAO.TableDef
Set cdb = CurrentDb
Set tbd = New DAO.TableDef

tbd.Connect = "ODBC;Driver={PostgreSQL ANSI};Server=10.10.10.200;Port=5432;Database=BIO-Static;" & _
"UID=UserID;PWD=Password;"
tbd.SourceTableName = PostgreSQL_tableName
tbd.Name = Replace(PostgreSQL_tableName, ".", "_", 1, -1, vbTextCompare)
' e.g. "public.table1"->"public_table1"

cdb.TableDefs.Append tbd
Set tbd = Nothing
Set cdb = Nothing
End Sub

 

[jdraw]

I haven't worked with PostgreSQL, but my guess is that you may not have Update permissions with the PostgreSQL environment. Your PostgreSQL account would have associated privileges- probably set by your PostgreSQL administrator.

Good luck. Please let us know what you find.

 

[GregoryWest]

Hi jdraw, no it is a pure DAO issue. If I open the tables in question with the menu bar, ie: ODBC link table ..... and run my program it works just fine, if I open the table through DAO code does not work. So must be something in the DAO stuff. I also check the DAO.Updatable property on the table I have opened it says "Read Only" Can not seen to find out how to change this.

 

[jdraw]

Does the associated table have a defined Primary Key? I have seen issues (table is readonly) where there has not been a PK on the table.

 

[GregoryWest]

There is a primary key, but you are correct when I link with the menus I have to tell Access which field it is. Is there a way to do this with DAO?

 

[jdraw]

Here is the associated SQL to define a pk, but once you open the table readonly, you won't be able to modify the table design.

http://allenbrowne.com/func-dao.html#CreateIndexesDAO

There is a PrimaryKey on the table residing in PostgreSQL, right?

 

[GregoryWest]

Thank You Thank You Thank You

That looks like it has solved the problem. Trying out the code now and it seems to work just fine. Can not tell you how much I thank you!!!

Greg

Here is the associated SQL to define a pk, but once you open the table readonly, you won't be able to modify the table design.

http://allenbrowne.com/func-dao.html#CreateIndexesDAO

There is a PrimaryKey on the table residing in PostgreSQL, right?