Solved

Append Data Using VBA With Criteria

Posted on 2002-07-29
11
496 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
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…

740 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