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?

 

[Pat Hartman]

DAO is the native method for Access databases. It is more efficient to use DAO than ADO when your data source is Jet tables. DAO is optimized for Jet. ADO is a general purpose, high level, tool for accessing data. Additionally, DAO supports the entire Access object model whereas ADO does not, nor should it. Despite the buzz when A2K appeared, DAO is alive and well and quite integral to working in Access. In fact, in A2003 it is back as the default access method.

 

[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?

 

[Pat Hartman]

This kb article should solve the UID/password problem using DAO:
Cannot Connect Data Control to a Password-Protected Access Database

 

[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.

 

[Pat Hartman]

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.

enlighten me please. I've never heard of a password protected DNS. How are they created? Access (actually Jet) security doesn't mention them at all.

 

[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.

 

[Pat Hartman]

If the Access database is secured and the Oracle tables are secured, you will need logins for both. Use the referenced article to log into the Access database and the following is DAO for logging in to Oracle:

Public Function ConnectToOracleBatch()
Dim WgtDB As Database
Dim strConnect As String
    
Set WgtDB = CurrentDb()
strConnect = "ODBC;UID=WGT;PWD=weight;DSN=weight;Database=WGT"
Set WgtDB = OpenDatabase("", False, False, strConnect)
strConnect = "ODBC;UID=FARMS_EXTRACT;PWD=mike;DSN=farms;Database=farms"
Set WgtDB = OpenDatabase("", False, False, strConnect)
WgtDB.Close

End Function

Another alternative is to delete the current links from the Access db to Oracle and relink the tables and check the option to save the password. This will store the password in the connection string so that you can just reference them and not have to log on.

 

[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?

 

[Pat Hartman]

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

- No, you only get prompted for the DSN when you actually try to reference a table and if you save the userid and password when you link the tables as I suggested, you will NEVER be prompted.