Requery dbEngine.workspace(0)

[CharlieT302]

Hi Everyone,

First, I apologize upfront. I originally posted this question within another post that covered different but related topics. It occurred to me that this forum may be more appropriate for this question. Not trying to be repetitive.

My question is this.
I have a form that I use to create user accounts in a database saved in a 2003 file format. I can add and remove users as well as assign and remove groups. Everything works fine with one exception. I have two list boxes. One shows all current user accounts and the other shows all current groups. As a user or group is rerated, I need both of these list boxes to be updated and reflect the new additions (or removals).

However, the only way that I can get this to work is to close the entire database and reopen it. My thought is that the dbengine.workspace needs to be requeried.

Is this the case? If so, how do you do that?

 

[Darrylles]

Can't you use:

lstMyListName.Requery

following whichever activity updates the underlying tables?

ATB,

D

 

[CharlieT302]

Not really. This RowSource for both list boxes are generated new each time the form is loaded. I have code on the OnLoad property that does this. See below.

Set WrkSpc = DBEngine.Workspaces(0)
Dim strAccountName As String
Dim grpAccountName As String
Dim usr As User
Dim grp As Group

List_Users: ' displays all user accounts
For Each usr In WrkSpc.Users
If usr.Name <> "Creator" And usr.Name <> "Engine" And usr.Name <> "Admin" _
And usr.Name <> "sysadm" And usr.Name <> "" Then
strAccountName = strAccountName & ";" & usr.Name

Me.User_List.RowSourceType = "Value List"
Me.User_List.RowSource = strAccountName
End If
Next

List_Groups: ' lists all group accounts
For Each grp In WrkSpc.Groups
If grp.Name <> "Admins" And grp.Name <> "Users" And grp.Name <> "AdmGroup" Then
grpAccountName = grpAccountName & ";" & grp.Name

Me.Group_List.RowSourceType = "Value List"
Group_List.RowSource = grpAccountName
End If
Next

I tried to attach this code to the code defined on my Create User button, in order to regenerate the list. However it appears as though the dbengine.workspace is loaded into memory once the database is opened and that regenerating the initial code is irrelevant since it is merely generating the RowSource from what is already in a memory cache. This is why I am thinking the workspace needs to be requeried. I could be wrong. Note: the User Accounts feature that come with Microsoft can read the changes immediately, so there must be a way for me to do the same.

 

[kjv1611]

FYI to anyone finding this here, this was the original thread:
thread702-1781489

CharlieT302,

I suggest you try to keep the questions on the same topic in the same thread. I realize this is a new topic, but you did post the question in the original thread and this new thread. Just try to keep the discussion in one place or the other. Otherwise, you could end up having 2 diff people offering different advice in diff threads.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[CharlieT302]

I realize that, which is why I opened my post with a pre-apology. This question would have been lost in thread of the other post, which is why I started a more direct post hers. Also, this forum, since it is geared towards VBA, is probably more directly related to my question.

While I appreciate the reasons behind your sentiment, honestly, this type of comment is not helpful. Especially when this was already acknowledged and apologized for upfront.

People post to share and receive needed help on projects. I have an issue that I need to solve quickly and am having a difficult time finding answers. I am trying to ensure that I am being as direct as I can and placing my posts into the correct forums. No breach of protocol was intended.

 

[CharlieT302]

Update:
I came across two topics on the net that "seem" like they want to point me in the right direction. One concerned the updating of the
dbengine. However, the code to do so is having no effect or I am just unsure of where in my code to use it.

DBEngine(0).Databases.Refresh

The other was using CurrentDB in lieu of DBEngine.Workspaces(0). However, I am unsure of how (or if) I can use it to pull user and group list. At this point, I will take any solution.

My code that initially populates the User list box and Group list box is posted above. I have a Create User button that creates new accounts (see below). Can either of the two suggestions above be used to ensure that a newly created User account is immediately displayed in the User list?

Create User Code:

Create_Acct:
Set WrkSpc = DBEngine.Workspaces(0)

Dim strAccountName As String
Dim usr As User
Dim usrNew As String
Dim grpdef As String
Dim grpNew As String
Dim passnew As String
Dim PidNew As String
Dim str As String

usrNew = Me.NewUser
grpdef = "Users"
'grpNew = Me.NewGroup
passnew = Me.NewPass
PidNew = usrNew & "23"

