Merge multiple tables in Access 2003 using VB6 Code

[jbfsantelices]

Good day to everyone...I'm a newbie here and a novice in VB6 programming...

I really having a hard time formulating the correct syntax on how to merge multiple tables in Access 2003 using VB6. All the tables that I want to merge had the same fields.

For example Table1 includes these Fields: LineNumber,JobName,Batch,Coder,DocDate,DocChar

And the next table also have these fields:
LineNumber,JobName,Batch,Coder,DocDate,DocChar

Thanks guys...

[vb5prgrmr]

Okay, As long as none of the fields that you mention are of an autonumber field you can do something like...

strSQL = "INSERT INTO Table1(LineNumber, JobName, Batch, Coder, DocDate, DocChar) SELECT Table2.LineNumber, Table2.JobName, Table2.Batch, Table2.Coder, Table2.DocDate, Table2.DocChar FROM Table2"

Then execute via command object and what this will do is take the information from Table2 and append it into Table1.

Good Luck

[jbfsantelices]

Thanks vb5prgrmr, I really appreciate it!

But what if I want to merge multiple tables having different table names and came from different database, coz the program that I was doing is some kind of database utility that will merge tables by and by...

Thanks for the help again...

[vb5prgrmr]

Then, (If I remember correctly) you need to prefix tablename (Table1) with the database name and for access that would be the path to the database. So, what you do is open the database that you want to move the records from and do something like...

INSERT INTO Table1 ( vText ) IN 'c:\z\test2.mdb'
SELECT Table1.vText
FROM Table1;

Okay, so what the above does is move the data from Table1 in Test1.mdb to Table1 in Test2.mdb...

NOTE: For future reference, using the Microsoft Access Query builder and design window is a very smart thing to do no matter what database or DBMS system you are working with. Meaning, if you are working with SQL server, MySQL, Progress, Oracle, etc. and you need to create a query of some sort, create a database with the appropriate tables that you want to work with and use access to build your query strings for you.

Now back to our regular scheduled program...

So what you would need to do then is something like...

strSQL = "INSERT INTO Table1(LineNumber, JobName, Batch, Coder, DocDate, 
DocChar) IN 'c:\z\test2.mdb' SELECT Table1.LineNumber, Table1.JobName, Table1.Batch, Table1.Coder, Table1.DocDate, Table1.DocChar FROM Table1"

And just to make sure that you are reading the above correctly. The above says that I have a database open and I want to insert from the currently open database to my main database named test2.

Good Luck