I've spent the last few years working with a SQL server back end and become fairly comfortable with it. Now I have a new job, a new database and a new Oracle back end. My sole mission in life is to learn everything there is to know about this db. In my previous incarnation, we simply used either a user DSN or a file DSN to link the tables. Permissions we set by the Active Directory memberships and it wasn't necessary to login to SQL Server.
This Oracle back end db requires a login ID and password, even though a connection has already been established via a connection string and code as follows
Code:
Public conTPD As New ADODB.Connection
Public conStr As String
conStr Driver={Microsoft ODBC for Oracle};Server=TPD.WORLD;UID=guest;PWD=guest
conTPD.Open constr
'The connection is successfully established
'conTPD.Stat = 1
'conTPD = Provider=MSDASQL.1;
Also, all tables are using a DSN with the same information.
This works perfectly EXEPT you still prompted to log into the Oracle back end.
This Oracle database, as far as I can tell , does not look at active directory groups to grant access.
In addition, since this is such a widely distributed back end I would like to use a file DSN than can be distributed. While they seem to work in the ODBC manager, they fail when I try to link tables using them.
Here's the DSN and the error message.
Code:
[ODBC]
DRIVER=Microsoft ODBC for Oracle
SERVER=TPD.WORLD
We currently have over 300 users and using User DSNs is impractical. For each new user, someone has to remote in to their PC and create a User DSN. If we change servers they would have to do that 300 times.