Access 2000

[Lou Civitella]

Using ADO how can I copy a column from one table into another?

What I have created is an Access 2000 utility that updates my backend
database from another 'master' database. The utility opens the 'master'
database and compares the tables, columns and columns properties to the same
in my backend database. If anything is different (new table, new columns,
changed property) the utility fixes the backend to make it have the same
structure as the 'master' database. I was just wondering if there was a way
to copy a column from one table to another without having to add the column
and the go through all the properties and update them based on the 'master'.

Thanks In Advnace,
Lou

 

[Ron Weiner]

If the tables are Jet tables (native access) or Sql Server You could write
some DDL scripts to Add, Drop, or Alter tables that need to change. For
instance:

docmd.RunSQL "Alter Table TableNameAdd Column NewColumnName Char(50)"

would add a new column "NewColumnName" to an existing table "TableName".

There is a little more information in Access help. Search on "alter table"
in the answer wizzard.

Ron W

 

[Steven Parsons [MSFT]]

Hi Lou -

This is Steven from Microsoft Access Technical Support replying to your
newsgroup post.

You can do the same using ADO, but in two steps. First add the new field to
the backend table, then enter the data. See example code:

Sub AlterAdd()
'''''''''''''''''''''''''''''''''''''''''''''
'Assumes tables are in same current database.
'''''''''''''''''''''''''''''''''''''''''''''
Dim ADOCon As New ADODB.Connection
Dim ADOrst1 As New ADODB.Recordset
Dim ADOrst2 As New ADODB.Recordset

Set ADOCon = CurrentProject.Connection

'Assumes a frontend table called tblFront and
'that it has a field called Added_Fld.
ADOrst1.Open "tblFront", ADOCon, adOpenDynamic, adLockOptimistic

'Assumes you have an existing backend table called tblBack
'Assumes you want to add a new field called New_Fld
ADOCon.Execute "ALTER TABLE tblBack ADD New_Fld Text"

'Populate New_Fld
ADOrst2.Open "tblBack", ADOCon, adOpenDynamic, adLockOptimistic
ADOrst1.MoveFirst

Do While Not ADOrst1.EOF
ADOrst2.AddNew
ADOrst2("New_Fld").Value = ADOrst1("Added_Fld").Value
ADOrst2.Update
ADOrst1.MoveNext
Loop

ADOrst2.Close
ADOrst1.Close
ADOCon.Close

Set ADOrst2 = Nothing
Set ADOrst1 = Nothing
Set ADOCon = Nothing
End Sub

Please let me know if this solves your problem or if you would like further
assistance. I look forward to hearing from you.

Sincerely,
Steven Parsons [MSFT]
Microsoft Access Product Support Services
This posting is provided "AS IS" with no warranties, and confers no rights.
Get Secure! (http://www.microsoft.com/security)

 

[¹Ú¹®¼ö]

Using ADO how can I copy a column from one table into another?

What I have created is an Access 2000 utility that updates my backend
database from another 'master' database. The utility opens the 'master'
database and compares the tables, columns and columns properties to the same
in my backend database. If anything is different (new table, new columns,
changed property) the utility fixes the backend to make it have the same
structure as the 'master' database. I was just wondering if there was a way
to copy a column from one table to another without having to add the column
and the go through all the properties and update them based on the 'master'.

Thanks In Advnace,
Lou

 

[¹Ú¹®¼ö]

Using ADO how can I copy a column from one table into another?

What I have created is an Access 2000 utility that updates my backend
database from another 'master' database. The utility opens the 'master'
database and compares the tables, columns and columns properties to the same
in my backend database. If anything is different (new table, new columns,
changed property) the utility fixes the backend to make it have the same
structure as the 'master' database. I was just wondering if there was a way
to copy a column from one table to another without having to add the column
and the go through all the properties and update them based on the 'master'.

Thanks In Advnace,
Lou