Results 1 to 13 of 13
  1. #1
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd do this manually, and in the future, make sure to use the same field names when you create the tables.

  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by sal21 View Post
    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:\MYDIR\Database 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
    Andrew

  4. #4
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    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

  5. #5
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by AndrewKKWalker View Post
    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:\MYDIR\Database 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

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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:\MYDIR\Database 1.mdb")


  7. #7
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    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:\MYDIR\Database 1.mdb")
    hi friend...
    but not possible with ADO?
    In other case the code with DAO work.

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You cannot do that with ADO, you'd need ADOX for that, an extension to ADO for security and DDL.


  9. #9
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    You cannot do that with ADO, you'd need ADOX for that, an extension to ADO for security and DDL.
    example

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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

  11. #11
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Quote Originally Posted by sal21 View Post
    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
    Andrew

  12. #12
    Gold Lounger
    Join Date
    Jan 2004
    Location
    Italy
    Posts
    3,245
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by HansV View Post
    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:\APPLICAZIONI\tracciato_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

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    You don't set cnn in the code that you posted.

    BTW it would be easier to use DAO.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •