Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 533
  • Last Modified:

Append Data Using VBA With Criteria

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
WonHop
Asked:
WonHop
  • 6
  • 5
1 Solution
 
nico5038Commented:
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
 
WonHopAuthor Commented:
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
 
nico5038Commented:
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
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
WonHopAuthor Commented:
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
 
nico5038Commented:
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
 
WonHopAuthor Commented:
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
 
nico5038Commented:
Your "original" database can link to the tables from the .mdb that's mailed...
The "mail" database won't know....

Clear ?

Nic;o)
0
 
WonHopAuthor Commented:
Thanks Nico.   Got everything I needed.

WonHop

0
 
nico5038Commented:
Glad I could help ;-)

Success with the application !

Nic;o)
0
 
WonHopAuthor Commented:
Thank Nico.   BTW  where are you located.  I am in TX.
0
 
nico5038Commented:
I'm dutch, thus living in The Netherlands (also called "Holland") and "world famous" for it's windmills, dikes and woodshoes ;-)

Nic;o)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now