Looping through hundreds of tables to create one new table with data from all - continued...
Looping through hundreds of tables to create one new table with data from all - continued...
(OP)
Hello All and happy New Year :)
I started a thread a while ago - thread700-1762245: How to loop through hundreds of tables and build a new table. -
In this I was trying to find out how to loop through hundreds of tables and extract data to a single table.
With help from those here I was able to do this.
I now need to take this project further and would appreciate help over the next few weeks developing the code robustly,
The concept is as follows:
I need to develop one table on which I can run Reports.
The database that I am using is commercially written, so I cannot alter the way in which it is structured.
It is a property database.
Each property is assigned its own table (I know this is not normalised - but it is, what it is)
The tables are identical in structure and are numbered 1,2,3 etc... these numbers correspond to the REFERENCE in the PROPERTY table.
I have created a table to hold the extracted data and called it TEST
The code which I have so far (and is working) is below (Thank you Mr Hookom)
So to get to my goal I need to develop the code to meet the following:
1. Rather than extract 1 record from each table I need to extract ALL records BETWEEN a specified date range.
(On my form I have a textboxes called DateStart and DateEnd)
2. I need the code only to look at tables which match the exact reference from the PROPERTY table.
(i.e. for Property 1, in the PROPERTY table there will be a number 1 in the REFERENCE field.
In the database there will be a table called 1, but there will also be a table called DT1 - I'm not sure if the current code ignores the DT1 or looks at both of these)
3. If a table is missing, I need the code to pop up a warning showing the missing table number. Once the warning is acknowledge the code will skip it and continue)
4. Also if possible, I would like to add a field to the TEST table called Property_Link, to which
I would like to add the number of the table being processed so I can tell which property the records came from.
I hope the above may interest someone. Help much appreciated.
My VBA is rather poor and I really need this code to be robust, accurate and reliable.
Many thanks in anticpation of your assistance.
Regards Mark
I started a thread a while ago - thread700-1762245: How to loop through hundreds of tables and build a new table. -
In this I was trying to find out how to loop through hundreds of tables and extract data to a single table.
With help from those here I was able to do this.
I now need to take this project further and would appreciate help over the next few weeks developing the code robustly,
The concept is as follows:
I need to develop one table on which I can run Reports.
The database that I am using is commercially written, so I cannot alter the way in which it is structured.
It is a property database.
Each property is assigned its own table (I know this is not normalised - but it is, what it is)
The tables are identical in structure and are numbered 1,2,3 etc... these numbers correspond to the REFERENCE in the PROPERTY table.
I have created a table to hold the extracted data and called it TEST
The code which I have so far (and is working) is below (Thank you Mr Hookom)
CODE -->
Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Dim strSQLInsert As String Set db = CurrentDb 'strSQL = "SELECT [Reference] FROM Property WHERE [Archive]=True ORDER BY [Reference]" strSQL = "SELECT [Reference] FROM Property ORDER BY [Reference]" Set rs = db.OpenRecordset(strSQL) Do While Not rs.EOF strSQLInsert = "Insert Into TEST" & _ " Select TOP 1 * From [" & rs("Reference") & "] Order By Reference Desc " db.Execute strSQLInsert, dbFailOnError rs.MoveNext Loop rs.Close Set rs = Nothing Set db = Nothing MsgBox "Complete"
So to get to my goal I need to develop the code to meet the following:
1. Rather than extract 1 record from each table I need to extract ALL records BETWEEN a specified date range.
(On my form I have a textboxes called DateStart and DateEnd)
2. I need the code only to look at tables which match the exact reference from the PROPERTY table.
(i.e. for Property 1, in the PROPERTY table there will be a number 1 in the REFERENCE field.
In the database there will be a table called 1, but there will also be a table called DT1 - I'm not sure if the current code ignores the DT1 or looks at both of these)
3. If a table is missing, I need the code to pop up a warning showing the missing table number. Once the warning is acknowledge the code will skip it and continue)
4. Also if possible, I would like to add a field to the TEST table called Property_Link, to which
I would like to add the number of the table being processed so I can tell which property the records came from.
I hope the above may interest someone. Help much appreciated.
My VBA is rather poor and I really need this code to be robust, accurate and reliable.
Many thanks in anticpation of your assistance.
Regards Mark
RE: Looping through hundreds of tables to create one new table with data from all - continued...
Did you add the field for the property link?
Duane
Hook'D on Access
MS Access MVP
RE: Looping through hundreds of tables to create one new table with data from all - continued...
The date field is called DATE
I have now added a field to the TEST table called Property_Link
Many thanks, regards and a very Happy New year to you
Mark
RE: Looping through hundreds of tables to create one new table with data from all - continued...
Assuming Property_Link is a numeric field, you could try something like:
CODE --> vba
Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Dim strSQLInsert As String Dim strWhere as String strWhere = " WHERE [Date] Between #" & Me.DateStart & "# AND #" & Me.DateEnd & "# " Set db = CurrentDb strSQL = "SELECT [Reference] FROM Property ORDER BY [Reference]" Set rs = db.OpenRecordset(strSQL) Do While Not rs.EOF ' you will need to enter all the field names from the tables. strSQLInsert = "Insert Into TEST (Property_Link,[Field1], [Field2], [Field3], [Field4], [...]) " & _ " Select " & rs("Reference") ", [Field1], [Field2], [Field3], [Field4], [...] From [" & _ rs("Reference") & "] " & strWhere & " Order By Reference Desc " db.Execute strSQLInsert, dbFailOnError debug.Print strSqlInsert rs.MoveNext Loop rs.Close Set rs = Nothing Set db = Nothing MsgBox "Complete"
Duane
Hook'D on Access
MS Access MVP
RE: Looping through hundreds of tables to create one new table with data from all - continued...
1. Loop the tabledefs
2. determine what table defs to include by name
3. Get the table name to use as a property reference
4 Loop the records in the table def
5. loop the fields in the table def
6. update the main table where the table fields equal the main table
7. there is probably an extra field in the main table to hold property ID. Update that
8. After the fact. Identified all missing properties
9. If you want delete the fields outside the date range or just query the range.
RE: Looping through hundreds of tables to create one new table with data from all - continued...
CODE -->
It shows the below section as red
' you will need to enter all the field names from the tables.
strSQLInsert = "Insert Into TEST ([Property_Link],[Date])" & _
" Select " & rs("Reference") ", [Date] From [" & _
rs("Reference") & "] " & strWhere & " Order By Reference Desc "
RE: Looping through hundreds of tables to create one new table with data from all - continued...
CODE --> vba
Dim db As DAO.Database Dim rs As DAO.Recordset Dim strSQL As String Dim strSQLInsert As String Dim strWhere As String strWhere = " WHERE [Date] Between #" & Me.DateStart & "# AND #" & Me.DateEnd & "# " Set db = CurrentDb strSQL = "SELECT [Reference] FROM Property ORDER BY [Reference]" Set rs = db.OpenRecordset(strSQL) Do While Not rs.EOF ' you will need to enter all the field names from the tables. strSQLInsert = "Insert Into TEST ([Property_Link],[Date])" & _ " Select " & rs("Reference") & ", [Date] From [" & _ rs("Reference") & "] " & strWhere & " Order By Reference Desc " db.Execute strSQLInsert, dbFailOnError Debug.Print strSQLInsert rs.MoveNext Loop rs.Close Set rs = Nothing Set db = Nothing MsgBox "Complete"
Duane
Hook'D on Access
MS Access MVP
RE: Looping through hundreds of tables to create one new table with data from all - continued...
Thank you very much.....
As with most projects I start, the end goal has changed a little.
I'd like to acheive the same result, but instead of running the code from within the database, I would like to run the code from a seperate database - i.e. run code that loops through tables in an external database.
So the database with all the tables I want to harvest is:
C:Temp\PropertyDB.mdb
The database that I want to populate is (and contains the table TEST which is to be populated)
C:Temp\AccountDB.mdb
Thank you for your continued, invaluable help. Mark
RE: Looping through hundreds of tables to create one new table with data from all - continued...
Duane
Hook'D on Access
MS Access MVP
RE: Looping through hundreds of tables to create one new table with data from all - continued...
The query suggestion works, but my problem is that I do not want to create the table in the original database (I should of course realised that at the start).
If I change set db to:
CODE -->
I can run the code from the external database, but it is still wanting to insert the data into the local C:Temp\AccountDB.mdb database, NOT my external one.
Can I tweak the code so that it populates the table in my external database, or is a different approach needed?
Thank you. Mark
RE: Looping through hundreds of tables to create one new table with data from all - continued...
If you have trouble, please come back with your code.
Duane
Hook'D on Access
MS Access MVP
RE: Looping through hundreds of tables to create one new table with data from all - continued...
I would rather not modify the original database if at all possible as it is a commercially written one and doing so 'might' cause issues (future upgrades etc)
Thank you Mark.
RE: Looping through hundreds of tables to create one new table with data from all - continued...
https://msdn.microsoft.com/en-us/library/bb177907(...
RE: Looping through hundreds of tables to create one new table with data from all - continued...
CODE -->