COPY DATA FROM ONE TABLE TO ANOTHER

  • Thread starter Thread starter Pkobo58 via AccessMonster.com
  • Start date Start date
P

Pkobo58 via AccessMonster.com

i'M NEW TO ACCESS AND AM HAVEING PROBLEMS APPENDING DATA FROM ONE TABLE TO
ANOTHER.
I have a table called ALL with the following fields:
LES
DWGNO
DWGTITLE

a SECOND TABLE WITH THE EXACT SAME FIELD NAMES

I NEED TO ADD THE 300 RECORDS IN HTE 2ND TABLE TO THE FIRST.

I TRIED AN APPEND QUERY BUT IT IT ADDS THE 300 RECORDS TO EVERY LES IN HTE
FIRST DATA BASE GIVING ME ABOUT 360,000 RECORDS.

THE HARD WAY WOULD BE TO USE A PROCEDURE LIKE THIS:
Dim conDWG As ADODB.Connection

Set conDWG = Application.CurrentProject.Connection


conDWG.Execute "INSERT INTO ALL(TLES,DWGNO,TWGTITLE}
VALUES("301001-89", "E96423", "'BUSHINGSi')"

conDWG.Close
Set conDWG = Nothing
End Sub

BUT TO DO IT THIS WY i WOULD HAVE TO TYPE IN EACH AND EVERY ENTRY.

HOW DO I READ EACH FIELD AND COPY IT TO THE FIRST TABLE?
 
a simple Append query wouldn't return a cartesian recordset, it would simply
append the 300 records in TableB to TableA. please post the SQL statement of
your Append query.

hth
 
If you can't get the SQL commands to work youi could use recordsets:

Set rstTable1 = CurrentDb.Openrecordset("Table1")
Set rstTable2 = CurrentDb.OpenRecordset("Table2")

With rstTable1
Do Until .EOF
rstTable2.AddNew
rstTable2!Field1 = !Field1
rstTable2!Field2 = !Field2
..... etc ....
rstTable2.Update
.MoveNext
Loop
End With
 
Pkobo58 via AccessMonster.com said:
i'M NEW TO ACCESS AND AM HAVEING PROBLEMS APPENDING DATA FROM ONE
TABLE TO ANOTHER.
I have a table called ALL with the following fields:
LES
DWGNO
DWGTITLE

a SECOND TABLE WITH THE EXACT SAME FIELD NAMES

I NEED TO ADD THE 300 RECORDS IN HTE 2ND TABLE TO THE FIRST.

I TRIED AN APPEND QUERY BUT IT IT ADDS THE 300 RECORDS TO EVERY LES
IN HTE FIRST DATA BASE GIVING ME ABOUT 360,000 RECORDS.

THE HARD WAY WOULD BE TO USE A PROCEDURE LIKE THIS:
Dim conDWG As ADODB.Connection

Set conDWG = Application.CurrentProject.Connection


conDWG.Execute "INSERT INTO ALL(TLES,DWGNO,TWGTITLE}
VALUES("301001-89", "E96423", "'BUSHINGSi')"

conDWG.Close
Set conDWG = Nothing
End Sub

BUT TO DO IT THIS WY i WOULD HAVE TO TYPE IN EACH AND EVERY ENTRY.

HOW DO I READ EACH FIELD AND COPY IT TO THE FIRST TABLE?
Click to expand...

You probably need to fix your keyboard to, see, only capital letters
is considered rude around here, and you don't want that to happen ;)

Basically

INSERT INTO [ALL] (TLES,DWGNO,TWGTITLE}
SELECT TLES, DWGNO, TWGTITLE FROM firstTable

or, if it's identical, I think you should be able to use

INSERT INTO [ALL] SELECT * FROM firstTable

Both of these, could be executed as stored queries, or through the
method you're using.

All, btw, is a reserved word in Jet, here's a list
http://support.microsoft.com/kb/321266/EN-US/
You'll probably get around it using [brackets]
 
Back
Top