How to simulate clicking View then Refresh (F5) in Access in VBA

add this line to your codes


application.RefreshDatabaseWindow

I tried this and am still getting the same behavior.....doesn't work the first time I ty to open a query but works every other time.  I even put this line of code as the very last line to be executed and still didn' seem to make a difference?

post the codes that you are running.

Function relink_working_folder_f()

strStatus = "Please wait while Multi_Work.mdb is updating all linked tables for the files in this folder..."
varStatus = SysCmd(acSysCmdSetStatus, strStatus)

'Turn Screen Updating off
Application.Echo False
DoCmd.Hourglass True

define_working_files_to_link
'Load in current working directory to begin processing multiple output
pathvar = CurrentProject.FullName
filenamevar = CurrentProject.Name
curdirvar = Mid(pathvar, 1, Len(pathvar) - Len(filenamevar) - 1)

Dim detailfilename As String
Let detailfilename = "Multi_Work_Detail.mdb"

Dim comparetblname As String
Let comparetblname = "Compare Databases"

update_log_file "Beginning linking of compare detail table."

'Load in current database as object
Dim obj As AccessObject, dbs As Object
Set dbs = Application.CurrentData
Dim dbSel As dao.Database
Set dbSel = CurrentDb()

'Remove any tables that match the current table prefix
update_log_file "Beginning removal of old compare detail table."
For Each obj In dbs.AllTables
    If obj.Name = comparetblname Then
        update_log_file "Removed table '" & obj.Name & "'."
        DoCmd.DeleteObject acTable, obj.Name
    End If
Next
update_log_file "Completed removal of old compare detail table."

Set obj = Nothing
Set dbs = Nothing

'Now open dbname database and link each table in it to this database
Dim db As dao.Database
Dim tds As dao.TableDefs
Dim tdf As dao.TableDef

Set db = OpenDatabase(curdirvar & "\" & detailfilename)
Set tds = db.TableDefs

update_log_file "Beginning linking of new compare detail table."
For Each tdf In tds
    'Only interested in non-system tables, which aren't linked
    If tdf.Name = comparetblname Then
        'Create a new table def, named as per the target table
        Set tdflink = dbSel.CreateTableDef(tdf.Name)
        'Identify the target table for the link
        tdflink.SourceTableName = tdf.Name
        'Identify its path
        tdflink.Connect = ";DATABASE=" & curdirvar & "\" & detailfilename
        'And add it to this database's tables
        dbSel.TableDefs.Append tdflink
        tdflink.RefreshLink
    End If
Next

update_log_file "Completed linking of new compare detail table."

'Close out variables
Set dbSel = Nothing

Set tdf = Nothing
Set tdflink = Nothing
Set tds = Nothing
db.Close
Set db = Nothing

update_log_file "Completed linking of compare detail table."
'Turn Screen Updating back on
DoCmd.Hourglass False
Application.Echo True

Application.RefreshDatabaseWindow

strStatus = "Ready"
varStatus = SysCmd(acSysCmdSetStatus, strStatus)

End Function

modify your codes

For Each obj In dbs.AllTables
    If obj.Name = comparetblname Then
        update_log_file "Removed table '" & obj.Name & "'."
        DoCmd.DeleteObject acTable, obj.Name
    End If
Next

Application.RefreshDatabaseWindow  ' <<< Add here


post back the result

it still is acting the same way....should I be activating a certain window first or something?  

what does this line do

    update_log_file

>> Then when I click OK and try again it works because it must trigger a refresh in doing that. <<

Do you have the Name AutoCorrect option enabled?  If so, then turn it OFF!  Also, apparently you do not have Option Explicit set at the top of your module ... I would suggest that you add that in order to ensure proper variable name usage.

Looking at your code now ...

>> update_log_file <<

Simply writes out a line to a log telling what the code is doing as it loops though objects...

Function update_log_file(logmessagevar)

'Load in current working directory
pathvar = CurrentProject.FullName
filenamevar = CurrentProject.Name
curdirvar = Mid(pathvar, 1, Len(pathvar) - Len(filenamevar) - 1)

'Code to write to log file
Dim iFileNumber As Long
Dim strFileName As String
Dim logfilenamevar
Dim sepvar
'Load in timestamp information
curtime = Now()

'Determine separator value
If Len(curtime) = 19 Then
    sepvar = "~~~~"
End If
If Len(curtime) = 20 Then
    sepvar = "~~~"
End If
If Len(curtime) = 21 Then
    sepvar = "~~"
End If
If Len(curtime) = 22 Then
    sepvar = "~"
End If

'Text to write to log file
strdata = curtime & " " & sepvar & " " & logmessagevar

'Update first log file
'Load in log file names
logfilenamevar = "Woodmac_CMS_Process.log"
iFileNumber = FreeFile()
strFileName = curdirvar & "\" & logfilenamevar
Open strFileName For Append Shared As #iFileNumber
Print #iFileNumber, strdata
Close #iFileNumber

End Function

You are doing a lot of work in your code to delete and recreate one linked table object.  You can simply modify the .Connect property of the existing table.

Function relink_working_folder_f()
   
    Dim strDetailFileName As String
    Dim strCompareTblName As String
    Dim strConnection As String

    strDetailFileName = "Multi_Work_Detail.mdb"
    strCompareTblName = "Compare Databases"
    strConnection = ";DATABASE=" & CurrentProject.Path & "\" & strDetailFileName    
   
    With CurrentDb
        With .TableDefs(strCompareTblName)
            .Connect = strConnection
            .RefreshLink
        End With
    End With
End Sub

By modifying the .Connect property, there is no need to delete and recreate the object.  By keeping your objects and just modifying the properties, you reduce your risk of corruption and bloating.

Sorry that was supposed to be End Function (the code was written "on the fly") -- BTW, any reason you are not using a Sub (as appossed to a Function)?

Function relink_working_folder_f()
   
    Dim strDetailFileName As String
    Dim strCompareTblName As String
    Dim strConnection As String

    strDetailFileName = "Multi_Work_Detail.mdb"
    strCompareTblName = "Compare Databases"
    strConnection = ";DATABASE=" & CurrentProject.Path & "\" & strDetailFileName    
   
    With CurrentDb
        With .TableDefs(strCompareTblName)
            .Connect = strConnection
            .RefreshLink
        End With
    End With

End Function

Thanks for your comments on how I wrote that piece of code.  That's not the issue I am having though.  It works fine as-is and was originally written for a more dynamic process of removing all linked tables and adding those of a completely different database with different tables, etc.  They are always different.  The problem is refreshing the database so it recognizes tables I have added and deleted via code.

try also adding the line after these codes

For Each tdf In tds
    'Only interested in non-system tables, which aren't linked
    If tdf.Name = comparetblname Then
        'Create a new table def, named as per the target table
        Set tdflink = dbSel.CreateTableDef(tdf.Name)
        'Identify the target table for the link
        tdflink.SourceTableName = tdf.Name
        'Identify its path
        tdflink.Connect = ";DATABASE=" & curdirvar & "\" & detailfilename
        'And add it to this database's tables
        dbSel.TableDefs.Append tdflink
        tdflink.RefreshLink
    End If

Application.RefreshDatabaseWindow  ' <<< Add here

Next


post back the result

I think it must be a timing issue.  If I do none of these changes and simply wait about 5 seconds, it works the first time.  If I try to open it immediately i get the same old error.  No matter what I try, it doesn't seem to resolve that issue.

where particularly is the error appearing?

>> It works fine as-is and was originally written for a more dynamic process of removing all linked tables and adding those of a completely different database with different tables, etc. <<

I was just going by the code you posted ... :)

