SystemDB property of DBEngine through DAO

[Woody]

I have worked extensively with Access and am currently developing a sample
program in Delphi that uses the DAO to show people how to access an Access
7.0 (DAO 3.0) database, create them, modify them, etc., etc..

I am having problems in one area. When creating an OLE connection through
the DAO using the CreateOLEObject('DAO.DBEngine') function, I need to set
the system database to allow modifications to the user security attributes.
However, I always get a message about not being able to find the system
database or that it is opened exclusively by another user as soon as I try
to instantiate a Workspace object. I have looked through Microsofts' KB and
found one article regarding this but it was for VB. Basically, it says that
in order to use the DBEngine.SystemDB setting, you must also set the
DefaultUser and DefaultPassword options. Well, I have done this as well but
it still does not work. Same error. I have tried the default workgroup file
as well as creating my own.

Can anyone shed some light on this problem? I also saw where this was a
problem in Access 97 but is circumvented by supplying the TYPE parameter in
the CreateWorkspace call. I am only interested in Access 95, though.

Thanks for any input or direction you can give...........
--

Woody

[Alden Streeter]

Hi Woody,

Don't get many Delphi programmers in these Microsoft groups. Nice to see
Access has value to non-MS devotees.

My idea:

You are setting the SystemDB property before you use the CreateWorkspace
method of the DBEngine, aren't you? You didn't mention using that method so
that is my first best guess. You have to use that method to get a Workspace
object.

Here is the code (Access VB, sorry, but you can get the gist) I use:

Dim strMDW As String
Dim strName As String
Dim strUser As String
Dim strPwd As String
Dim wsp As Workspace

DBEngine.SystemDB = strMDW
Set wsp = DBEngine.CreateWorkspace(strName, strUser, strPwd)

I have found (in VBA and Access VB anyway, don't know any Delphi), that even
though the help file says you have to use the DefaultUser and
DefaultPassword properties, it is sufficient to just use the arguments for
the user and password in the CreateWorkspace method.

Alden

Woody wrote in message <76rq6c$pi8$1...@remarQ.com>...

[Woody]

Thanks Alden. Yes, I am setting the SystemDB property before using the
CreateWorkSpace. It still doesn't seem to work. I don't know if there is
something particular about using the DAO through another package besides VBA
in Access itself. The help file for Access suggests that the SystemDB needs
to be set before instatiating the DBEgine object (i.e: CreateWorkSpace) and
I am not sure that by calling the DAO to create a new DBEngine that it is
not already instatiating it. I really have no way of knowing.

I have created some pretty good code in VBA in Access itself so I am real
familiar with it's workings. Unfortunately, there are no good code examples
except for VB to go by.

The following code is what I use to create an OLE object to DAO in my Delphi
program:

var
DBEngine: variant;
DBWorkSpace: variant;

begin
DBEngine := CreateOLEObject('DAO.DBEngine');
DBEngine.SystemDB := 'c:\windows\system\MyGroup.mdb'
DBWorkSpace := DBEngine.CreateWorkSpace('Sample','Admin','');

...... more code.....

end;

The problem is that no matter how I go about it, including changing the
MyGroup.mdb to MyGroup.mdw, it won't fly. If I look at the value of SystemDB
before I try to set it, it is blank. Even though I have my system set to
specific workgroup file using WrkGAdmin. Oh well, I'll keep trying. Thanks
for the response.

--

Woody

Alden Streeter <astr...@email.msn.com> wrote in message
news:Ohsq$nPO#GA....@uppssnewspub05.moswest.msn.net...

[Terry Kreft]

Hi Woody,
This is how I do it in a VBA Class module (I've chopped out the
non-essentials)

Private Sub Class_Initialize()
Set loDBEngine = New PrivDBEngine
End Sub

Function OpenDatabase(Name As String, Optional Options, Optional ReadOnly As
Boolean, Optional Connect As String)

If Len(strSysDb) > 0 Then
If Len(Name) > 0 And Len(Dir(Name)) > 0 Then
With loDBEngine
.SystemDB = strSysDb
Set loWs = .CreateWorkspace("#default#", strUserName, strPass)
End With
Set lodb = loWs.OpenDatabase(Name, Options, ReadOnly, Connect)
Set OpenDatabase = lodb
Call Me.GetDBProperties
Else
Err.Raise 65533 + vbObjectError, strDescription, "Can't find
Database"
End If
Else
Err.Raise 65534 + vbObjectError, strDescription, "System database not
defined"
End If
End Function

You can see from the above that the DBEngine is instantiated first (in the
Class initialisation) the Systemdb property is then assigned and the
CreateWorkspace method is used (this is pretty much as Alden has said
before).

You can also use the DefaultUser and DefaultPassword properties of the
DBEngine instead of stipulating them in the CreateWorkspace method.

Woody <wood...@ih2000.net> wrote in message
news:76u6ir$9t7$1...@remarQ.com...

[Woody]

I realize that this is a bit different than doing it in Delphi. I can do
everything you say in VBA and it all works of course. However, VBA is tied
closely with the DAO and may work a bit differently, I don't know for sure.
Basically, I do the same thing that you are doing here but in Delphi Pascal
syntax. The problem, however, still occurs and the error is still there.

I will keep searching..... Thanks for the help....

--

Woody

Terry Kreft <terry...@mps.co.uk> wrote in message
news:76vgm5$4k3$1...@gate.mps.co.uk...

[Alden Streeter]

Ok here's one more random guess then. I have experienced when distributing
run-time Access applications on machines that didn't have Access on them
that run with the run-time version of Access, that it won't let you change
to a custom secured workgroup (.mdw) file using Workgroup Administrator or
using the msaccess.exe command line /wrkgrp shortcut, unless you have the
default unsecured system.mdw file in the Windows\System folder. (This was
never a problem when changing the workgroup file with VB code, however.) It
gives the same error message you described originally. It's like it needs
something to change from in order to change to the workgroup file you really
want to use. So maybe just put that default system.mdw file in that system
folder on the affected machine. You can make a new default one with
Workgroup Administrator if you need to.

Alden

Woody wrote in message <76vjja$il6$1...@remarQ.com>...

[Woody]

Been there, Done that.....

Good thought, though. I had the same a few days ago and tried putting the
default system.mdw in both the Windows and Windows\System directories just
in case. It doesn't seem to change anything.

The main thing that gets me is that when I instantiate the OLE DBEngine, it
contains nothing in the SystemDB property originally. I would have thought
that since I used WrkGAdmin to explicitly join a group, the DAO should
create an engine that is assigned to that particular group already. I think
my problem may lie in there somewhere, but I don't know for sure.

--

Woody

Alden Streeter <astr...@email.msn.com> wrote in message

news:ekQxQkaO#GA....@uppssnewspub04.moswest.msn.net...

[Woody]

OK, I don't know what I've done but it is working now like it should. I can
change the SystemDB property before accessing the CreateWorkSpace method and
it uses the correct MDW file. I appreciate all the help.

--

Woody

Woody <wood...@ih2000.net> wrote in message

news:770ggr$54g$1...@remarQ.com...