Copy field to new Database 2

[kimtp]

Am trying to copy the contents of a field from one table to another. Have added the new field to the copyto table with

ALTER Table Members ADD Column Deceased LOGICAL

but am stuck when trying to copy the contents from one table to the new one.

 

[ArtieChoke]

Are you trying to use VB to do this?

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[kimtp]

yes. thanx.

 

[ArtieChoke]

You'll need to use ADO to access the database. You can use an insert into ... select from construct in the execute command. Search the forums for some example code.

"I think we're all Bozos on this bus!" - Firesign Theatre

 

[fredericofonseca]

On this case it is not the insert but the update statement that should be used, as a new field is being added.


something like

update tbl_old set f1.new_field = f2.new_field
from tbl_old f1, tbl_new f2
where f1.key_fields = f2.keyfields

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[kimtp]

Thanx for the replies.

The two tables are Members and Attending. I want to copy Field Deceased from Attending to Members. If Attending is the old table then:

sSql = "UPDATE Attending SET Attending.Deceased = Members.Deceased" & _
           " FROM Attending.Deceased, Members.Deceased WHERE Attending.ID = Members.ID"

Which gives a syntax error, missing operator. Appreciate any help.

Kim

 

[gmmastros]

As I am reading through your last post, I'm a little confused. Originally, attending has the data. You want to copy that data to the Members table. If this is correct, then....

sSql = "UPDATE [red]Members[/red] SET [red]Members[/red].Deceased = [red]Attending{/red].Deceased" & _
           " FROM Members Inner Join Attending On Members.Id = Attending.ID"

The reason your sql was failing was because you had the table names AND FIELD NAMES in the from clause. You should only have had the table names.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Stupid tags! Here's a better version (with the tags corrected.

sSql = "UPDATE Members SET Members.Deceased = Attending.Deceased" & _
           " FROM Members Inner Join Attending On Members.Id = Attending.ID"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kimtp]

Thanks for the help. I'm still getting the error msg:
Syntax Error (Missing Operator) in query expression 'Attending.Deceased FROM Members Inner Join Attending On Members.Id = Attending.ID'

Thanks.

Kim

 

[PCLewis]

What is the database you are working with? For example, you cannot so an update statement like that one in Oracle because you may not use joins like that.

IN Oracle you would use:

UPDATE Members SET Members.Deceased = 
    (SELECT Attending.Deceased 
       FROM Members, Attending 
       WHERE Members.Id = Attending.ID)

 

[kimtp]

I am working with Access. Your statement does not open both tables in VB.

Any other ideas would be great.

Thanx.

Kim

 

[gmmastros]

Ah... Access. My sql would have worked with SQL Server. In Access, it's structured a little different. Try this...

sSQL ="Update Members Inner Join Attending On Members.Id = Attending.Id " & _
"set Members.Deceased = Attending.Deceased"

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kimtp]

Sorry I wasn't more inclusive with the info. Seems that the latest is a step closer. However, the msg now is 'No value given for one or more required parameters.'

Thanx.

 

[fredericofonseca]

Do you have the new field already defined on the old table?
And are the table and field names exactly as gmmastros posted?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[gmmastros]

I recommend that you check and double check that the table names and field names are spelled correctly. It would seem that Access thinks that there is a parameter to the query if the tables and fields do not match.

I am reasonably sure that the sql will work because I created tables and fields in an Access database to test the sql before posting it.

When I run the query in Access, I get a message... "You are about to update 0 Row(s). Once you click Yes, you can't use the Undo comand to reverse the changes. Are you sure you want to update these records? Y/N"


-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[kimtp]

Thanx to all. Had a minor glitch. Must have been the midnight fairies. Works like a charm now. I fired the fairies.

Kim