How to Refresh/Requery mSysObjects in Combobox

[sxschech]

I set up several combo boxes to display Tables from msysobjects. The record source is using a WHERE clause to display certain tables both Linked and Local. Occasionally, when clicking the dropdown arrow, the box only displays local tables. This situation seems not to matter whether tables have been newly linked or unlinked. despite having both a me.requery and a me.refresh in the code and/or even clicking the Refresh All button on the ribbon menu, the form does not seem to update the combo box to display both linked and local tables.

In further testing, it seems that it is not a form issue because I took the SQL statement I used as the record source and created a new query with the sql string. The query behaved the same as the combo box, only listing out local tables.

My current work around is to close the database and reopen, then all is well. Hopefully there is another way to get the linked tables listed without this drastic step.

Don't think it's necessary, but here is the sql statement. Type 6 is linked.

SELECT msysobjects.name 
FROM msysobjects  
WHERE msysobjects.[flags] = 0 
AND msysobjects.[type] in (1, 6) 
AND Right([name],7)<>'_SOURCE' 
AND Right([name],4)<>'_OUT' 
AND msysobjects.name not in ('tblImport','tblImportFormats','tblUniversal') OR msysobjects.database=gblprojectname() 
ORDER BY msysobjects.name, Right([name],7);

 

[spikepl]

So why not start from the beginning, remove all restrictions and list all the tables ?

Besides you do not need to muck about in mSysObjects either, which is not recommended practice anyway, but just use the normal tables collection.

 

[MarkK]

Changing this . . .

AND msysobjects.[type] in (1, 6)

. . . to this . . .

AND ([type] = 1 OR [type] = 6)

. . . solves the problem in my Db.

 

[sxschech]

Hi MarkK, I'll try your suggestion and let you know. May or may not be a while as I am not sure what circumstance causes the code to ignore linked tables to try and replicate the problem to see how the suggestion performs.

spikepl: Until I saw MarkK's post, I started looking into this per your suggestion for using the tables collection, not sure how to convert to a query/populate the combo box. I have several versions of the query with different where clauses and some also use a left outer join to another table. I used this post as a starting point, it gave me the list of tables, but as stated above hadn't gotten to figure out how to filter and populate the combo box. http://www.techrepublic.com/article/how-to-create-a-list-of-tables-or-queries-from-access/5047664/

 

[sxschech]

Looks like MarkK's suggestion did not do the trick for me. I am only seeing local tables. Back to the drawing board.

 

[sxschech]

MarkK, maybe your code is working, just not for me and I think I found a part of the problem.

My sql statement is using a function to get the name of a specific external database. Reason being is for external tables, only want to display the tables in that file along with local tables, rather than all linked tables and local tables. Since the user can change which external to connect to at anytime, I don't want to hard code the file name. I set up a global variable, but perhaps something is causing it to be cleared out and that could be why the combo box is not showing any linked tables. Originally I had relied on the value of the text box of the form saved to a table to obtain the external file name. Sometimes the record got deleted due to an interruption in the code (user changed mind about linking to another database file, or picked the wrong db). Since wanted it available throughout the program even when the form has been closed or record deleted, I thought that by putting into global variable it would keep. Maybe I set it up wrong.

 

[CJ_London]

one other thing to correct - name, like type, is a reserved word so you should also put that in square brackets as well. It may not matter in this case, but better safe than sorry. I've never understood why MS did not follow their own advice for this and other system tables

 

[vbaInet]

So why not start from the beginning, remove all restrictions and list all the tables ?

So have you done this yet? Begin with removing this entire line:

AND msysobjects.name not in ('tblImport','tblImportFormats','tblUniversal') OR msysobjects.database=gblprojectname()

At what stage do you call/use this query? When the db is loading or at a much later stage?

You can also try calling this before you run the query:

Application.RefreshDatabaseWindow

 

[sxschech]

Hi all, as mentioned in post#6, I think the problem of not picking up linked tables is related to the function that derives the linked filename and path and my theory is that perhaps the data has dropped out of the variable. I have modified my code for the function to evaluate if the length > 0 rather than just blindly using the variable and will see if that takes care of the issue. So far is working, but since haven't determined what is triggering the loss of the value in the variable, will need to wait until the msgbox shows up.

Public Function gblProjectName() As String
    Debug.Print globalstProjectName & " " & Now()
    If IsOpen("frmUtilities", 2) And Len(Forms!frmUtilities.txtProject) > 0 Then
        gblProjectName = Forms!frmUtilities.txtProject
    ElseIf Len(globalstProjectName) > 0 Then
        gblProjectName = globalstProjectName
    Else
        MsgBox "Missing Project File Location information.  Open the utilities form, if not already open, " & _
               "check if a project file is displayed.  If project file is not displayed, please choose it " & _
               "using the choose project button.", vbOKOnly, "Project File Location Issue"
    End If
End Function

Thanks all for your suggestions

 

[MarkK]

I took another look at this, and now I'm noticing that my linked tables don't have . . . .

flags = 0

. . . so this where clause . . .

WHERE msysobjects.[flags] = 0 
AND msysobjects.[type] in (1, 6)

. . . filters out the linked tables, even though [type] IN (1, 6) would allow them. Try removing your flags = 0 criterion and see if your linked tables show up.