Windows Forms No-Touch Deployment problems using DAO on User-Level Secured Access DB to access groups

I have a VB windows forms application that accesses a Microsoft Access
database that has been secured using user-level security. The
application is being deployed using No-Touch deployment. The objective
in utilizing this new deployment method is to reduce the maintenance
overhead as well as making it easier for my users to setup and run the
application initially. I have VS 2002, Windows XP, Access XP(2000
format).

He is my problem. Since the database is secured, I must provide a way
for my users to change their passwords. Also, some forms access tables
that certain permission groups do not have access to. So instead of
letting the error come up when they open the form and try to update,
at logon I am searching through the groups that the user belongs to
and locking out access to these forms if they do not have permission.
The only way I know to do these 2 functions is to use DAO. However,
for accessing and updating my data, I'm using ADO.NET.

So I have included DAO 3.6 using Project>>Add References>>COM tab in
the VS project.
I No-Touch deployed it and most users are working, running the gamut
from Windows NT 4.0 to Windows XP, admins and non-admins.

Then there was this 1 machine, then another...It is now 3 machines.
Each have different errors or none at all(app won't even open). On 1
of the 3 I have a strong belief due to the nature of the errors that
it is just something wrong with the machine. The user has reported
problems with the unit before my app was placed on it.

Now on the last 2 machines....1 is Windows NT 4.0 and crashes before
the app even opens, the other is Windows 2000 and has the error of
"QueryInterface for interface DAO._DBEngine failed.". I thought that
maybe installing MDAC would work. So I put the version of MDAC that
all the machines have including mine(2.6) on both, and same error. I
have also made sure that the Jet 4.0 engine is setup and current. I
thought that maybe the dao360.dll was not properly registered or set
up which is why I did these steps. No change. I checked the registry
and "HKEY_CLASSES_ROOT\DAO.DBEngine.36\CLSID" is present. I don't know
if that even matters. Also, I have a VB6 app that uses dao360.dll on
these machines and this VB6 app works. The more I work on this, the
more I think it is not just this machine, but rather something(s) I'm
doing wrong in .NET(also taking into account the 2 other users and
even more mentioned below). The framework version on these machines
is the same as mine 1.0.3705.

NOW HERE IS THE DOOZY:
Since this is No-Touch deployment I am accessing the app by clicking a
link in the browser of course(IE 6.0). It errors. If I instead browse
out to the folder using Windows Explorer....IT RUNS FINE ON BOTH
MACHINES!!? My limited knowledge about .NET No-Touch doesn't help me
understand this much. This re-enforces my belief it is me and not the
just the machines.

But if I understand right, doesn't No-Touch put the assemblies in the
GAC. Maybe I can force what I need into the GAC or something along
those lines. How could I remedy/do this? It seems that on the NT 4.0
machine it can't find any assemblies even to open and the Windows 2000
machine just can't find the DAO assembly.....but I'm probably way
off??

Here is the .NET code and the spot that errors out when No-Touch is
used:
Dim wks As DAO.Workspace
Dim theEngine As New DAO.DBEngine()
' Open the workspace, specifying the system database to use

'***************** error
theEngine.SystemDB = AccessDatabasePath & AccessWorkgroupName'this is
the line that errors
'***************** error

wks = theEngine.CreateWorkspace("", UserName, OldPassword)
' Change the password for the user Admin
wks.Users(UserName).NewPassword(OldPassword, NewPassword)
ChangePassword = True

'I have a loop that gets the users permission groups here

As a matter of fact if I try to reference any of the DBEngine's
methods or properties it errors on this machine.

Also,

I tried latebinding:
Dim theEngine As DAO.DBEngine
theEngine = CreateObject("DAO.DBEngine.36")

and it errors with a message: "Specified cast is not valid."

Here is the VB6 code that works:

Dim wks As DAO.Workspace
Dim usr As DAO.User
' Open the workspace, specifying the system database to use
DBEngine.SystemDB = theSysDB
Set wks = DBEngine.CreateWorkspace("", UserName, OldPassword)
' Change the password for the user Admin
wks.Users(UserName).NewPassword OldPassword, NewPassword
DAOChangePassword = True


WHAT I'D LIKE TO KNOW:
I know that .NET is trying to avoid "DLL HELL" and that by using COM
items I'm throwing myself back into it. I'd love to use ADO.NET(or
anything else in the framework) to do what I'm doing with DAO
but....how....can it even do it(getting user group and password in
from *.mdw)?

How does No-Touch work, for instance, the point I made above about the
app running from the folder and not the browser, could the method of
No-Touch deployment be the problem, and if so how can one fix it,
something like forcing stuff into the GAC or other method? Basically
why does it work when executed from the folder directly, but not from
the Browser using No-Touch deployment on this particular machine(yes I
know you would have to see the machine, but what COULD it be, maybe
just some things for me to check)?

Even if ADO.NET or other item can do what the DAO is doing I'd still
like to know the way to get COM items to work with the "No-Touch"
philosophy. Meaning little to no maintenance. In addition to the 3
user mentioned above I have had others with errors. I was able to
resolve them simply by installing MDAC 2.6 or later. This kind of
defeats my purpose.

Is it possible to deploy(via No-Touch deployment) projects using COM
items and still fully reap the benefits of No-Touch in every case(ie
no DLL HELL)?

Is there some special tricks that can perhaps still use COM items and
avoid "DLL HELL", or is it that if you go using COM items your just
stuck taking the risks?
If there are tricks, what are they?

If no tricks, then if COM must be used, are there precautions that can
reduce the problems?

Are there any books, etc. that are very thorough on "No-Touch"
deployment

Any other pointers would be most appreciated. Thanks in advance.

JL

re: Windows Forms No-Touch Deployment problems using DAO on User-Level Secured Access DB to access groups

James,

Here are some things that came to my mind when I read your text:

* You can change a user's password by executing the Microsoft
Jet-specific "ALTER USER" SQL command.

* You can access information from the DAO object model indirectly by
adding query objects to your database and by calling Visual Basic
function procedures in those queries. For example, you get find out if
the user is in specific groups as follows:

1) Add a module to the database containing the following procedure:

