Solved

Append Data Using VBA With Criteria

Posted on 2002-07-29
11
486 Views
Last Modified: 2012-06-27
Hello all.  I would like to know if there is a way to append data from one table to another using VBA with a criteria?

I need to get data from table “tsub_Issue_To_ICWG” and Append it to “tsub_Boeing_Issue_To_ICWG

This is what my VBA code looks like so far:
rstAppBoeingIssueToICWG.Open "SELECT * FROM tsub_Issue_To_ICWG WHERE num_Foreign_Issue =" & intPrimaryKeyICWG, cnn, adOpenKeyset, adLockOptimistic

I know how to do the With……With End….just looking for a more efficient way

Thanks
WonHop
0
Comment
Question by:WonHop
  • 6
  • 5
11 Comments
 
LVL 54

Accepted Solution

by:
nico5038 earned 250 total points
ID: 7185890
Did you try:

rstAppBoeingIssueToICWG.Open "INERTS INTO tsub_Boeing_Issue_To_ICWG ( field1, field2, etc... ) SELECT * FROM tsub_Issue_To_ICWG WHERE num_Foreign_Issue =" & intPrimaryKeyICWG, cnn, adOpenKeyset, adLockOptimistic

Replace the "field1, field2, etc..." with the fields needed to be moved. Don't pass the key when it's an autonumber in the tsub_Boeing_Issue_To_ICWG table !

Nic;o)
0
 
LVL 2

Author Comment

by:WonHop
ID: 7185923
Thanks Nico.
I forgot to mention.  One table is an exact copy of the other except for the name.  Do I still need to add the name of each field?  

This is the first step in what I am doing.  I will be posting another question about taking the data once it is appened and sending that table to another database.

What I am doing taking certain data from one table, putting it in another table, Then sending the new tables to another database, then we will be emailing the small database to another offsite group to make any needed changes.

WonHop
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7186065
Just curious, why don't you just use an "export" of that table and/or query to mail directly using a docmd.sendobject ?

For the fieldnames, "yes" specify them all, except the key field when that's an autonumber!
(I normally make first the query in the query editor and copy/paste the generated SQL to the code.

Nic;o)
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
LVL 2

Author Comment

by:WonHop
ID: 7186177
We are only sending them the data from the records that are "OPEN".  That is what will be put in the database to be mailed.  That will help keep the database small.
I have code to do the email.
 
But, I will be willing to increase the point here to 250 instead of asking another question on exporting the data to the new database to be emailed.

If the Append or Make Table could be sent directly to the new database and replace the current table, that would be a lot better to.

From Current: D:\data\Data_Nate\ICWG_Database\ICWG.mdb
To New: D:\data\Data_Nate\ICWG_Database\ICWG_Boeing.mdb

The main thing is that I need to replace the current data with the new data.  
I was going to run Delete queries to delete all of the data in the current table, append the new data, then replace the current table in the Mail version database with new updated table.

WonHop


0
 
LVL 54

Expert Comment

by:nico5038
ID: 7186201
The "Open" implies that you need a query with s WHERE status = 'open', such a query can also be used for mailing.

For placing the data in another .mdb you can just link the "New" table from the "old" .mdb. This enables you to use the "select open" query as an append pointing to that table in the other "New" database.

The same can be done with a delete query to empty that table.

What more do you need?

Nic;o)
0
 
LVL 2

Author Comment

by:WonHop
ID: 7186311
The "Open" part is already done.  The only thing left is to put the table into the new database.  This can't be linked because the new database will be sent to another company that do not have access to our servers.

I know in Access 97 using DAO, the db.Execute thing was used.
How would I do that using ADO?

I know you are trying to do what truly makes sense, (it is a long story because they used Oracle on this database in the past, and they did not have access to change anything) but this has to be done this way because of the things they are doing. Then send a copy of the table to the new database so that it can be emailed.

WonHop
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7186350
Your "original" database can link to the tables from the .mdb that's mailed...
The "mail" database won't know....

Clear ?

Nic;o)
0
 
LVL 2

Author Comment

by:WonHop
ID: 7186675
Thanks Nico.   Got everything I needed.

WonHop

0
 
LVL 54

Expert Comment

by:nico5038
ID: 7186679
Glad I could help ;-)

Success with the application !

Nic;o)
0
 
LVL 2

Author Comment

by:WonHop
ID: 7186690
Thank Nico.   BTW  where are you located.  I am in TX.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7186707
I'm dutch, thus living in The Netherlands (also called "Holland") and "world famous" for it's windmills, dikes and woodshoes ;-)

Nic;o)
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question