Results 1 to 14 of 14
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Accessing Group Collection in Access from (Access 97/VB6)

    I am posting the code I've been experimenting with below.

    I created an application using Visual Basic with a Access 97 back end. I am limited to using Access 97 due to the terminals this application will be running on only have Access 97.

    I secured the 97 Database using the corresponding Access 97 MDW file. I've created a sign on form and am able to successfully connect a user to the back end data.

    My problem is that I need to access the Users and Groups collection. The ADOX library doesn't appear to work, probably because I am running it against an Access 97 database. It returns an error stating "Provider doesn't support this function". It is interesting that I can view table properties using the ADOX Catalog, just not the user objects.

    I've developed a security routine using Access 2000 as a front end and Access 97 as a back. As I recall, I had to use DAO because of the lower version of the back end. In an ADO book I looked at by Microsoft Press, they made a comment in there that if you put "Jet OLEDB:Engine Type=4" in the connect string using the Jet 4.0 Provider, it will tell the engine to open a Access 97 Database. I tried that and still got the Provider doesn't support this . . error.

    I tried using the DAO commands from my other Access 2000 application, but in VB they are erroring. I'm wondering if there is another step I need to take in actually opening the database in the workspace perhaps (I'm not very familiar with the workspace object yet).

    At the end of the On Load event below, I pasted in some code I got from an article in the MSDN help file that prints all users and all groups in the debug window. These don't error, but they don't return any records.

    Code is as follows (the commented connection strings are other strings I've tried):

    Private Sub Form_Load()



    'sConnString = "Provider=MSDASQL.1;DBQ=CatabaseOncology97.mdb;Driver={Microsoft Access Driver (*.mdb)};DriverId=281;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;PWD=viking ;SystemDB=CATABASEROADMAP.MDW;UID=mshea;"

    'sConnString = "Provider=Microsoft.Jet.OLEDB.3.51;Persist " & _
    ' "Security Info=False;User ID=RMUser;Data Source=C:" & _
    ' "DatabaseOncology97.mdb;Mode=Share Deny None" & _
    ' ";Extended Properties=';COUNTRY=0;CP=1252;LANGID=" & _
    ' "0x0409';Jet OLEDB:System database=c:database" & _
    ' "roadmap.mdw;Jet OLEDB:Registry Path='';Jet OLEDB:" & _
    ' "Database Password='';Jet OLEDB:Global Partial " & _
    ' "Bulk Ops=2;Password=user"

    sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist " & _
    "Security Info=False;User ID='" & sUserName & "';Data Source=C:" & _
    "DatabaseOncology97.mdb;Mode=Share Deny None" & _
    ";Extended Properties=';COUNTRY=0;CP=1252;LANGID=" & _
    "0x0409';Jet OLEDB:System database='" & sWIF & "';Jet OLEDB:Registry Path='';Jet OLEDB:" & _
    "Database Password='';Jet OLEDB:Engine Type=4;Jet OLEDB:Global Partial " & _
    "Bulk Ops=2;Password='" & sPassword & "'"

    'sConnString = "Provider=Microsoft.Jet.OLEDB.4.0;Persist " & _
    ' "Security Info=False;User ID='" & sUserName & "';Data Source=C:" & _
    ' "DatabaseOncology97.mdb;Mode=Share Deny None" & _
    ' ";Extended Properties=';COUNTRY=0;CP=1252;LANGID=" & _
    ' "0x0409';Jet OLEDB:System database='" & sWIF & "';Jet OLEDB:Registry Path='';Jet OLEDB:" & _
    ' "Database Password='';Jet OLEDB:Global Partial " & _
    ' "Bulk Ops=2;Password='" & sPassword & "'"

    'sConnString = "DSN=MS Access Database;DBQ=CatabaseOncology97." & _
    ' "mdb;DefaultDir=Catabase;DriverId=281;FIL=MS " & _
    ' "Access;MaxBufferSize=2048;PageTimeout=5;PWD=vikin g" & _
    ' ";UID=MShea;Jet OLEDB:Engine Type = 4;Jet OLEDB:System database='" & sWIF & "'"

    '
    'Dim Cat As New ADOX.Catalog
    '
    '
    'cn.Open sConnString

    '
    'Cat.ActiveConnection = cn
    '
    ''Cat.Create sConnString
    '
    '
    'Cat.Groups.Refresh
    '
    '
    'DBEngine.SystemDB = "c:databaseroadmap.mdw"

    Dim db As Database

    Dim wrk As Workspace
    Dim usr As User
    Dim grp As Group

    Set wrk = DBEngine.Workspaces(0)

    ' Set db = wrk.OpenDatabase("c:databaseoncology97.mdb")

    Debug.Print "The Users collection has the following " & _
    wrk.Users.Count & " members:"
    For Each usr In wrk.Users
    Debug.Print usr.Name
    Next usr


    Debug.Print "The Groups collection has the following " & _
    wrk.Groups.Count & " members:"
    For Each grp In wrk.Groups
    Debug.Print grp.Name
    Next grp


    End Sub

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Accessing Group Collection in Access from (Access 97/VB6)

    You can't mix the two object models. If you use DAO, you have to use DAO connections and DAO data controls, etc. If you use ADO, same thing. Which version of ADO are we talking about? The "Provider doesn't support ..." error can also be caused by not using exactly the right syntax in referring to the property in question. And ADO is designed to facilitate data access, not to handle user interface issues.

    One problem you run into with ADO and an MDB (97 *or* 2000) is that it can't see the querydefs collection because there isn't any such thing in ADO. However, there are Views and Stored Procedures collections.

    What are you trying to print the users and groups from. the mdw file or the mdb file?
    Charlotte

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing Group Collection in Access from (Access 97/VB6)

    Hi Charlotte,

    I am using the ActiveX Data Objects 2.5, and ActiveX Ext. 2.5 for DLL and Security.
    In DAO I am using version 2.6.

    Yes, I figured I couldn't mix them, and agree with you that the ADOX catelog commands are largely not being understood by the 97 version of Access. But I figured that the DAO code at the end of my example would work, or should work.

    Regarding your last question, this is a little embarassing, but I don't know if it is the MDB or MDW file I'm pulling from. When I did this in Access, everything was being run in the existing native database and I never had to specify a workgroup information file. I guess I always figured it was a combination of both, but it was never an issue.

    The connect string in the example I posted is a module level variable to use in ADO objects later in the program, not related to the DAO examples at the bottom.

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing Group Collection in Access from (Access 97/VB6)

    Just as an afterthought looking at the code I posted, this is done in the first step, the On Load event. How would it know what database it is referring to? I think the Set wrk = DBEngine.Workspaces(0) sets the workspace to the existing database object, but I'll bet it just creates a database if there isn't one currently occupying that space. I feel like I am missing a relatively basic step here in telling it what database I want to look in, or rather, correctly telling it what database.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Accessing Group Collection in Access from (Access 97/VB6)

    No, all that does is point to the current workspace, i.e., the one you're operating in. It does nothing about opening a database object or even referring to one. If you want to refer to a database object, you have to go down to DBEngine.Workspaces(0).Databases(n), where n is the index of the database in question. The shorthand for referring to the current database is DBEngine(0)(0).
    Charlotte

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing Group Collection in Access from (Access 97/VB6)

    Charlotte,

    I figured out how to have it understand what database I was referring to. I pasted the following code into each DAO function I use to access the users and groups:

    'Set the location of the system database
    DBEngine.SystemDB = _
    "Catabaseroadmap.mdw"

    'Create a new workspace object

    Set wrkTmp = DBEngine.CreateWorkspace("New", _
    sUserName, _
    sPassword)

  7. #7
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Accessing Group Collection in Access from (Access 97/VB6)

    That isn't creating a reference to the database, it's telling the application where to find the security information to use on the database you connect to.
    Charlotte

  8. #8
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Accessing Group Collection in Access from (Access 97/VB6)

    You could distribute the Access 2000 runtime with your VB application, unless the users will also need interactive access to the database...

  9. #9
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing Group Collection in Access from (Access 97/VB6)

    I wish I understood why it works better, but the line below the command telling it where the workgroup information file is does sign into the database using DAO. Since I put that in I haven't had a problem.

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing Group Collection in Access from (Access 97/VB6)

    Good suggestion, and I had thought of that. We would have to leave Access 97 on the user's machine, and though Access 2K can co-exist with 97, it has some quirks that would aggravate the end user (and would likely increase support calls to the IS Department). We are ordering the Developers edition of Access 2000 in case we ever want to implement another project that has a smaller scope. Interesting to note that they have discontinued the developers edition with the XP edition.

  11. #11
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Accessing Group Collection in Access from (Access 97/VB6)

    Well you've already created a connection to the database itself either through a datacontrol or a designer, right? In that case, all you need is the security information and you're getting that from the mdw when you create a connection to it.
    Charlotte

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Accessing Group Collection in Access from (Access 97/VB6)

    It hasn't been released yet, but I'm not sure that means that they've discontinued it. The impression I got at the launch was thay they just hadn't rolled it out yet.
    Charlotte

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Serbia and Montenegro (Yugoslavia)
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Accessing Group Collection in Access from (Access 97/VB6)

    You are probably right, Charlotte. When my work tried ordering it for me from our vendor they told us it was discontinued. I think it more likely that they were just making that assumption.

    Have you used XP, and if so, do you like it?

  14. #14
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Accessing Group Collection in Access from (Access 97/VB6)

    Briefly until it messed up my Access 2000 databases. Then I removed it pending their forthcoming hot patch. It is only marginally different from Access 2000 in the default 2000 database format. I didn't get the chance to play with the XP format at all.
    Charlotte

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •