Solved

Append Data Using VBA With Criteria

Posted on 2002-07-29
11
453 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 2

Author Comment

by:WonHop
Comment Utility
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
Comment Utility
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 
LVL 2

Author Comment

by:WonHop
Comment Utility
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
Comment Utility
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
Comment Utility
Thanks Nico.   Got everything I needed.

WonHop

0
 
LVL 54

Expert Comment

by:nico5038
Comment Utility
Glad I could help ;-)

Success with the application !

Nic;o)
0
 
LVL 2

Author Comment

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

Expert Comment

by:nico5038
Comment Utility
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

728 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now