Change Name Of Field From Db To Other…

Topic

New Reply

I have in the same dir c:MYDIR A database1.mdb and a database2.mdb

in database1.mdb have a table MASTER in database1.mdb have a table SOURCE
All 2 table have the same number of field
But SOURCE table have a different name of field respect MASTER

Is possible to change the name of all field in SOURCE based the name of field in MASTER????

Example:
MASTER the first field have name UNO change in SOURCE first field name in UNO, MASTER the second field have name DUE change in SOURCE second field name in DUE…ecc

Reply | Quote

Replies

I’d do this manually, and in the future, make sure to use the same field names when you create the tables.

Reply | Quote

I’d do this manually, and in the future, make sure to use the same field names when you create the tables.

sure i can…

but the mdb file is created at every day from other apps

Reply | Quote

I have in the same dir c:MYDIR A database1.mdb and a database2.mdb

in database1.mdb have a table MASTER in database1.mdb have a table SOURCE
All 2 table have the same number of field
But SOURCE table have a different name of field respect MASTER

Is possible to change the name of all field in SOURCE based the name of field in MASTER????

Example:
MASTER the first field have name UNO change in SOURCE first field name in UNO, MASTER the second field have name DUE change in SOURCE second field name in DUE…ecc

You could do it with code like the code below.
Need to put this function into a module in the MASTER DB.

Then run it.

Check that the Database names and paths are correct for your system.

Code:

Function RenameFields()

Dim dbM As DAO.Database, dbS As DAO.Database
Dim tdM As DAO.TableDef, tdS As DAO.TableDef
Dim flM As DAO.Field, strField As String, intField As Integer

Set dbM = CurrentDb
Set dbS = DBEngine(0).OpenDatabase(“c:MYDIRDatabase 2.mdb”)

Set tdM = dbM.TableDefs(“MASTER”)
Set tdS = dbS.TableDefs(“SOURCE”)

For intField = 0 To tdM.Fields.Count – 1
    strField = tdM.Fields(intField).Name
    tdS.Fields(intField).Name = strField
Next

dbM.Close
dbS.Close

Set tdM = Nothing
Set tdf = Nothing
Set dbM = Nothing
Set dbS = Nothing

MsgBox “Table Updated in Database 2”, vbExclamation

End Function

Reply | Quote

You could do it with code like the code below.
Need to put this function into a module in the MASTER DB.

Then run it.

Check that the Database names and paths are correct for your system.

Code:

Function RenameFields()

Dim dbM As DAO.Database, dbS As DAO.Database
Dim tdM As DAO.TableDef, tdS As DAO.TableDef
Dim flM As DAO.Field, strField As String, intField As Integer

Set dbM = CurrentDb
Set dbS = DBEngine(0).OpenDatabase(“c:MYDIRDatabase 2.mdb”)

Set tdM = dbM.TableDefs(“MASTER”)
Set tdS = dbS.TableDefs(“SOURCE”)

For intField = 0 To tdM.Fields.Count – 1
    strField = tdM.Fields(intField).Name
    tdS.Fields(intField).Name = strField
Next

dbM.Close
dbS.Close

Set tdM = Nothing
Set tdf = Nothing
Set dbM = Nothing
Set dbS = Nothing

MsgBox “Table Updated in Database 2”, vbExclamation

End Function

i ma sorry but i use vba for excel

Reply | Quote

You only have to set a reference to the Microsoft DAO 3.6 Library and to change the line

Set dbM = CurrentDb

to

Set dbM = DBEngine(0).OpenDatabase(“c:MYDIRDatabase 1.mdb”)

Reply | Quote

You only have to set a reference to the Microsoft DAO 3.6 Library and to change the line

Set dbM = CurrentDb

to

Set dbM = DBEngine(0).OpenDatabase(“c:MYDIRDatabase 1.mdb”)

hi friend…
but not possible with ADO?
In other case the code with DAO work.

Reply | Quote

You cannot do that with ADO, you’d need ADOX for that, an extension to ADO for security and DDL.

Reply | Quote

You cannot do that with ADO, you’d need ADOX for that, an extension to ADO for security and DDL.

example

Reply | Quote

You have to set a reference in Tools | References to Microsoft ADO Ext. 2.n for DDL and Security.

You can then use code like this:

Code:

Dim cnn1 As ADODB.Connection
Dim cat1 As New ADOX.Catalog
Dim tbl1 As ADOX.Table
Dim cnn2 As ADODB.Connection
Dim cat2 As New ADOX.Catalog
Dim tbl2 As ADOX.Table
Dim i As Integer
Set cnn1 = ... ' connect to database1.mdb
Set cat1.ActiveConnection = cnn1
Set tbl1 = cat1.Tables("MASTER")
Set cnn2 = ... ' connect to database2.mdb
Set cat2.ActiveConnection = cnn2
Set tbl2 = cat2.Tables("SOURCE")
For i = 0 To tbl1.Fields.Count - 1
  tbl2.Fields(i).Name = tbl1.Fields.Name
Next i

Reply | Quote

You have to set a reference in Tools | References to Microsoft ADO Ext. 2.n for DDL and Security.

You can then use code like this:

Code:

Dim cnn1 As ADODB.Connection
Dim cat1 As New ADOX.Catalog
Dim tbl1 As ADOX.Table
Dim cnn2 As ADODB.Connection
Dim cat2 As New ADOX.Catalog
Dim tbl2 As ADOX.Table
Dim i As Integer
Set cnn1 = ... ' connect to database1.mdb
Set cat1.ActiveConnection = cnn1
Set tbl1 = cat1.Tables("MASTER")
Set cnn2 = ... ' connect to database2.mdb
Set cat2.ActiveConnection = cnn2
Set tbl2 = cat2.Tables("SOURCE")
For i = 0 To tbl1.Fields.Count - 1
  tbl2.Fields(i).Name = tbl1.Fields.Name
Next i

Hi!
Based ADOX.catalog…
Admit have this mdb:

“C:APPLICAZIONItracciato_19-25ottobre2009.mdb”…
i use this piece of code but naturally no work!
i want to know the name of tables…

Sub TROVA_NOME_TABELLA1()

Set CAT1 = New ADOX.Catalog
CAT1.ActiveConnection = cnn
For I = 0 To CAT1.Tables.COUNT – 1
If CAT1.Tables(I).Type = “TABLE” Then
NOME_TABELLA = CAT1.Tables(I).Name
Exit For
End If
Next I

End Sub

Reply | Quote

You don’t set cnn in the code that you posted.

BTW it would be easier to use DAO.

Reply | Quote

example

You have solution that works.
Working with Microsoft Access, Microsoft say use DAO not ADO.
Reserve ADO for working with SQL SERVER and other databases.

ADO as Hans says using an ADOX Library requires more work.

You have to use connection strings rather than just open the database.

There is no reason to want to do it here other than academic interest.

But if you want to see some examples

http://support.microsoft.com/kb/303814

Reply | Quote