With CurrentProject.Connection
    str = "CREATE USER " & usrNew & " " & passnew & " " & PidNew & ";"
    .Execute str
    
    str = "ADD USER " & usrNew & " TO " & grpdef & ";"
    .Execute str
    
End With
Me.NewUser = ""
Me.NewPass = ""

 

[kjv1611]

CharlieT302,

I never said anything about breaking protocol or anything. Having an actual reference between threads that are linked in some way is a good way to keep duplication to a minimum if not eliminate it.

As far as your upfront apology negating the need for a link... What? Really?

You came here wanting help. My note was a way to link the 2 SO PEOPLE CAN BETTER HELP YOU... AND so people later can get correct help. How many times have you searched for an answer, found a related discussion only to see the question not fully answered? In those cases, I wonder how often questions ARE answered just in other (non-linked) locations.

The way this site is built, any time there is a new post, it goes to the bottom of the specific discussion thread (default sorting), and it always shows up at the TOP of the forum discussion list (default sorting). So it's not likely to be lost in the shuffle - at least with folks who know what's going on.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[Darrylles]

I have never use 'workspaces' in Access, but, should they not still conform to 'scope' rules?
After quickly reading up on them...
I'm reading that 'workspaces' should not ever be 'closed'.
I'm assuming that you are declaring the 'WrkSpc' variable in a Module somewhere (so that it can be referenced by any object within the application).

The 'set' command:

[indent][/indent]Set WrkSpc = DBEngine.Workspaces(0)

...I am thinking should only be set once per session.
Is it not true that WrkSpc, is now pointing to a 'dynamic' workspace 'session' / a pointer to an object?
Therefore, you have no need to 'refresh' / 'requery' it?

So:

Create a Module (call it modWorkSpace).
Within it, use this code:

[indent][/indent]Dim Wrkspc as Object
[indent][/indent]Set Wrkspc = DBEngine.Workspaces(0)

This will only ever be 'called' once, and it will be called 'automatically' (no need for you to explicitly call it at all).

Define a sub within your form (as above) - (you had some fundamental errors in YOUR list population code):

Public Sub Refresh_Listboxes
[indent]Dim strUser As String[/indent]
[indent]Dim strUserList as string[/indent] 
[indent]Dim strGroup As String[/indent]
[indent]Dim strGroupList as string[/indent]
[indent]Dim Usr As User[/indent]
[indent]Dim Grp As Group[/indent]

[indent]'(Don't use 'labels')...[/indent]
[indent][s]List_Users: ' displays all user accounts[/s][/indent]
[indent]For Each Usr In WrkSpc.Users[/indent]
[indent][indent]strUser = Usr.Name[/indent][/indent]

[indent][indent]If (strUser <> "Creator" And _[/indent][/indent]
[indent][indent][indent]strUser <> "Engine" And _ [/indent][/indent][/indent]
[indent][indent][indent]strUser <> "Admin"And  _[/indent][/indent][/indent]
[indent][indent][indent]strUser <> "sysadm" And _[/indent][/indent][/indent]
[indent][indent][indent]strUser <> "") Then[/indent][/indent][/indent]

[indent][indent][indent]'Forming the delimited string list is different if NOT first item...[/indent][/indent][/indent]
[indent][indent][indent]If (Len(strUserList > 0)) Then[/indent][/indent][/indent]
[indent][indent][indent][indent]strUser = ";" & strUser[/indent][/indent][/indent][/indent]
[indent][indent][indent]EndIf[/indent][/indent][/indent]
[indent][indent][indent]strUserList = strUserList & strUser[/indent][/indent][/indent]
[indent][indent]End If[/indent][/indent]
[indent]Next[/indent]

[indent]'Populate the listbox once (NOT every For loop interation)...[/indent]	
[indent]User_List.RowSourceType = "Value List"[/indent]
[indent]User_List.RowSource = strAccountName[/indent]

[indent]'(Don't use 'labels')...[/indent]
[indent][s]List_Groups: ' lists all group accounts[/s][/indent]
[indent]For Each Grp In WrkSpc.Groups[/indent]
[indent][indent]strGroup = Grp.Name[/indent][/indent]

[indent][indent]If (strGroup <> "Admins" And _[/indent][/indent]
[indent][indent][indent]strGroup <> "Users" And _[/indent][/indent][/indent]
[indent][indent][indent]strGroup <> "AdmGroup") Then[/indent][/indent][/indent]

