Report based on rst in other database

What is the best way to create a report that is based upon a recordset in
another database that has security implemented?

Arlene

Hi, Arlene.

First join the secure workgroup, then open the database where you want to
create the new report. Next, create a new remote query with the tables in
the secure database as the data sources. Then create a new report with this
query as the report's Record Source Property. In the future, as long as the
user is first joined to the secure database and authenticates with his User
ID and password, the report can be opened by the user. If the user doesn't
join the secure workgroup before attempting to open the report, then it won't
open.

Example SQL statement for the remote query:

SELECT *
FROM [;DATABASE=C:\Work\MyData.mdb;].tblOrders
ORDER BY CustomerID;

.. . . where the secure database path and file name is C:\Work\MyData.mdb,
and tblOrders is the name of the table in that database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"swedbera" wrote:

What is the best way to create a report that is based upon a recordset in
another database that has security implemented?

Arlene

Hi Gunny,

Thanks for responding.

I forgot to mention that my database is also secured and that the users of
my database are not the same users as in this other secured database. I
assumed that I would have to ask the other database owner to create a login
that I would use to connect to run this report. Another problem I have here
is that there is no standard anything as far as IT goes. Everyone maps
drives differently etc., so can I use a UNC path to point to this other
database in my code?

Now, when you say to create a new remote query and a report, do you mean in
the source database or in my database?

Arlene

"'69 Camaro" wrote:

Hi, Arlene.

First join the secure workgroup, then open the database where you want to
create the new report. Next, create a new remote query with the tables in
the secure database as the data sources. Then create a new report with this
query as the report's Record Source Property. In the future, as long as the
user is first joined to the secure database and authenticates with his User
ID and password, the report can be opened by the user. If the user doesn't
join the secure workgroup before attempting to open the report, then it won't
open.

Example SQL statement for the remote query:

SELECT *
FROM [;DATABASE=C:\Work\MyData.mdb;].tblOrders
ORDER BY CustomerID;

. . . where the secure database path and file name is C:\Work\MyData.mdb,
and tblOrders is the name of the table in that database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"swedbera" wrote:

What is the best way to create a report that is based upon a recordset in
another database that has security implemented?

Arlene

Hi, Arlene.

I forgot to mention

That makes life more difficult. For this to work, the other database owner
must create new users in the other secure workgroup. (Sometimes, that's like
pulling teeth to get them to agree to allow other users into their
databases.) These new users must match exactly in User ID and PID with those
in your own secure workgroup. When the users join their own secure workgroup
and try to open the report whose data source is residing in another database
secured by a different workgroup, they'll be prompted for their User ID and
password, and they should be recognized as members of that other secure
workgroup. If they aren't recognized, then you'll need to alter your own
secure workgroup to include the Group name and PID of the Group allowed to
access the table (actually, it should be an RWOP query, not a table) in the
other secure database.

can I use a UNC path to point to this other
database in my code?

The UNC path is recommended for networked databases, so it will be fine for
pointing to the report's source database.

Now, when you say to create a new remote query and a report, do you mean in
the source database or in my database?

Create the new remote query and report in your own database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"swedbera" wrote:

Hi Gunny,

Thanks for responding.

I forgot to mention that my database is also secured and that the users of
my database are not the same users as in this other secured database. I
assumed that I would have to ask the other database owner to create a login
that I would use to connect to run this report. Another problem I have here
is that there is no standard anything as far as IT goes. Everyone maps
drives differently etc., so can I use a UNC path to point to this other
database in my code?

Now, when you say to create a new remote query and a report, do you mean in
the source database or in my database?

Arlene

"'69 Camaro" wrote:

Hi, Arlene.

First join the secure workgroup, then open the database where you want to
create the new report. Next, create a new remote query with the tables in
the secure database as the data sources. Then create a new report with this
query as the report's Record Source Property. In the future, as long as the
user is first joined to the secure database and authenticates with his User
ID and password, the report can be opened by the user. If the user doesn't
join the secure workgroup before attempting to open the report, then it won't
open.

Example SQL statement for the remote query:

SELECT *
FROM [;DATABASE=C:\Work\MyData.mdb;].tblOrders
ORDER BY CustomerID;

. . . where the secure database path and file name is C:\Work\MyData.mdb,
and tblOrders is the name of the table in that database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"swedbera" wrote:

What is the best way to create a report that is based upon a recordset in
another database that has security implemented?

Arlene

Hi Gunny,

Even though this other database owner has created individual userid's based
upon the user's actual network id, I have created mine with logins that are
more like an SQL login or role. I do track who is logging in and what their
network username and machinename are so that I can track who has been in
there, but I did not want the hassle of having to apply permissions to every
Access object for so many users. I thought that this would work and this
other person would only have to create one login for me to connect and run
this report. Can't I hard code the username and password to whatever
username this person creates and include this in the connection string?

Arlene

"'69 Camaro" wrote:

Hi, Arlene.

I forgot to mention

That makes life more difficult. For this to work, the other database owner
must create new users in the other secure workgroup. (Sometimes, that's like
pulling teeth to get them to agree to allow other users into their
databases.) These new users must match exactly in User ID and PID with those
in your own secure workgroup. When the users join their own secure workgroup
and try to open the report whose data source is residing in another database
secured by a different workgroup, they'll be prompted for their User ID and
password, and they should be recognized as members of that other secure
workgroup. If they aren't recognized, then you'll need to alter your own
secure workgroup to include the Group name and PID of the Group allowed to
access the table (actually, it should be an RWOP query, not a table) in the
other secure database.

can I use a UNC path to point to this other
database in my code?

The UNC path is recommended for networked databases, so it will be fine for
pointing to the report's source database.

