VBA script to append a number of tables

Hi,

Grateful for assistance.

I would like to append a number of tables to a main table. I know I can do it through the use of individual Append queries and then run a macro to run all the append queries.

However I was wondering if it could be done using VBA code.

I am using A97.

Thanks
PipMicAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gplanaCommented:
If you are using SQL, you have the UNION clause for append results from different SELECT statements:

SELECT field1, field2, ...., fieldN
FROM table1
UNION
SELECT field1, field2, ..., fieldN
FROM table2;

You should ensure that both SELECTS have the same number of fields and that data type of every field on SELECT 1 matches its corresponding field on SELECT 2.

Hope it helps.
0
PipMicAuthor Commented:
Hi,

Do I have to use a DoCmd?
0
gplanaCommented:
If you are using Microsoft Access, you should use RunSQL:

http://msdn.microsoft.com/en-us/library/office/aa220717%28v=office.11%29.aspx
0
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

gplanaCommented:
Sorry, I was wrong. With RunSQL  you can execute SQL statements but you want to execute a SELECT, for which you should create a recordset.



    Dim SQL As String
    Dim pstrcorpname  As String

    SQL = "SELECT CorpName FROM [tblCorp Name] " & _
                "WHERE Corp=" & pstrco

    With CurrentDb.OpenRecordset(SQL)
        If Not .EOF Then
            pstrcorpname = !CorpName
        End If
        .Close
    End With

    MsgBox "pstrco = " & pstrco & " pstrcorpname = " & pstrcorpname
0
mbizupCommented:
You can write your Append queries in VBA and run them like this:


dim strSQL as string
strSQL = "INSERT INTO Table1 (Field1, Field2...) SELECT Field1, Field2.. FROM Table2"
CurrentDB.Execute strSQL, dbFailOnError

strSQL = "INSERT INTO Table1 (Field1, Field2...) SELECT Field1, Field2.. FROM Table3"
CurrentDB.Execute strSQL, dbFailOnError

strSQL = "INSERT INTO Table1 (Field1, Field2...) SELECT Field1, Field2.. FROM Table4"
CurrentDB.Execute strSQL, dbFailOnError

' etc

Open in new window


If the tables you are appending data FROM are sequentally numbered, you can use a loop.  For example, this will append table 2 through 5 to table 1:

dim strSQL as string
Dim I as integer

For I = 2 to 5
strSQL = "INSERT INTO Table1 (Field1, Field2...) SELECT Field1, Field2.. FROM Table" & I
CurrentDB.Execute strSQL, dbFailOnError
Next I 

Open in new window


Alternatively, if you already have your append queries written you can simply use the Open Query command:


Docmd.OpenQuery "YourFirstAppendQuery"
Docmd.OpenQuery "YourSecondAppendQuery"
Docmd.OpenQuery "YourThirdAppendQuery"

' ETC

Open in new window

0
PipMicAuthor Commented:
Hi,

this is my append query in SQL view:

INSERT INTO [All] ( Body, Title, [End Date], [a], Interest, [d], Holder, [c], add1, add2, add3, [Tel No], [Client id], DOB, Remarks, RealDate )
SELECT [Tbl_1].Body, [Tbl_1].Title, [Tbl_1].[End Date], [Tbl_1].[a], [Tbl_1].Interest, [Tbl_1].[d], [Tbl_1].Holder, [Tbl_1].[c], [Tbl_1].add1, [Tbl_1].add2, [Tbl_1].add3, [Tbl_1].[Tel No], [Tbl_1].[Client id], [Tbl_1].DOB, [Tbl_1].Remarks, [Tbl_1].RealDate
FROM [Tbl_1];


Both tables are identical in structure and the everything in Tbl_1 is appended to All

the vba looks very longwinded and was hoping to say something like :

Append all the records from Tbl_1 to All without exception and then do the same routine for different tables i.e. copy the contents from different tables to ALL.

Hope this makes sense!
0
PipMicAuthor Commented:
Hi mbizup,

I like the idea of looping and it was more or less the idea I was trying to carry out.

I try that out.

Thanks
0
PipMicAuthor Commented:
Hi,

How can i sequentially number my tables? My tables have names!

 If the tables you are appending data FROM are sequentally numbered, you can use a loop.  For example, this will append table 2 through 5 to table 1:

Grateful for advice
0
mbizupCommented:
Are your tables all numbered like Tbl_1, Tbl_2, etc?

You can do this to append Tbl_1 through Tbl_10, for example:

dim strSQL as string
Dim i as Integer

