Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Append Data Using VBA With Criteria

Posted on 2002-07-29
11
Medium Priority
?
530 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 1000 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
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…
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…
Suggested Courses

564 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