[indent][indent][indent]'Forming the delimited string list is different if NOT first item...[/indent][/indent][/indent]
[indent][indent][indent]If (Len(strGroupList > 0)) Then[/indent][/indent][/indent]
[indent][indent][indent][indent]strGroup = ";" & strGroup[/indent][/indent][/indent][/indent]
[indent][indent][indent]EndIf[/indent][/indent][/indent]
[indent][indent][indent]strGroupList = strGroupList & strGroup[/indent][/indent][/indent]	
[indent][indent]End If[/indent][/indent]	
[indent]Next[/indent]	

[indent]'Populate the listbox once (NOT every For loop interation)...[/indent]		
[indent]Group_List.RowSourceType = "Value List"[/indent]	
[indent]Group_List.RowSource = strGroupList[/indent]
End Sub

Call this Sub every time you want the listboxes re-populated...

[indent]Call Refresh_Listboxes[/indent]

Like I said, I haven't used Workspaces, however, how they are managed should be 'standard' stuff.
Try this, let us know if it made a difference.

ATB,

D

 

[Darrylles]

Apologies...

Please replace:

User_List.RowSource = strAccountName

with:

User_List.RowSource = strUserList

 

[CharlieT302]

Darrylles

Thank you very much for the reply. I am under a timeline, so I truly appreciate your help. I will try this right away.

I just want to clear up something.
If I am understanding you correctly, I should create a public module to replace my initial code to populate list boxes. I could then call it in the OnLoad event of my form (or wherever else I need it). That's fine, but what I am not seeing is where the module: ModWorkSpace, is being used. You said there is no need to officially call it. How then is it being referenced? Keep in mind I am self-taught with programming, so I may be just missing some fundamentals here.

 

[CharlieT302]

Darrylles
Thank you for your suggestion. Unfortunately, I did not have much success. The module: modWorkSpace produced an error and would not work as constructed. So, to test the rest of the code, I eliminated modWorkspace. To compensate, I added: Set Wrkspc = DBEngine.Workspaces(0) to top of the module: Refresh_ListBoxes(). Everything else is the same. However, I also had an error with this function (see below).

modWorkSpace
Produced Error:
Compile Error: Invalid Outside Procedure

Refresh_ListBoxes()
This following line produces an error
If (Len(strUserList > 0)) Then

Error Produced:
Variable required - Can't assign to this expression (I rather expected this error , since I did not see where strUserList was being defined or how the code would know what to do with it.)

Unless I am missing something (and I may very well be), I do not see a fundamental difference between the original and the new code. It appears as we are just taking a different route to the same outcome but not addressing the issue. I can generate a list of accounts, and it is accurate. The issue is refreshing the list after a change is made.

To ask a previous question, is this a dbEngine vs. CurrentDB issue? I have read that CurrentDB is guaranteed accurate, but not dbEngine. Is dbEngine accurate only at the time it is open?

I really appreciate your input in this. I have been at this for days. This seems like it should be such a simple task.

Any thoughts?

 

[CharlieT302]

Still frustrated..!

Let's try this a different way. I actually have a few areas that will all necessitate the need to refresh the list of user accounts. The Form OnLoad event, Create Users, and Remove Users functions. It will be the same answer for all areas.

Here is sample code from the Create Users function. I need this function to update my user list once the new user is created. As far as I can tell, dbEngine is taking a snapshot in time, in that it is accurate as of the moment you open the database, but does maintain a dynamic link to the workgroup file.

Set Wrkspc = DBEngine.Workspaces(0)

Dim strAccountName As String
Dim usrNew As String
Dim grpdef As String
Dim passnew As String
Dim PidNew As String
Dim str As String

usrNew = Me.NewUser
grpdef = "Users"
passnew = Me.NewPass
PidNew = usrNew & "23"

With CurrentProject.Connection
    str = "CREATE USER " & usrNew & " " & passnew & " " & PidNew & ";"
    .Execute str
    str = "ADD USER " & usrNew & " TO " & grpdef & ";"
    .Execute str
End With

Me.NewPass = ""

I am seriously ready to throw in the towel on this one. There just does not seem to be any info on this anywhere. Well...maybe the rest of my weekend will be better!

 

[kjv1611]

I honestly haven't tried to read every bit of code here, but it leaves me wondering:
1. Why not use INSERT/DELETE/UPDATEqueries to make your updates and also use a SELECT query to feed data to the form?
2. Why use DBEngine.Workspaces(0) instead of something like:

