Open Report Object

[the_utmost]

Hi:

I am opening up a remote database and I have a syntax problem. I know how to open up my report with DAO but I need to open it with ADO. I have code for both but I am still getting prompted for the DSN User Name and password. Here is my code.

Dim AccessApp As Access.Application

Set AccessApp = CreateObject("access.application")
AccessApp.OpenCurrentDatabase (strPath) '* I get prompted for the user name & password here.

I have the code for an ADO connection, but I need to put 2 + 2 together. Here is my database connection code:

Public Sub OpenRemoteDatabase()

Set cn = New ADODB.Connection

cn.ConnectionString = "Provider=MSDASQL.1;Persist Security Info=False;Extended Properties=DSN=RPT_TRIDENTPROD;UID=RPT;PWD=RPT;DBQ=TRIDENTPROD;" cn.Open

End Sub

How can I open up my Access App object with ADO like above?

 

[the_utmost]

I am a diehard Visual B programmer and I have only done about 2 years of Access. I am unfamiliar with some of the objects. I haven't used DAO since VB 5 and it is somewhat different here.

My problem is that I have a DSN connection to a remote database. When I use DAO to open the report (which is what I am comfortable with at this point) the DSN asks for the user name and password. So I looked up some ADO syntax to open the database. This works fine but I cannot make a logical connection between opening the report object with ADO. Actually, I am just trying to get around the DSN asking for a user name and password. I cannot change this on the users computer as there are way too many computers to do that and then we would lose needed security in that area. Any idea's?

 

[the_utmost]

Thanks Pat. I previously found this article on www.microsoft.com and I thought that it would fix my problem. The thing is, we are talking about 2 distinctly different things. There is a password protected *DNS* which is what I am dealing with and a password protected database which is what the kb article deals with. Very different things.

I would prefer to open the Access Application object with AccessApp.OpenCurrentDatabase method but I am looking into other options. That is why I thought maybe opening the database with ADO might enable me to open the DSN connection *with* the user name and password in the connection string. It is sort of a circular problem but first of all, I have to figure out how to open the DSN connection with a hardcoded password and *then* open up the Access Application object with a similar method.

 

[the_utmost]

We are using an Oracle backend. There are system DSN's that are created on each computer. If I go to Control Panel - Admin Tools - Data Sources (ODBC) I have a list of connections to Oracle. There are System DSN's and my reports use one. It uses an Oracle ODBC driver and whenever we start it up, it asks for a user name and password.

 

[the_utmost]

This is how I see it. When I link my Access application object, that is when I get prompted for the DSN password.

Dim AccessApp As Access.Application

Set AccessApp = CreateObject("access.application")
AccessApp.OpenCurrentDatabase (strPath) '* I get prompted for the user name & password here.


So it is not necessarily the database connection, but the method of the object. Should I use the above code and replace strPath with strConnect?