Reassign the table to which a TableDef object refers

[JimmyMack]

Hello all. Thank you for reading. I am running some code that creates new tables in my database (based on records in a table: one new table for each record), and then subsequently I am trying to iterate through the newly created tables to add new fields (once again based on records in another table: one new field for each record) and perform some calculations. The issue I'm having is that once I set my TableDef object variable equal to db.TableDefs("TableInDatabase"), I can not RE-set it to a different table. Is it possible to clear the definition and reassign to a new table? The line in red is the one not working how I'd like it to...no matter what db.TableDefs("ta_" & well) refers to, tblTAWell persists in pointing to the very first value it receives.

    Public Sub WellTables()    Dim strWList As String, strPList As String, strSQL As String, well As String, param As String
    Dim db As DAO.Database, rsWList As DAO.Recordset, rsPList As DAO.Recordset, rsWell As DAO.Recordset
    Dim tblrecord As DAO.Recordset
    Dim rsWFld As DAO.Field, rsPFld As DAO.Field, newFld As DAO.Field
    Dim tblTAWell As DAO.TableDef
    Dim i As Integer, fcount As Integer
    
    Set db = CurrentDb
    strWList = "SELECT DISTINCT * FROM tblTrendingAnalysisWells"
    strPList = "SELECT DISTINCT tblTrendingAnalysisParameters.Param FROM tblTrendingAnalysisParameters;"
    Set rsWList = db.OpenRecordset(strWList)
    Set rsPList = db.OpenRecordset(strPList)
    
    rsWList.MoveLast
    rsWList.MoveFirst
    
    ''Iterate through trending analysis wells
    While Not rsWList.EOF
        For Each rsWFld In rsWList.Fields
            well = rsWFld
        
            'Delete old tables if they already exist
            On Error Resume Next
            DoCmd.DeleteObject acTable, "ta_" & well
        
            ''Output table for each TA well (collection id and decimal date) for samples in date range containing TA parameters


''I'VE REMOVED THE CODE WHICH SETS strSQL EQUAL TO A SQL QUERY THAT OUTPUTS A UNIQUE TABLE NAMED "ta_[wellname]"
''FOR EACH WELL LISTED IN rsWList. THIS CODE WORKS FINE BUT IS LONG


            CurrentDb.Execute strSQL, dbFailOnError
            
            [COLOR=#ff0000]Set tblTAWell = db.TableDefs("ta_" & well)[/COLOR]

             'MsgBox tblTAWell.Name
            rsPList.MoveLast
            rsPList.MoveFirst
            
            ''Iterate through defined trending analysis parameters, adding fields and populating records in "ta_[wellname]" table
            While Not rsPList.EOF
                For Each rsPFld In rsPList.Fields
                    param = rsPFld
                    
                    ''Add field
                    Set newFld = tblTAWell.CreateField(param, dbDouble)
                    tblTAWell.Fields.Append newFld
                    
                    ''Populate field
                    strSQL = "UPDATE [ta_" & well & "] INNER JOIN (tbl3_SampleResults INNER JOIN tblTrendingAnalysisParameters " & _
                        "ON tbl3_SampleResults.STORETCode = tblTrendingAnalysisParameters.STORETCode) ON [ta_" & well & "].CollectionId = " & _
                        "tbl3_SampleResults.CollectionID SET [ta_" & well & "]." & param & " = [tbl3_SampleResults]![Value] " & _
                        "WHERE (((tblTrendingAnalysisParameters.Param)=""" & param & """));"
                    
                    CurrentDb.Execute strSQL, dbFailOnError
                                        
                    rsPList.MoveNext
                Next rsPFld
            Wend

Any tips will be much appreciated! By the way I'm running Access 2010 on a 64 bit Windows 7 Enterprise Operating System, although that probably doesn't effect my issue here.

 

[JimmyMack]

Solved it. I simply needed to add the following line of code after setting the TableDef variable (tblTAWell) to a new table:

db.TableDefs.Refresh

Simple, I know...I guess I just needed a nap!

 

[strive4peace]

glad you got it ~ it is a good idea to add this after a refresh:

DoEvents

DoEvents is a good way to say, "Wake Up!"

 

[JimmyMack]

Thanks! I will add that to the code and hopefully to my memory!

 

[strive4peace]

you're welcome happy to help