Take a look at the following code.  It is a modification of yours.
Things to note:
- The use of CurrentProject.Path
- The "Step -1" looping to delete the linked tables in the dbSel.TableDefs collection.
- The dbSel.TableDefs.Refresh call in order to refresh the TableDefs collection of dbSel.
- The placement of the RefreshDatabaseWindow in the loop that adds the tabledefs to the current database.  Plus the one "extra" at the end (just as you had it).
- No need to call .RefreshLink when creating a linked table object.  The .Append takes care of that.

<< Biggest note of all! ... this is AIR CODE as I did not test this code in an actual database :-s >>

Take a look and see if it solves the issue you are having.  Also, PLEASE make sure you have the Name AutoCorrect option disabled .. otherwise the dependancy mapper may get confused with adding and deleting objects, especially if the names of the linked tables are the same.

Function relink_working_folder_f()
   
    Dim strStatus As String
    strStatus = "Please wait while Multi_Work.mdb is updating all linked tables for the files in this folder..."
   
    Dim varStatus As Variant
    varStatus = SysCmd(acSysCmdSetStatus, strStatus)

    'Turn Screen Updating off
    Application.Echo False
    DoCmd.Hourglass True

    define_working_files_to_link
   
    'Load in current working directory to begin processing multiple output
    Dim curdirvar As String
    curdirvar = CurrentProject.Path

    Dim detailfilename As String
    Let detailfilename = "Multi_Work_Detail.mdb"

    Dim comparetblname As String
    Let comparetblname = "Compare Databases"

    update_log_file "Beginning linking of compare detail table."

    'Load in current database as object
    Dim dbSel As DAO.Database
    Set dbSel = CurrentDb()

    'Remove any tables that match the current table prefix
    'notice the reverse looping because when you loop a collection
    'for removal, its safest to remove based on the highest index.
    update_log_file "Beginning removal of old compare detail table."
    Dim x As Long
    For x = dbSel.TableDefs.Count - 1 To 0 Step -1
        If dbSel.TableDefs(x).Name = comparetblname Then
            update_log_file "Removed table '" & obj.Name & "'."
            DoCmd.DeleteObject acTable, obj.Name 'Automatically refreshes dbwindow
        End If
    Next
    update_log_file "Completed removal of old compare detail table."

    'Refresh the TableDefs collection of the db object variable that points to
    'the current db.
    dbSel.TableDefs.Refresh

    'Now open dbname database and link each table in it to this database
    Dim db As DAO.Database
    Set db = OpenDatabase(curdirvar & "\" & detailfilename)
   
    update_log_file "Beginning linking of new compare detail table."
   
    Dim tdf As DAO.TableDef
    For Each tdf In db.TableDefs
   
        'Only interested in non-system tables, which aren't linked
        If tdf.Name = comparetblname Then
           
            'Create a new table def, named as per the target table
            Dim tdflink As DAO.TableDef
            Set tdflink = dbSel.CreateTableDef(tdf.Name)
           
            'Identify the target table for the link
            tdflink.SourceTableName = tdf.Name
           
            'Identify its path
            tdflink.Connect = ";DATABASE=" & db.Name
           
            'And add it to this database's tables
            dbSel.TableDefs.Append tdflink
            Set tdflink = Nothing
           
            'Refresh the dbwindow/navpan
            RefreshDatabaseWindow
           
        End If
       
       
    Next
   
    db.Close
    Set db = Nothing
   
    update_log_file "Completed linking of new compare detail table."

    'Turn Screen Updating back on
    DoCmd.Hourglass False
    Application.Echo True
   
    'A refresh to just be sure
    RefreshDatabaseWindow

    strStatus = "Ready"
    varStatus = SysCmd(acSysCmdSetStatus, strStatus)

End Function

>> If I do none of these changes and simply wait about 5 seconds, it works the first time.  <<

This is why I made the comment about Name AutoCorrect ... I never have the option enabled, but the delay may be due to the dependancy mapper figuring out what to do with all the new linked table objects.

Yes I turned that off and still have the same error.  It is only when I try to "immediately" open a query that was dependent on the underlying tables that have been erased and brought back.  If I simply wait 5 or 6 seconds and then open it, no error.  I'm sure there is something simple I am missing here.  I'll take a hard look at the revidescode you gave me here and see if it still accomplishes my goal and hopefully fixes the issue with the timing error.

Cool! ... Cap and I are looking forward to the results.

---

Also, one more thing in trying to debug the delay in tabledefs being "available" for the Query, do you open the Query object from the database window? or in code?

I open it manually at the moment, but that is the intent for resolving this issue.  The whole idea behind this code is it will link multiple databses of like structure all to one file so that the like tables can all be joined with unions in SQL code and the resultant queries will then be created automatically and is completely flexible to the number of like databases.  We use this in comparing multiple forecasts at once that are all in the exact same format, just different numbers.  Just throw all of the output mdb files into one directory and open this file and all data is linked and available to build comparison queries off of.  Just a little background info for you on what I am trying to do here.

Perhaps, a delay before running the codes will help resolve the issue.

https://www.experts-exchange.com/questions/24176531/How-do-I-use-the-Sleep-function-in-this-script.html?anchorAnswerId=23735472#a23735472

Very intuitive and very useful for my needs here.  I may go down this route as it will speed up the whole"linking" process that buils the queries....since there won't even be any more linking involved, just SQL syntax....thanks!

One of the benfits of linking tables is that when you run queries off of linked tables you get no Access CREEP in the current file or the source file....is that still the case when using queries that link to other files like you demonstrated here?

I have never had an issue -- if your are talking about bloating of the database files.   If you are not putting any data into the source files then there should be virtually no growth in there size.  The client file (the one running the SQL's) might be more prone to growth -- it just depends on what you are doing with your objects and local data (if any exists).  With your original thought of deleting and recreating the Linked Table Objects, your client db (aka: application database) would be prone to bloating because of the constant creation of objects.  The way database files work, in short, is that they will never shrink barring any "external" process.  A database will claim disk space it needs to store the data or objects it needs to store.  Deleting data or objects do NOT free up the database file claim on the hard drive.  Thus the need for the Compact and Repair operation (in SQL server, the same phenomenon exists).  The C&R is sorta like a defrag of your database -- it reorganizes all the data in order to release the unused space back to the hard drive.

... Continuing ...

The reason my initial suggestion to modify the .Connect property of the linked table object was to reduce the bloating of the application database file.  So ... if you use the Query Object route, I would suggest creating the Query Objects you need, then just modify the SQL property of the QueryDef object.  Or, as I stated earlier, just use SQL statements in source type properties (RecordSource, RowSource) or the raw SQL statement to build recordsets in VBA.

Yep, I have decided to go the route of simply creating the SQL statements via VBA and do away the linking and relinking.  Much cleaner and faster.  Thank you s much for the redirection here.  Learn something new everday!

You're welcome!

Its definately good to learn more stuff -- that is why I like EE and other sites like it, its amazing the solutions that folks can come up with!  Always good to learn new stuff.

Good luck on your project!