Login to FE as 2 different users?

[Guest]

Ok - I should have read all about security BEFORE I spent months and months
designing and developing my application (ARG!!!). Here is my delima - I have
over 150 SQL statements in VBA code and have setup user level security - when
I login as Admin (no permissions on BE) I get an error - no read permsssions
on table RWOP only works on querys (what a time to find this out!!)

....so I was wondering if I use the Workspace object and login to the Admins
group (have done this for using DAO to add tables and update fields) can I
then somehow set the Admins user as the current user that runs my vba code so
that all my SQL's will execute ?

Thanks for any help or suggestions!
Jerry

 

[Jeff Conrad]

Ouch!
Yes, it is too bad you realize this now.

I believe when using the Workspace object to do something that
the CurrentUser does not normally have permissions to do, you
are in fact that "other" person so to speak as far as Access is
concerned. However, from my experience it only lives for the
scope of the code involved. You cannot magically make Access
believe that you are a different CurrentUser globally if that is what
you are thinking. I'd be happy to be proven wrong though.

You'll either have to create that temporary workspace whenever
you need to execute those SQL statements or rewrite the
SQL statements to reference RWOP queries. Either way,
it does sound like a lot of work.

--
Jeff Conrad
Access Junkie - MVP
http://home.bendbroadband.com/conradsystems/accessjunkie.html
http://www.access.qbuilt.com/html/articles.html

in message:

 

[Guest]

Jeff - In some preliminary testing it appears as thought the database object
that I assigned using the the Opendatabase method with the new workspace does
not maintain it's value once I try to use it in another module (just as you
mentioned), will keep trying to see if I can figure out a strategy to get
myself out of this ugly mess!!

Thanks for the help! Will let you know if I find a way out!

Jerry

 

[robert d via AccessMonster.com]

I'm not sure I understand what your exact problem is, but I had a similar
situation, except that I have probably closer to 500 SQL statements in VBA
Code.

If your concern is that you need to rewrite your SQL statements, then let me
suggest an alternative that was suggested to me.

1) Leave your SQL statements as is
2) Instead, rename your tables by putting the letters 'tbl' in front of the
table name: so if table name is Products; new table name is tblProducts
3) Create RWOP queries on these tables that have the name that the table
previously had. So the name of the RWOP query in my example is Products.

That way you don't have to rewrite your SQL statements in code.

HTH

 

[Jeff Conrad]

in message:

Jeff - In some preliminary testing it appears as thought the database object
that I assigned using the the Opendatabase method with the new workspace does
not maintain it's value once I try to use it in another module (just as you
mentioned), will keep trying to see if I can figure out a strategy to get
myself out of this ugly mess!!

Yep, that was my experience as well.

Thanks for the help! Will let you know if I find a way out!

Good luck,

 

[Jeff Conrad]

in message:

I'm not sure I understand what your exact problem is, but I had a similar
situation, except that I have probably closer to 500 SQL statements in VBA
Code.

If your concern is that you need to rewrite your SQL statements, then let me
suggest an alternative that was suggested to me.

1) Leave your SQL statements as is
2) Instead, rename your tables by putting the letters 'tbl' in front of the
table name: so if table name is Products; new table name is tblProducts
3) Create RWOP queries on these tables that have the name that the table
previously had. So the name of the RWOP query in my example is Products.

That way you don't have to rewrite your SQL statements in code.

Interesting idea Robert!

 

[TC]

Are you saying that you have 500 stired queries? I can;'t conceive of
how that would be necessary, in a typical desktop application. Are you
sure you can't use parameters, and WHERE clauses, and so on, to make a
smaller number of queries serve the same purpose?

TC

 

[Jack MacDonald]

Ouch!
Yes, it is too bad you realize this now.

I believe when using the Workspace object to do something that
the CurrentUser does not normally have permissions to do, you
are in fact that "other" person so to speak as far as Access is
concerned. However, from my experience it only lives for the
scope of the code involved. You cannot magically make Access
believe that you are a different CurrentUser globally if that is what
you are thinking. I'd be happy to be proven wrong though.

You'll either have to create that temporary workspace whenever
you need to execute those SQL statements or rewrite the
SQL statements to reference RWOP queries. Either way,
it does sound like a lot of work.

Jeff -- Never tried it, but I wonder whether its possible to create
two global variables. One workspace object and one database object.
They would live for the duration of the application. Couldn't you then
run queries against the global database object.

Not that creating global variables is a good idea...


**********************
(e-mail address removed)
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

 

[TC]

Sorry, I now see that you said 500 queries "in VBA code".

But it still seems like a very high number. Why do you have so many of
them?

Cheers,
TC

 

[robert d via AccessMonster.com]

It is high. I've been working on this app for about a year straight and I
keep adding more and more. Currently about 100 forms and 80 tables. I am
not a professional programmer and have learned as I've gone along. So there
is no doubt that I could probably significantly reduce the number.

None of my forms set a form recordsource because every form requires that
data be displayed from multiple tables using joins (I can honestly say that
only one of my tables is not normalized; at the time I didn't think it would
be a big problem, but this non-normalized table has come to haunt me; too
late now).

I realized I had over 500 queries when I had to add "dbSeeChanges" for the
creation of a version that accesses a SQLServer backend.

