How to loop through hundreds of tables and build a new table. 2

[Moss100]

Hello, I am a little behind with a project and could do with some help / pointers.

I am working with a database that has several thousand tables (yes I know that is a long way from normalized, but it is a professional program and one which I have no option to modify.

The tables are all named as numbers i.e. 1,2 3, etc....

I have a tables called Property, from which I can select all the tables of interest (around 500)

I need to look at every table, go to the last record and copy this record into a new table (so I will end up with a new table with 500 records - those being the last record of each of the individual tables).

I'm guessing I will need to do the following:

1. Create a new table which has fields matching the tables I am looking at (let say I csll it tbl_Master)
2. Open the first table
3 Go to the last record
4. Select the last record
5. Append this record to my tbl_Master
6. go to the next table etc...

I would much appreciate direction / methods / thoughts

Many thanks Mark

 

[Andrzejek]

Do those 500 tables have the same structure? I.e. field names, field types, field order, etc.?
And when you say: "Go to the last record" - what constitutes "last record"?

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Moss100]

Hello - yes they have the same structure.

They have a unique ID called Reference (1 appears at the top...)

Many thanks Mark

 

[dhookom]

So by "last" do you mean the record with the max ID value?

I would use the query designer to create the base SQL statement and then use code to change the SQL of the query and run it.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

If your Select statement to get the last record (from table 7, for example) would be something like this:
[tt]
Select TOP 1 (*)
From 7
Order By ID Desc
[/tt]
and you said you have 500 tables named 1, 2, 3, ..., 499, 500

I would just do a simple loop like this:

Dim i As Integer
Dim strSQL As String

For i = 1 to 500
    strSQL = "Insert Into tbl_Master " & _
        " Select TOP 1 (*) From " & i & _
        " Order By ID Desc"

    Db.Execute strSQL
Next i

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[dhookom]

Good job Andy. Very concise code and nice explanation.

Duane
Hook'D on Access
MS Access MVP

 

[Andrzejek]

Thanks Duane, that means a lot coming from you

Let's see what Mark says about it...

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[Moss100]

Thank you very much for your help..

I used the following code on a command button, but it gave me the error: runtime error 424, object required.

Any idea - thank you. (tables 1197 & 1198 exist)

Private Sub Command0_DblClick(Cancel As Integer)

Dim i As Integer
Dim strSQL As String

For i = 1197 To 1198
    strSQL = "Insert Into tbl_Master " & _
        " Select TOP 1 (*) From " & i & _
        " Order By ID Desc"

    db.Execute strSQL
Next i
End Sub

 

[Moss100]

I have made some progress:

Using the code below it works, but errors if the table does not exist. How could I get it to skip the table if it does not exist?

Many thanks Mark

Private Sub Command0_DblClick(Cancel As Integer)


Dim i As Integer
Dim strSQL As String

Set db = CurrentDb

    For i = 500 To 1000
    strSQL = "Insert Into tbl_Master " & _
        " Select TOP 1 * From " & i
      
    db.Execute strSQL
Next
End Sub

 

[dhookom]

Moss100,
Can you provide an accurate description of your requirements?

You can use some code that loop through all tables but I expect you have some extra tables that shouldn't be included. Are all of the table names numbers?

Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

Hello - thank you for your help and interest.

I will try and describe in detail what I am trying to do:

I am wanting to build a table to show current arrears for a number of property accounts.

The database (which is a commercial one and NOT written by me) sets up a new table for each property account.

As the database is quite old, there are over 3000 tables in it (all identical). There are also other tables in the database such as tbl_Property etc...

All the account tables are named using a number (basically say 1-3000 for example - although there are a few gaps, probably where users have deleted a table).

Not all of the tables are of interest to me as I am only interested in active accounts.

I am able to determine the Active properties from the tbl_Property. The [Reference] field in tbl_Property is also the same number as the corresponding account table. For example record 100 in tbl_Property, will also have an account table called 100 (although if for some reason the account table 100 was missing I would like the code to not crash).

I therefore want to get some code that can:

1. Look at tbl_Property, see which are the active properties
2. Loop through the account tables of the active properties
3. Add the last record in each table to tbl_Account_Master (which would be identical to the account table, but additionally have an extra field to record the [reference] ID

4. Optionally it would be great if rather than just adding the last record I could also have the option to add the last record where the field [Type] (which exists in the accounts tables) could be defined. For example if I set criteria for [Type] to 'rent' my master tables would be populated with the last rent that each tenant had paid etc..

Many thanks as always. Best regards Mark

 

[dhookom]

If you have all of the required numbers in a table, then use the table.

Dim db as DAO.Database
Dim rs as DAO.Recordset
Dim strSQL as String
Dim strSQLInsert as String
strSQL = "SELECT [Reference] FROM tbl_Property WHERE [Active]=True ORDER BY [Reference]"
Set rs = db.OpenRecordset(strSQL)
Do WHile Not rs.EOF
    strSQLInsert = "Insert Into tbl_Master " & _
        " Select TOP 1 * From [" & rs("Reference") & "] Order By ID Desc "
    db.Execute strSQLInsert, dbFailOnError
    rs.MoveNext
Loop
rs.Close
set rs = Nothing
set db = Nothing
MsgBox "Complete"

Wouldn't you want to store the Reference number in tbl_Master? Also, I would probably add a line of code to avoid duplicate values.


Duane
Hook'D on Access
MS Access MVP

 

[Moss100]

Thank you kindly for your answer - this has really assisted me.

I'm sorry for the delay in replying, but very much appreciate the help.

Best regards Mark