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.
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.
Code:
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.