G
Guest
What is the best way to create a report that is based upon a recordset in
another database that has security implemented?
Arlene
another database that has security implemented?
Arlene
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
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.
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.
G
Guest
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
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 said: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 said:What is the best way to create a report that is based upon a recordset in
another database that has security implemented?
Arlene
G
Guest
Hi, Arlene.
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.
The UNC path is recommended for networked databases, so it will be fine for
pointing to the report's source 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.
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 said: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 said: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 said:What is the best way to create a report that is based upon a recordset in
another database that has security implemented?
Arlene
G
Guest
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
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 said: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 said: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 said: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.
:
What is the best way to create a report that is based upon a recordset in
another database that has security implemented?
Arlene
G
Guest
Hi, Arlene.
Hard code it? That wouldn't be very secure, now would it?
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.
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 said: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 said: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 said: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
:
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.
:
What is the best way to create a report that is based upon a recordset in
another database that has security implemented?
Arlene