Function IsCurrentUserMemberOf(GroupName As String)
Dim Workspace As Workspace
Set Workspace = DBEngine.Workspaces(0)
Dim User As User
Set User = Workspace.Users(Workspace.UserName)
Dim Group As Group
For Each Group In User.Groups
If Group.Name = GroupName Then
IsCurrentUserMemberOf = True
Exit Function
End If
Next
IsCurrentUserMemberOf = False
End Function

2) Add a named query to the database containing the following SQL statement:

SELECT IsCurrentUserMemberOf("Admins") As MemberOfAdmins,
IsCurrentUserMemberOf("Users") As MemberOfUsers

3) Suppose this query is called UserInfo. In your client app, execute
the query using an SQL statement like

SELECT MemberOfAdmins, MemberOfUsers FROM UserInfo

* Your problem with COM may be related to the .NET Framework Code Access
Security functionality. An assembly that uses COM interop needs full
trust. The default .NET Framework security policy assigns full trust
only to assemblies in the My Computer zone. By default, assemblies on
network shares or from http URLs cannot use COM interop. You can change
the security policy using the .NET Framework Configuration snap-in or by
installing an MSI file created using the same snap-in on another machine.

* I hope that you are aware that putting an Access database on a network
share to which all users have read and write access, and enforcing
security only inside the Access application, does not provide perfect
security. For example, any user could just copy a database they created
themselves over your database file. Also, I'm sure cracks exist that
allow any user with an account in the database to gain complete access
to all objects; such cracks are at least theoretically possible. A more
secure setup would be to move to a client-server architecture, such as
the free Microsoft Data Engine (which is, however, limited to 5
concurrent users) or some other relational database server.

* In recent versions of Windows, there is support for using COM without
DLL hell. It is known as side-by-side deployment. This allows you to put
COM component DLLs in the same directory as your executable, along with
some extra XML files called manifests, so that your application will not
look in the registry to find a COM component; instead, it will use the
version deployed with your app. This allows so-called "XCOPY"
deployment. (Personally, I would call it drag-and-drop deployment, which
sounds less archaic.) That being said, however, I do not know if the DAO
component supports side-by-side deployment.