For i = 1 to 10
strSQL = "INSERT INTO [All] ( Body, Title, [End Date], [a], Interest, [d], Holder, [c], add1, add2, add3, [Tel No], [Client id], DOB, Remarks, RealDate ) SELECT Body, Title, [End Date], [a], Interest, [d], Holder, [c],  add1, add2, add3, [Tel No], [Client id],  DOB, Remarks, RealDate FROM Tbl_" & i
CurrentDB.Execute strSQL, dbFailOnError
Next

Open in new window

0
PipMicAuthor Commented:
Hi mbizup

My tables are not numbered :(
0
mbizupCommented:
Then you may need the individual statements for each table.

How many tables are we talking about, and is this a one-time append, or is it something you will be doing regularly?

An option might be to create a table (tblAppend) with a field (TableName) for the names of the tables to be appended.  If you do this, you can loop through the table containing the names of tables to append like this:


Dim rs as DAO.Recordset
Set rs = CurrentDB.OpenRecordset("SELECT TableName FROM tblAppend", dbOpenDynaset)

dim strSQL as string

Do Until rs.EOF
strSQL = "INSERT INTO [All] ( Body, Title, [End Date], [a], Interest, [d], Holder, [c], add1, add2, add3, [Tel No], [Client id], DOB, Remarks, RealDate ) SELECT Body, Title, [End Date], [a], Interest, [d], Holder, [c],  add1, add2, add3, [Tel No], [Client id],  DOB, Remarks, RealDate FROM " & rs("TableName")
CurrentDB.Execute strSQL, dbFailOnError
Loop

rs.close
set rs = nothing

Open in new window

0
PipMicAuthor Commented:
Tried this but (at bold line it stops) ----- i'm using A97

Private Sub Command1_Click()


Dim rs As Recordset
Set rs = CurrentDb.OpenRecordset("SELECT database FROM Tbl_databases", dbOpenDynaset)

Dim strSQL As String

Do Until rs.EOF
strSQL = "INSERT INTO [00 EoI - All] Select * FROM " & rs("database")
CurrentDb.Execute strSQL, dbFailOnError

'DoCmd.RunSQL (strSQL)
Loop

rs.close
Set rs = Nothing


   
End Sub
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mbizupCommented:
What is the exact error message?
0
PipMicAuthor Commented:
Run Time error 3131

Syntax error in FROM clause
0
mbizupCommented:
If your table names contain spaces, special characters or reserved words you'll have to enclose them in square brackets like this:


strSQL = "INSERT INTO [00 EoI - All] Select * FROM [" & rs("database")  & "]"


Complicates things a bit, doesn't it? :-)  

That is why it is best to use good (standard) naming conventions like:

tbl00EoIAll

instead of 00 EoI - All
0
PipMicAuthor Commented:
Hi,

Apologies for the naming convention :{

The code is looping and table is being populated endlessly.

I have tried this and works fine....



Private Sub Command1_Click()

Dim db As Database
       
        Dim rst As Recordset
        Dim strSQL As String
       
        Set db = CurrentDb()
        Set rst = db.OpenRecordset("Tbl_databases")
       
    If rst.EOF Then
   
    Else
   
    Do While Not rst.EOF
   
        DoCmd.SetWarnings False

            strSQL = "INSERT INTO [00 EoI - All] SELECT * FROM [" & rst.Fields("database") & "]"
                     
        DoCmd.RunSQL (strSQL)

        DoCmd.SetWarnings False

        rst.MoveNext
       

        Loop
       
        End If
       
     rst.close            'Close what you opened.


   
End Sub
0
mbizupCommented:
Sorry - I forgot to include the MoveNext in my code, so it is endlessy repeating the same table.

Corrected:

Dim rs as DAO.Recordset
Set rs = CurrentDB.OpenRecordset("SELECT TableName FROM tblAppend", dbOpenDynaset)

dim strSQL as string

Do Until rs.EOF
strSQL = "INSERT INTO [All] ( Body, Title, [End Date], [a], Interest, [d], Holder, [c], add1, add2, add3, [Tel No], [Client id], DOB, Remarks, RealDate ) SELECT Body, Title, [End Date], [a], Interest, [d], Holder, [c],  add1, add2, add3, [Tel No], [Client id],  DOB, Remarks, RealDate FROM " & rs("TableName")
CurrentDB.Execute strSQL, dbFailOnError
rs.MoveNext
Loop

rs.close
set rs = nothing

Open in new window


Your RunSQL command works - but for the record, currentdDB.execute is a better option.

The reason for that is RunSQL will happily complete regardless of errors in your SQL, without giving you any indication that the query did not work.

CurrentDB.Execute, on the other hand will give you meaningful messages if the queries fail if you use the dbFailOnError option.
0
PipMicAuthor Commented:
Got me there in the end...thanks
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.