VBA Help - Append Multiple Tables Into 1 Main Table

[Lobbie168]

Hi all,

I am new to VBA and hope you can assist.

I have a database with,
1. Around 50 - 1000 tables of the same data structure.
2. Each week the number of tables differ.
3. All the tables start with CustAcctXXX where XXX is the number eg CustAcct123, CustAcct456 and so on...

I need to append all the records in these tables into a MAIN Table. No PK is needed. Creating the append queries is very time consuming and not effective because the number of tables differ each week.

Is there a way to loop through the database for these tables and append the records into the MAIN table using VBA?

Many thanks for your help in advance.

Regards,
Lobbie168

 

[ajetrumpet]

Lobbie,

I thought I'd peak in on this one. I might be able to get you started here...

Look at the append SQL (INSERT INTO table, etc, etc...) syntax in the help menu if you don't already know it. What comes to mind is...

For each TableDef in CurrentDB.TableDefs

(loop the process here)

This probably works, but you might just be better off looping through all of the tables as described above and copying the values from every field over to the MAIN table, if there aren't a lot of them (the fields), that is. Updating that way might be a little bit easier. Take a look at the help menu for the syntax lines. That should get you started on the right track...

 

[Rabbie]

Depending on the number of tables and the amount of records it might be worth while looking at the possibility of building the append queries using loops in VBA and then running them from your VBA code.

 

[Lobbie168]

Thank you guys for your feedback.

Let me be more specific.

This database has varying number of tables from 50 - 1500 each week with the same table name except the last few digits. Each table has 4 fields and only contain 1 record each.

I would like to have a separate database to loop through the first database for each table, import and append the imported record to a MAIN table.

I am really new to VBA, I do not know where to start and I need this quite urgently hence if you can advise a couple of code samples would be greatly appreciated.

Many thanks again,
Lobbie

 

[ajetrumpet]

This database has varying number of tables from 50 - 1500 each week with the same table name except the last few digits. Each table has 4 fields and only contain 1 record each.

I would like to have a separate database to loop through the first database for each table, import and append the imported record to a MAIN table.

Lobbie, here is something that might help. This code should loop through all the tables and copy the one record in each to the main table...

Function newfunction()

  Dim db As Database
    Dim def As TableDef
      Dim str As String
        Dim newrs As Recordset
          Dim oldrs As Recordset

Set newrs = db.OpenRecordset("MAIN")

    For Each def In db.TableDefs

        str = def.Name

            If str <> "MAIN" Then
            Set oldrs = db.OpenRecordset (str)

          oldrs.MoveFirst
  
        newrs.AddNew

        newrs!field1 = oldrs!field1
          newrs!field2 = oldrs!field2
          newrs!field3 = oldrs!field3
        newrs!field4 = oldrs!field4

        newrs.Update

      oldrs.Close

            End If

      Next def

    set oldrs = nothing
  
  set newrs = nothing

End Function

Keep in mind that this code will only work if you already have a table named MAIN created with all the same fields as the others.

 

[Lobbie168]

Many thanks Adam. Your sample code is exactly what I need to get a head start. Will let you know how I go.

 

[boblarson]

Since you are on the way to getting a single table, I just have to ask. Why are there that many tables? How do they get created and why? Are you getting this info from some external source?

 

[Lobbie168]

Hi boblarson,

The source files are all XML and external. With little knowledge on XML and with insufficient time, I found an utility that allows me to import the XML files into Access. Sort of a quick and dirty solution that will last me for 6 months at least. There are many imported tables and only tables that start with certain names contain the data I required. The problem is the utility creates multiple tables eg CustAcct1, CustAcct2, CustAcct3.......I need to combine them all into one MAIN table.

Obviously, there are better ways to do this but this should be suffice for the time being. We are going to get the XML files loaded properly into a database sometime in the future.

Thanks,
Lobbie

 

[boblarson]

I just had to ask because it seems like so much work to do. I actually think you could work out a solution where you didn't have to import all of the tables to separate tables, but instead import into an existing one so then they all would just come into the one table and save you a lot of hassle.

 

[Lobbie168]

Mate, you are absolutely correct that it seems a lot of work.
Like I said, I an new to VBA and absolutely a noob to XML. It is too much an ask for my current capability.

The XML files are all in 1 main folder with many sub-folders. Conceptually, I know that I will need to trawl through all the folders for the XML files, look for the data I need, import and append to a main table. Writing the code is another story though.

Any chance for an import and append to main table sample code?

Thanks muchly.

 

[Lobbie168]

It works!!!

Hi all,

Thank you all for your advice and sample codes, especially ajetrumpet.

His code works and I am not sure ajetrumpet did it delibrately or not.
I encountered a runtime error for object not set and made some changes to his code.

I added near the beginning of the function,

Set db = CurrentDb​

and

Set db = nothing​

near the end of the function and it works like a breeze.

many thanks again.

Rdgs,
Lobbie

 

[boblarson]

Any chance for an import and append to main table sample code?

I wish I had enough time available to do that for you. I know it would be a good time saver. But, unfortunately, I don't for the forseeable future.

 

[Lobbie168]

mate, not to worry. the solution I have now will last me for a long while.
Greatly appreciate if you can point me to some good books I can start learning access vba and xml stuff. having these knowledge and capability will be useful in the future. thanks a million

 

[daFreq]

Hi all,

Following on Lobbie168's question. I have a similar problem but in my database I have tables such as 1234abcd, 1234efgh, 5678abcd, 5678efgh...etc.

I want to append all tables with 1234 as prefix into a single table called 1234; all tables with 5678 prefix into a single table called 5678...etc. And of course, the number of tables with each prefix is different (e.g. there might be 3 tables with 1234 as prefix; but 5 tables with 5678 as prefix).

I'd like to create a button on a form where when one clicks on it, it calls a VBA function or an SQL query. It can either then asks which "prefix" I want to combine (I'll then select the prefix from a drop down or something like that), or simply combine all tables based on their prefix automatically (e.g. in this example, clicking the button will simply create two tables: 1234 and 5678).

Any help will be greatly appreciated!