* Microsoft realizes that No-Touch Deployment, as it exists in the
current versions of the .NET Framework, is not perfect. The next version
(code-name Whidbey) will support a new deployment approach called
ClickOnce, which they claim will solve some of the issues with No-Touch
Deployment.

* Some tips on debugging No-Touch Deployed apps:
- You can see which permissions are assigned to an assembly using the
Evaluate Assembly command in the .NET Framework Configuration snap-in.
Just type the assembly's network path or URL in the Assembly File text box.
- Sometimes a failure generates an entry in the Fusion log, which you
can view using the FusLogVw.exe program that is in the "bin"
subdirectory of the .NET Framework SDK directory.

Hope this helps...

Bart Jacobs

James wrote:
[color=blue]
> I have a VB windows forms application that accesses a Microsoft Access
> database that has been secured using user-level security. The
> application is being deployed using No-Touch deployment. The objective
> in utilizing this new deployment method is to reduce the maintenance
> overhead as well as making it easier for my users to setup and run the
> application initially. I have VS 2002, Windows XP, Access XP(2000
> format).
>
> He is my problem. Since the database is secured, I must provide a way
> for my users to change their passwords. Also, some forms access tables
> that certain permission groups do not have access to. So instead of
> letting the error come up when they open the form and try to update,
> at logon I am searching through the groups that the user belongs to
> and locking out access to these forms if they do not have permission.
> The only way I know to do these 2 functions is to use DAO. However,
> for accessing and updating my data, I'm using ADO.NET.
>
> So I have included DAO 3.6 using Project>>Add References>>COM tab in
> the VS project.
> I No-Touch deployed it and most users are working, running the gamut
> from Windows NT 4.0 to Windows XP, admins and non-admins.
>
> Then there was this 1 machine, then another...It is now 3 machines.
> Each have different errors or none at all(app won't even open). On 1
> of the 3 I have a strong belief due to the nature of the errors that
> it is just something wrong with the machine. The user has reported
> problems with the unit before my app was placed on it.
>
> Now on the last 2 machines....1 is Windows NT 4.0 and crashes before
> the app even opens, the other is Windows 2000 and has the error of
> "QueryInterface for interface DAO._DBEngine failed.". I thought that
> maybe installing MDAC would work. So I put the version of MDAC that
> all the machines have including mine(2.6) on both, and same error. I
> have also made sure that the Jet 4.0 engine is setup and current. I
> thought that maybe the dao360.dll was not properly registered or set
> up which is why I did these steps. No change. I checked the registry
> and "HKEY_CLASSES_ROOT\DAO.DBEngine.36\CLSID" is present. I don't know
> if that even matters. Also, I have a VB6 app that uses dao360.dll on
> these machines and this VB6 app works. The more I work on this, the
> more I think it is not just this machine, but rather something(s) I'm
> doing wrong in .NET(also taking into account the 2 other users and
> even more mentioned below). The framework version on these machines
> is the same as mine 1.0.3705.
>
> NOW HERE IS THE DOOZY:
> Since this is No-Touch deployment I am accessing the app by clicking a
> link in the browser of course(IE 6.0). It errors. If I instead browse
> out to the folder using Windows Explorer....IT RUNS FINE ON BOTH
> MACHINES!!? My limited knowledge about .NET No-Touch doesn't help me
> understand this much. This re-enforces my belief it is me and not the
> just the machines.
>
> But if I understand right, doesn't No-Touch put the assemblies in the
> GAC. Maybe I can force what I need into the GAC or something along
> those lines. How could I remedy/do this? It seems that on the NT 4.0
> machine it can't find any assemblies even to open and the Windows 2000
> machine just can't find the DAO assembly.....but I'm probably way
> off??
>
> Here is the .NET code and the spot that errors out when No-Touch is
> used:
> Dim wks As DAO.Workspace
> Dim theEngine As New DAO.DBEngine()
> ' Open the workspace, specifying the system database to use
>
> '***************** error
> theEngine.SystemDB = AccessDatabasePath & AccessWorkgroupName'this is
> the line that errors
> '***************** error
>
> wks = theEngine.CreateWorkspace("", UserName, OldPassword)
> ' Change the password for the user Admin
> wks.Users(UserName).NewPassword(OldPassword, NewPassword)
> ChangePassword = True
>
> 'I have a loop that gets the users permission groups here
>
> As a matter of fact if I try to reference any of the DBEngine's
> methods or properties it errors on this machine.
>
> Also,
>
> I tried latebinding:
> Dim theEngine As DAO.DBEngine
> theEngine = CreateObject("DAO.DBEngine.36")
>
> and it errors with a message: "Specified cast is not valid."
>
> Here is the VB6 code that works:
>
> Dim wks As DAO.Workspace
> Dim usr As DAO.User
> ' Open the workspace, specifying the system database to use
> DBEngine.SystemDB = theSysDB
> Set wks = DBEngine.CreateWorkspace("", UserName, OldPassword)
> ' Change the password for the user Admin
> wks.Users(UserName).NewPassword OldPassword, NewPassword
> DAOChangePassword = True
>
>
> WHAT I'D LIKE TO KNOW:
> I know that .NET is trying to avoid "DLL HELL" and that by using COM
> items I'm throwing myself back into it. I'd love to use ADO.NET(or
> anything else in the framework) to do what I'm doing with DAO
> but....how....can it even do it(getting user group and password in
> from *.mdw)?
>
> How does No-Touch work, for instance, the point I made above about the
> app running from the folder and not the browser, could the method of
> No-Touch deployment be the problem, and if so how can one fix it,
> something like forcing stuff into the GAC or other method? Basically
> why does it work when executed from the folder directly, but not from
> the Browser using No-Touch deployment on this particular machine(yes I
> know you would have to see the machine, but what COULD it be, maybe
> just some things for me to check)?
>
> Even if ADO.NET or other item can do what the DAO is doing I'd still
> like to know the way to get COM items to work with the "No-Touch"
> philosophy. Meaning little to no maintenance. In addition to the 3
> user mentioned above I have had others with errors. I was able to
> resolve them simply by installing MDAC 2.6 or later. This kind of
> defeats my purpose.
>
> Is it possible to deploy(via No-Touch deployment) projects using COM
> items and still fully reap the benefits of No-Touch in every case(ie
> no DLL HELL)?
>
> Is there some special tricks that can perhaps still use COM items and
> avoid "DLL HELL", or is it that if you go using COM items your just
> stuck taking the risks?
> If there are tricks, what are they?
>
> If no tricks, then if COM must be used, are there precautions that can
> reduce the problems?
>
> Are there any books, etc. that are very thorough on "No-Touch"
> deployment
>
> Any other pointers would be most appreciated. Thanks in advance.
>
> JL[/color]

re: Windows Forms No-Touch Deployment problems using DAO on User-Level Secured Access DB to access groups

In case you can't get it to work, you might be able to cobble up something
with this:

http://www.dxonline.com/dxLoaderDetails.htm

------------------------------------------------------------------------
George Shubin Custom Software Development
dX Software Systems Database Applications
Ph: 503-981-6806 Fax: 503-982-0120
www.dxonline.com george@dxonline.com
------------------------------------------------------------------------


"James" <smooges1@yahoo.com> wrote in message
news:1ceaeb49.0311141127.237f7e2e@posting.google.c om...[color=blue]
> I have a VB windows forms application that accesses a Microsoft Access
> database that has been secured using user-level security. The
> application is being deployed using No-Touch deployment. The objective
> in utilizing this new deployment method is to reduce the maintenance
> overhead as well as making it easier for my users to setup and run the
> application initially. I have VS 2002, Windows XP, Access XP(2000
> format).
>
> He is my problem. Since the database is secured, I must provide a way
> for my users to change their passwords. Also, some forms access tables
> that certain permission groups do not have access to. So instead of
> letting the error come up when they open the form and try to update,
> at logon I am searching through the groups that the user belongs to
> and locking out access to these forms if they do not have permission.
> The only way I know to do these 2 functions is to use DAO. However,
> for accessing and updating my data, I'm using ADO.NET.
>
> So I have included DAO 3.6 using Project>>Add References>>COM tab in
> the VS project.
> I No-Touch deployed it and most users are working, running the gamut
> from Windows NT 4.0 to Windows XP, admins and non-admins.
>
> Then there was this 1 machine, then another...It is now 3 machines.
> Each have different errors or none at all(app won't even open). On 1
> of the 3 I have a strong belief due to the nature of the errors that
> it is just something wrong with the machine. The user has reported
> problems with the unit before my app was placed on it.
>
> Now on the last 2 machines....1 is Windows NT 4.0 and crashes before
> the app even opens, the other is Windows 2000 and has the error of
> "QueryInterface for interface DAO._DBEngine failed.". I thought that
> maybe installing MDAC would work. So I put the version of MDAC that
> all the machines have including mine(2.6) on both, and same error. I
> have also made sure that the Jet 4.0 engine is setup and current. I
> thought that maybe the dao360.dll was not properly registered or set
> up which is why I did these steps. No change. I checked the registry
> and "HKEY_CLASSES_ROOT\DAO.DBEngine.36\CLSID" is present. I don't know
> if that even matters. Also, I have a VB6 app that uses dao360.dll on
> these machines and this VB6 app works. The more I work on this, the
> more I think it is not just this machine, but rather something(s) I'm
> doing wrong in .NET(also taking into account the 2 other users and
> even more mentioned below). The framework version on these machines
> is the same as mine 1.0.3705.
>
> NOW HERE IS THE DOOZY:
> Since this is No-Touch deployment I am accessing the app by clicking a
> link in the browser of course(IE 6.0). It errors. If I instead browse
> out to the folder using Windows Explorer....IT RUNS FINE ON BOTH
> MACHINES!!? My limited knowledge about .NET No-Touch doesn't help me
> understand this much. This re-enforces my belief it is me and not the
> just the machines.
>
> But if I understand right, doesn't No-Touch put the assemblies in the
> GAC. Maybe I can force what I need into the GAC or something along
> those lines. How could I remedy/do this? It seems that on the NT 4.0
> machine it can't find any assemblies even to open and the Windows 2000
> machine just can't find the DAO assembly.....but I'm probably way
> off??
>
> Here is the .NET code and the spot that errors out when No-Touch is
> used:
> Dim wks As DAO.Workspace
> Dim theEngine As New DAO.DBEngine()
> ' Open the workspace, specifying the system database to use
>
> '***************** error
> theEngine.SystemDB = AccessDatabasePath & AccessWorkgroupName'this is
> the line that errors
> '***************** error
>
> wks = theEngine.CreateWorkspace("", UserName, OldPassword)
> ' Change the password for the user Admin
> wks.Users(UserName).NewPassword(OldPassword, NewPassword)
> ChangePassword = True
>
> 'I have a loop that gets the users permission groups here
>
> As a matter of fact if I try to reference any of the DBEngine's
> methods or properties it errors on this machine.
>
> Also,
>
> I tried latebinding:
> Dim theEngine As DAO.DBEngine
> theEngine = CreateObject("DAO.DBEngine.36")
>
> and it errors with a message: "Specified cast is not valid."
>
> Here is the VB6 code that works:
>
> Dim wks As DAO.Workspace
> Dim usr As DAO.User
> ' Open the workspace, specifying the system database to use
> DBEngine.SystemDB = theSysDB
> Set wks = DBEngine.CreateWorkspace("", UserName, OldPassword)
> ' Change the password for the user Admin
> wks.Users(UserName).NewPassword OldPassword, NewPassword
> DAOChangePassword = True
>
>
> WHAT I'D LIKE TO KNOW:
> I know that .NET is trying to avoid "DLL HELL" and that by using COM
> items I'm throwing myself back into it. I'd love to use ADO.NET(or
> anything else in the framework) to do what I'm doing with DAO
> but....how....can it even do it(getting user group and password in
> from *.mdw)?
>
> How does No-Touch work, for instance, the point I made above about the
> app running from the folder and not the browser, could the method of
> No-Touch deployment be the problem, and if so how can one fix it,
> something like forcing stuff into the GAC or other method? Basically
> why does it work when executed from the folder directly, but not from
> the Browser using No-Touch deployment on this particular machine(yes I
> know you would have to see the machine, but what COULD it be, maybe
> just some things for me to check)?
>
> Even if ADO.NET or other item can do what the DAO is doing I'd still
> like to know the way to get COM items to work with the "No-Touch"
> philosophy. Meaning little to no maintenance. In addition to the 3
> user mentioned above I have had others with errors. I was able to
> resolve them simply by installing MDAC 2.6 or later. This kind of
> defeats my purpose.
>
> Is it possible to deploy(via No-Touch deployment) projects using COM
> items and still fully reap the benefits of No-Touch in every case(ie
> no DLL HELL)?
>
> Is there some special tricks that can perhaps still use COM items and
> avoid "DLL HELL", or is it that if you go using COM items your just
> stuck taking the risks?
> If there are tricks, what are they?
>
> If no tricks, then if COM must be used, are there precautions that can
> reduce the problems?
>
> Are there any books, etc. that are very thorough on "No-Touch"
> deployment
>
> Any other pointers would be most appreciated. Thanks in advance.
>
> JL[/color]

re: Windows Forms No-Touch Deployment problems using DAO on User-Level Secured Access DB to access groups

Thanks, but I was looking more to using the technology provided with
..NET. I really don't intend to pay for any software to do what .NET is
supposed to do. Rather, I'd like to find out what I'm doing wrong or
could do differently to get my solution to work.


"George Shubin" <dx@dxonline.com> wrote in message news:<ed9nz6UrDHA.2188@TK2MSFTNGP09.phx.gbl>...[color=blue]
> In case you can't get it to work, you might be able to cobble up something
> with this:
>
> http://www.dxonline.com/dxLoaderDetails.htm
>
> ------------------------------------------------------------------------
> George Shubin Custom Software Development
> dX Software Systems Database Applications
> Ph: 503-981-6806 Fax: 503-982-0120
> www.dxonline.com george@dxonline.com
> ------------------------------------------------------------------------[/color]

re: Windows Forms No-Touch Deployment problems using DAO on User-Level Secured Access DB to access groups

I figured it out. I am posting this in case anyone else cares to know
what to do.

Since I am seemingly forced to use DAO(for accessing secured Microsoft
Access user group info), I threw myself into DLL HELL. There appears
to be no .NET alternative that works to do this. You cannot do it
indirectly as stated in a reply above.

Running MDAC(as I did) should have worked. But for some reason, on
this machine it did not install the type library (*.tlb) for dao 3.6.
The file in question is this case was dao2535.tlb.

So, I put the tlb in the same directory as the dao360.dll and used
regsvr32 on the dao360.dll. BOOM! Problem solved.

I am still VERY interested in getting a .NET solution to get the same
functionality in DAO that I need so please let me know.

Thanks for all your help,
JL

smooges1@yahoo.com (James) wrote in message news:<1ceaeb49.0311180513.4cc0e7d5@posting.google. com>...[color=blue]
> Thanks, but I was looking more to using the technology provided with
> .NET. I really don't intend to pay for any software to do what .NET is
> supposed to do. Rather, I'd like to find out what I'm doing wrong or
> could do differently to get my solution to work.
>
>
> "George Shubin" <dx@dxonline.com> wrote in message news:<ed9nz6UrDHA.2188@TK2MSFTNGP09.phx.gbl>...[color=green]
> > In case you can't get it to work, you might be able to cobble up something
> > with this:
> >
> > http://www.dxonline.com/dxLoaderDetails.htm
> >
> > ------------------------------------------------------------------------
> > George Shubin Custom Software Development
> > dX Software Systems Database Applications
> > Ph: 503-981-6806 Fax: 503-982-0120
> > www.dxonline.com george@dxonline.com
> > ------------------------------------------------------------------------[/color][/color]