Solved

How to copy fields from table a and insert them into table b

Posted on 2009-05-09
15
858 Views
Last Modified: 2012-05-06
I am useing Delphi 7
I have access 2003 database.
I have customerinfo table in it.
I have estimates table in it.
I need to copy
FullName,Address,PhoneNumber,ZipCode,VIN,HaleWork,DentWork,Year,Make,Model,Miles from customerinfo table to estimates table. Field names are the same in both tables.
I need example code ASAP so this is 500 pts.
0
Comment
Question by:Grant Fullen
[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
  • 9
  • 6
15 Comments
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24346094
Well there are two ways of doing this: SQL, and using datasets in Delphi.
The SQL way where inside delphi you send the SQL message to the server is the best because all the work is done on the server - and all the information "stays" on the server.
But you can also do this in Delphi, where the data will have to be transported from the server, to your datasets in delphi, processed, and sent back to your server. This is only good if you are in a big hurry and you are only doing this procedure once. Otherwise it is a stupid idea.

I will do the Delphi way first because it's easier and quicker FOR ME, but I will try come back later with the SQL version. You could also ask your question in the SQL zone.

Right, so the best/easiest way to talk to an access database is using ADO.

Put two TADOTables on your form.
Using the one ADOTAble, build a connection string to connect to your access table (MDB file).
Example: Click on ADOTable1, in object inspector click on Connection String, then elpisis(...)
By "Use Connection String" click "Build Button"
Select Microsoft Jet 4.0 OLE DB PRovider
-Click NExt
Under "Select or enter database name" click the elipsis button(...) and navigate to your access database (MDB file).
Click Test Connection button.
When done, copy the connection string from your ADOTable 1 to ADOTable2.

Click on ADOtable1 and under 'Table' set the table name to 'customerinfo'
On ADOTAble2, under 'table' set the table name to 'Estimates'

Now, put a button on your form, double-click it, and put this code:
begin
  ADOTable1.first;
  while ADOTAble1.Eof = false do
  begin
    with ADOTable2 do begin
      insert;
      Fieldbyname('Fullname').value := ADOTable1.Fieldbyname('Fullname').Value;
      Fieldbyname('Address').value := ADOTable1.Fieldbyname('Address').Value;
      Fieldbyname('PhoneNumber').value := ADOTable1.Fieldbyname('PhoneNumber').Value;
      Fieldbyname('ZipCode').value := ADOTable1.Fieldbyname('ZipCode').Value;
      Fieldbyname('VIN').value := ADOTable1.Fieldbyname('VIN').Value;
      Fieldbyname('HaleWork').value := ADOTable1.Fieldbyname('HaleWork').Value;
      Fieldbyname('DentWork').value := ADOTable1.Fieldbyname('DentWork').Value;
      Fieldbyname('Year').value := ADOTable1.Fieldbyname('Year').Value;
      Fieldbyname('Make').value := ADOTable1.Fieldbyname('Make').Value;
      Fieldbyname('Model').value := ADOTable1.Fieldbyname('Model').Value;
      Fieldbyname('Miles').value := ADOTable1.Fieldbyname('Miles').Value;
      post;
    end;
    ADOTable1.Next;
  end;
end;

Notes:
- You can improve upon the field matches like this
      Fieldbyname('Miles').asInteger := ADOTable1.Fieldbyname('Miles').asInteger;
it is recommended that you study the field types of all these fields to ensure you they are the same in each table. For example if one field type is a String, and the other field type is an integer, you may have problems.
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24346102
Sorry I forgot, your tables must be open. You can do this in code at the beginning of your button's onclick event by saying
ADOTAble1.Open;
ADOTable2.Open;

Or you can do this at design time - click on the ADOTable1 and 2 and in object inspector, under 'Active', set to True.
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24346116
Now you can also try the SQL way.
This time you put a TADOQuery on your form, and you will still have to build a Connection String like before.
Then try this SQL (you can put it in the SQL property of the ADOQuery in the object inspector, or you can code it like this:
ADOQuery1.SQL.Text := 'ALL MY SQL GOES HERE';

then when you are ready to execute the query, you just say
ADOQUery1.ExecSQL;
INSERT INTO ESTIMATES(FullName, Address, PhoneNumber, ZipCode, VIN, HaleWork, DentWork, Year, Make, Model, Miles) SELECT FullName, Address, PhoneNumber, ZipCode, VIN, HaleWork, DentWork, Year, Make, Model, Miles FROM CUSTOMERINFO;

Open in new window

0
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:Grant Fullen
ID: 24346123
CRAP i forgot to tell you only where the EstimateID in table a matches the EstimateID in table 2.
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24346128
You didn't even mention EstimateID as a field.
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24346139
Tell me, why would your customerinfo table have an EstimateID?
Are you explaining this correctly? So you have only 2 relevant tables here: Estimates and Customerinfo. Both of those tables have an EstimateID field. You want to copy the values of those fields from one table to the other WHERE the EstimateID fields are the same?
0
 
LVL 13

Accepted Solution

by:
rfwoolf earned 500 total points
ID: 24346190
Okay here's the updated SQL statement based on the information you have presented executes correctly in access, but you should double-check)
UPDATE estimates, customerinfo
SET estimates.Fullname = customerinfo.Fullname, 
estimates.Address = customerinfo.Address,
estimates.PhoneNumber = customerinfo.PhoneNumber,
estimates.ZipCode = customerinfo. ZipCode,
estimates.VIN = customerinfo.VIN,
estimates.HaleWork = customerinfo.HaleWork,
estimates.DentWork = customerinfo.DentWork,
estimates.Year = customerinfo.Year,
estimates.Make = customerinfo.Make,
estimates.Model = customerinfo.Model,
estimates.Miles = customerinfo.Miles
WHERE (estimates.EstimateID = customerinfo.EstimateID);

Open in new window

0
 

Author Comment

by:Grant Fullen
ID: 24346202
Sorry I miss the EstimateID field in each table. This is the auto field that connects each estimate to the correct User Info.
Thanks for your help. I will try it now.
0
 

Author Comment

by:Grant Fullen
ID: 24346275
Ok i put the sql in the querys sql text and then i try to set active to true.
I get error.
No value given for one or more required parameters.
UPDATE estimates, customerinfo
SET estimates.Fullname = customerinfo.Fullname, 
estimates.Address = customerinfo.Address,
estimates.PhoneNumber = customerinfo.PhoneNumber,
estimates.ZipCode = customerinfo.ZipCode,
estimates.VIN = customerinfo.VIN,
estimates.HaleWork = customerinfo.HaleWork,
estimates.DentWork = customerinfo.DentWork,
estimates.Year = customerinfo.Year,
estimates.Make = customerinfo.Make,
estimates.Model = customerinfo.Model,
estimates.Miles = customerinfo.Miles
WHERE (estimates.EstimateID = customerinfo.EstimateID);

Open in new window

0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24346287
That's odd. It works for me, but I'm not using Access 2003 - I'm probably using 2000.
Try it inside access and see what happens.
0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24346289
Carefully check all your field names. If it doesn't see something as a field it will think it's a parameter, so check carefully that you have spelled all your column names correctly and that those columns do exist in each table.
You can also try testing it by only doing one field, like this
UPDATE estimates, customerinfo
SET estimates.Fullname = customerinfo.Fullname
WHERE (estimates.EstimateID = customerinfo.EstimateID);

Open in new window

0
 

Author Comment

by:Grant Fullen
ID: 24346290
Thanks i am looking.

0
 

Author Comment

by:Grant Fullen
ID: 24346338
Ok i checked spelling and stuff.

AQCopyInfo: commandtext does not return a result set
UPDATE Estimates, customerinfo
SET Estimates.Fullname = customerinfo.Fullname
WHERE (Estimates.EstimateID =  customerinfo.EstimateID);

Open in new window

0
 
LVL 13

Expert Comment

by:rfwoolf
ID: 24346346
That's correct. You get queries that return a result set, and queries that do not. (If you go back to learning SQL they tell you there is DDL, DML, etc etc). Yours is an update query, so it won't return a result set. Ideally you should be EXECUTING the query, and not OPENING it.
AQCopyINfo.ExecSQL.
0
 

Author Closing Comment

by:Grant Fullen
ID: 31579834
Thanks for your patience. Great Job
0

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Access #Deleted data 20 65
Options for Linking SQL tables to Access 2013 9 69
comparing two rows 10 39
idtcpserver and idtcpclient encryption using ssl 17 10
Entering a date in Microsoft Access can be tricky. A typo can cause month and day to be shuffled, entering the day only causes an error, as does entering, say, day 31 in June. This article shows how an inputmask supported by code can help the user a…
Many companies are looking to get out of the datacenter business and to services like Microsoft Azure to provide Infrastructure as a Service (IaaS) solutions for legacy client server workloads, rather than continuing to make capital investments in h…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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…

732 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