Dim db as DAO.Database
Set db = CurrentDb

If this article is correct (and I didn't read all of it, but just skimmed a bit), it sounds like your best off using CurrentDb for what I'd call "current events":

https://www.experts-exchange.com/ar...kspaces-0-Databases-0-and-an-alternative.html

The problem:

CurrentDB() is an Access function and is processed by the UI layer, which returns a new reference to the current database. As a result, it's always up to date with current information (i.e. it would include a form that was just added) in its collections. Using dbEngine(0)(0) on the other hand is getting a pointer to the DAO (Data Access Object) reference maintained by JET. In using this reference you are bypassing the UI layer, which means that you won't know if something has been done in the UI unless you do a refresh of a collection. That refresh can be quite expensive in terms of performance.

I added emphasis on the 2 compared terms.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[CharlieT302]

kjv1611

Thank you for your interest. What you are suggesting is exactly what I am asking. Is CurrentDB a viable alternative to what I was doing? Keep in mind what it is I am trying to do. The whole point is that I have two list boxes that need updating: One displaying User accounts and the other showing Group accounts assigned to a user. I actually have a third which shows available Group accounts, but this one will never be updated.

My code updates the list boxes only once; when the database is opened. I need them to refresh as a change is made, in real time. If CurrentDB can do the job (or any other method), I need to know how to do it. As of now, I am at a complete loss.

 

[kjv1611]

Are the values stored in tables or something else? From my understanding, CurrentDb is what you want to use if you want to be able to get updates from the user interface, such as this scenario.

If the values are stored in tables, then I would get the queries first that do what I wanted to do, then have VBA either update the rowsources for the listboxes accordingly or if you need to loop through them for some reason, looping through a filtered query may save a bit of time. Just depends upon your underlying data.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[CharlieT302]

No. We have a program stored in an Access 2003 format. We have User Level Security installed. My list boxes are displaying User and Group accounts, which are stored in the Workgroup information file. The code that I have pulls that data nicely. When I add a user account or assign a group to a user, I need the list boxes to update in real time. They do not. They will only update if the database is closed and then reopened.

 

[kjv1611]

I asked:
Are the values stored in tables or something else?

You said:
No. We have a program stored in an Access 2003 format.

You do realize that Access databases have tables that store data, correct? At least that's by default. That's the only way to truly "store" data unless you've got it in static code or something, which doesn't make much sense for a manageable list of any kind. It is possible to store the data to some other linked database, such as SQL Server or Oracle or others. (Edit: One other possible source is if they are stored as values within an Access form field, such as a listbox. But that's a really bad idea if it's for changeable values.)

So we need to know:
1. Where is the data stored?
2. How is that data connected to the list boxes?

Then we can go from there.

If the list box is populated by a query of some sort or else code rather than a direct connection to the table, then the data is being pulled in as read-only. Some query types will allow you to still modify the underlying data, other types will not.

Can you tell us where the data is stored precisely and how it's pulled into your form listboxes?

With that information, I think surely we can get you to a working solution (I say we as in any of the folks reading this thread).

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

 

[CharlieT302]

kjv1611

I appreciate your taking an interest. I really do. However, my issue here is clear, even though the solution may not be. This is not a matter of simply linking to tables or populating list boxes with queries. I would not require assistance if it were. All of the information is coming from the Workgroup Information file (.mdw). All of the information there is stored in system tables, to which you cannot create a direct link.

The code I have is designed to pull the User Accounts and Group accounts established thru User Level Security and stored in that file. Microsoft no longer includes User Level security in the current versions. However, it does still support it if your file remains in the 2003 format. Many of us are (and must be) still dependent upon this and cannot change. This has been an elusive answer. I have been unable to find information anywhere on this; including the forums at Microsoft's website.

If anyone is familiar with User Level security and how to create a list box that can be updated without the need to close the database, I would be most appreciative. Thank you in advance.

 

[kjv1611]

Gotcha. OK, yes, a fun situation there. I have not (thankfully) had to bother with that to date. Sounds like it's not likely I ever will, since it's a deprecated feature of Access. Perhaps that's a good thing on Microsoft's part. Sorry, I know that gives you no help for now.

Perhaps as a long-term solution, you (your company) should be working towards a different tool altogether if at all possible. I know that sometimes you may be stuck with older technology due to another party and/or another device that requires it. I deal with that fun constantly with my current employer due to the equipment used.

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57