Now, when you say to create a new remote query and a report, do you mean in
the source database or in my database?

Create the new remote query and report in your own database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"swedbera" wrote:

Hi Gunny,

Thanks for responding.

I forgot to mention that my database is also secured and that the users of
my database are not the same users as in this other secured database. I
assumed that I would have to ask the other database owner to create a login
that I would use to connect to run this report. Another problem I have here
is that there is no standard anything as far as IT goes. Everyone maps
drives differently etc., so can I use a UNC path to point to this other
database in my code?

Now, when you say to create a new remote query and a report, do you mean in
the source database or in my database?

Arlene

"'69 Camaro" wrote:

Hi, Arlene.

First join the secure workgroup, then open the database where you want to
create the new report. Next, create a new remote query with the tables in
the secure database as the data sources. Then create a new report with this
query as the report's Record Source Property. In the future, as long as the
user is first joined to the secure database and authenticates with his User
ID and password, the report can be opened by the user. If the user doesn't
join the secure workgroup before attempting to open the report, then it won't
open.

Example SQL statement for the remote query:

SELECT *
FROM [;DATABASE=C:\Work\MyData.mdb;].tblOrders
ORDER BY CustomerID;

. . . where the secure database path and file name is C:\Work\MyData.mdb,
and tblOrders is the name of the table in that database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"swedbera" wrote:

What is the best way to create a report that is based upon a recordset in
another database that has security implemented?

Arlene

Hi, Arlene.

Can't I hard code the username and password

Hard code it? That wouldn't be very secure, now would it?

to whatever
username this person creates and include this in the connection string?

And have everybody share the same password? Passwords are supposed to be
guarded like toothbrushes. You want these people to share the same
toothbrush?!!

I'm giving you a hard time. ;-) The bottom line is that one cannot assign
both a User ID and workgroup file within the same connection string in a
remote query. Either the user has already joined the other workgroup file as
a particular user before the query is run -- or he hasn't (in which case the
secure database won't be accessible to the user).

If the User ID and PID aren't identical in both workgroups, then you have
two choices:

1.) Have the user manually change workgroups by selecting the Tools -
Security - Workgroup Administrator menu (which will kick him out of the
current database unless you create an identical "shared User ID" from the
other workgroup in your own workgroup), then open the report that's based
upon the remote query. Be aware that the current database closes when the
user changes to another workgroup through the GUI.

2.) Use VBA code to connect to the secure database, then open up the report
from that database (yes, this means that the report must reside in the other
database, unless you know how to code around this).

Really, the best way to share secure Access databases is to use the same
workgroup information file, not swap back and forth.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"swedbera" wrote:

Hi Gunny,

Even though this other database owner has created individual userid's based
upon the user's actual network id, I have created mine with logins that are
more like an SQL login or role. I do track who is logging in and what their
network username and machinename are so that I can track who has been in
there, but I did not want the hassle of having to apply permissions to every
Access object for so many users. I thought that this would work and this
other person would only have to create one login for me to connect and run
this report. Can't I hard code the username and password to whatever
username this person creates and include this in the connection string?

Arlene

"'69 Camaro" wrote:

Hi, Arlene.

I forgot to mention

That makes life more difficult. For this to work, the other database owner
must create new users in the other secure workgroup. (Sometimes, that's like
pulling teeth to get them to agree to allow other users into their
databases.) These new users must match exactly in User ID and PID with those
in your own secure workgroup. When the users join their own secure workgroup
and try to open the report whose data source is residing in another database
secured by a different workgroup, they'll be prompted for their User ID and
password, and they should be recognized as members of that other secure
workgroup. If they aren't recognized, then you'll need to alter your own
secure workgroup to include the Group name and PID of the Group allowed to
access the table (actually, it should be an RWOP query, not a table) in the
other secure database.

can I use a UNC path to point to this other
database in my code?

The UNC path is recommended for networked databases, so it will be fine for
pointing to the report's source database.

Now, when you say to create a new remote query and a report, do you mean in
the source database or in my database?

Create the new remote query and report in your own database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"swedbera" wrote:

Hi Gunny,

Thanks for responding.

I forgot to mention that my database is also secured and that the users of
my database are not the same users as in this other secured database. I
assumed that I would have to ask the other database owner to create a login
that I would use to connect to run this report. Another problem I have here
is that there is no standard anything as far as IT goes. Everyone maps
drives differently etc., so can I use a UNC path to point to this other
database in my code?

Now, when you say to create a new remote query and a report, do you mean in
the source database or in my database?

Arlene

"'69 Camaro" wrote:

Hi, Arlene.

First join the secure workgroup, then open the database where you want to
create the new report. Next, create a new remote query with the tables in
the secure database as the data sources. Then create a new report with this
query as the report's Record Source Property. In the future, as long as the
user is first joined to the secure database and authenticates with his User
ID and password, the report can be opened by the user. If the user doesn't
join the secure workgroup before attempting to open the report, then it won't
open.

Example SQL statement for the remote query:

SELECT *
FROM [;DATABASE=C:\Work\MyData.mdb;].tblOrders
ORDER BY CustomerID;

. . . where the secure database path and file name is C:\Work\MyData.mdb,
and tblOrders is the name of the table in that database.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address so that a message will
be forwarded to me.)
- - -
If my answer has helped you, please sign in and answer yes to the question
"Did this post answer your question?" at the bottom of the message, which
adds your question and the answers to the database of answers. Remember that
questions answered the quickest are often from those who have a history of
rewarding the contributors who have taken the time to answer questions
correctly.


"swedbera" wrote:

What is the best way to create a report that is based upon a recordset in
another database that has security implemented?

Arlene