When a user uses the app, they generally only use a small portion at a time
and I've been careful (I hope) to close all DAO recordsets in code.
Speaking of that, is there a utility that can check to make sure that there
is recordset closing code in a module? To go back and check almost 15,000
lines of code would be a massive task.

 

[robert d via AccessMonster.com]

Yes, it is a clever idea. When I was forced to go to RWOP queries, I was in
a huge panic because I thought I would have to rewrite all of the VBA code
SQL. Fortunately, for us amateurs, there are forums like this where one can
get help ( I actually posted in a different forum than this one). I posted
for help and received the tip I've passed on here. It only took me about an
hour to an hour and a half to change all the table names and set up the RWOP
queries. If I had changed the over 500 SQL statements in code, it would have
taken days.

I'm not sure I understand what your exact problem is, but I had a similar
situation, except that I have probably closer to 500 SQL statements in VBA

[quoted text clipped - 10 lines]

That way you don't have to rewrite your SQL statements in code.

Interesting idea Robert!

 

[Guest]

Jack - I have done some testing with doing just as you siggested and it seems
to work - I created global variables for the Workspace and Database and then
used the db.execute "SQL statements" and they worked!

Some more testing needed but the approach will work - Have to change from
ADO to DAO recordsets also....

Jerry

 

[Guest]

Robert - Thanks for the suggestions, sounds like we had a similar issue - I
am testing the use of global variables, would be easier for me than creating
RWOP queries...

Thanks again!
Jerry

 

[Guest]

FYI ...
Also found that any Dlookup fubctions have to be changed to DAO.Recordset
statements - appears as Dlookup requires Read permissions...

 

[TC]

I'm not aware of any product that checks for recordset-closing code.
That would be fairly hard, anyway. For example:

set rs = db.openrecordset(...)
....
if whatever then exit sub
....
' oops, next code is present, but might never be executed!
rs.close
set rs = nothing

HTH,
TC

 

[robert d via AccessMonster.com]

Well, if that doesn't work for you fairly quickly, you really ought to
consider the suggestion. Once implemented it will be something you can count
on to work correctly.

For the RWOP queries, I keep it very simple in that I use:

SELECT * FROM tblProduct

If you can do it this way, you'll be able to create the RWOP queries quickly.
When you create an RWOP query, Access will automatically add the with
OwnerAccess Option to the SQL statement after you select Design View -->
Properties and then the Run Permissions line. In fact, I'm pretty sure there
is a way to set an option or something where all queries will be RWOP when
first created. So when you go to the SQL View to setup the query, the "WITH
OWNERACCESS OPTION" part is already there. When you're done you could then
change the option back, but I forget how this is done.

With regards, to global variables, I'm no expert, but it seems like they can
be killed fairly easily, which makes me nervous.

Good Luck

Robert - Thanks for the suggestions, sounds like we had a similar issue - I
am testing the use of global variables, would be easier for me than creating
RWOP queries...

Thanks again!
Jerry

I'm not sure I understand what your exact problem is, but I had a similar
situation, except that I have probably closer to 500 SQL statements in VBA

[quoted text clipped - 26 lines]

 

[david epsom dot com dot au]

Dlookup is a method of the Application object.

It uses
Application.dbengine.workspaces(0).databases(0)
ie CurrentDB.

(david)

 

[Guest]

David,

Is there a way to have it use the secure workspace variable so that I don't
have to change all my dlookups? if my variable is dbSecure would I use
dbSecure.Dlookup?

Thanks
Jerry

 

[Guest]

Thanks Robert - I am making good progress using the global variables, can
easily change all my SQL statements, most of my queries are somewhat complex
so recreating them is time consuming -

Have not had any issues with the global variable (so far!).

Thanks for the ideas and input!

Jerry

Well, if that doesn't work for you fairly quickly, you really ought to
consider the suggestion. Once implemented it will be something you can count
on to work correctly.

For the RWOP queries, I keep it very simple in that I use:

SELECT * FROM tblProduct

If you can do it this way, you'll be able to create the RWOP queries quickly.
When you create an RWOP query, Access will automatically add the with
OwnerAccess Option to the SQL statement after you select Design View -->
Properties and then the Run Permissions line. In fact, I'm pretty sure there
is a way to set an option or something where all queries will be RWOP when
first created. So when you go to the SQL View to setup the query, the "WITH
OWNERACCESS OPTION" part is already there. When you're done you could then
change the option back, but I forget how this is done.

With regards, to global variables, I'm no expert, but it seems like they can
be killed fairly easily, which makes me nervous.

Good Luck

Robert - Thanks for the suggestions, sounds like we had a similar issue - I
am testing the use of global variables, would be easier for me than creating
RWOP queries...

Thanks again!
Jerry

I'm not sure I understand what your exact problem is, but I had a similar
situation, except that I have probably closer to 500 SQL statements in VBA

[quoted text clipped - 26 lines]

Thanks for any help or suggestions!
Jerry

 

[Jeff Conrad]

in message:

Jack - I have done some testing with doing just as you siggested and it seems
to work - I created global variables for the Workspace and Database and then
used the db.execute "SQL statements" and they worked!

Some more testing needed but the approach will work - Have to change from
ADO to DAO recordsets also....

Very interesting Jerry.
Thanks for sharing.