Looping through hundreds of tables to create one new table with data from all - continued... 1

[Moss100]

Hello All and happy New Year

I started a thread a while ago - thread700-1762245 -

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)

  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

 

[dhookom]

What is the name of the date field?
Did you add the field for the property link?


Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

Hello Duane,

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

 

[dhookom]

Date is a horrible name for a field since it is a function name but I assume this is something that you can't change.

Assuming Property_Link is a numeric field, you could try something like:

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
     [COLOR=#EF2929]' you will need to enter all the field names from the tables.[/color]
     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

 

[MajP]

I would not do it as Duane suggests if there is a lot of fields in the tables. Building a sql insert string that can handle lots of date, text, and numeric fields by hand will be really hard unless you build it through code. I also would not do what you suggest. I am going to read all the tables and insert all the fields. If the dates are outside the range then use a query to pick the correct range. Report missing properties after the fact.

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.

 

[Moss100]

Hello I have tried the following, but obviously something is not right - please could someone have a look. Thank you

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"

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

 

[dhookom]

Try add the missing &:

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")[highlight #FCE94F][COLOR=#EF2929] &[/color][/highlight] ", [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

 

[Moss100]

What magnificent code - with a click of the mouse and 30 seconds the table is filled with 666481 records .....yipeee

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

 

[dhookom]

You can create queries that pull data from tables in other databases. Look at the query properties. View the SQL of the query and convert it to the VBA.

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

Thanks Duane -

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:

Set db = OpenDatabase("C:Temp\PropertyDB.mdb")

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

 

[dhookom]

Just link the one external table for the insert.

If you have trouble, please come back with your code.

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

Is it possible to insert into the external database without creating a link in the original database or does this really complicate matters?

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.

 

[MajP]

So it sounds like you just want to do it vice versa. You have a commercial "source" database with a bunch of tables named 1,2,3 .... Your "destination" database has the combined table. In your destination database you could link to the tables in your source database and run the code in your destination. Without linking you could use the IN predicate of the SQL clause providing a database path.

https://msdn.microsoft.com/en-us/library/bb177907(v=office.12).aspx

 

[Moss100]

No luck with my efforts below:

Dim db As DAO.Database
  Dim rs As DAO.Recordset
  Dim strPropertyDB As String
  Dim strAccountDB As String
  Dim strSQL As String
  Dim strSQLInsert As String
  Dim strWhere As String
  
  strPropertyDB = "C:\Temp\PropertyDB.mdb"
  strAccountDB = "C:\Temp\AccountDB.accdb"
  
  strWhere = " WHERE [Date] Between #" & Me.dateStart & "# AND #" & Me.dateEnd & "# "
  Set db = OpenDatabase(strPropertyDB)
  
  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 IN strAccountDB([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"