Excel Hints

Results 1 to 8 of 8

Thread: Copying Caption to Description

  1. #1

    Copying Caption to Description

    Hello.
    Iīm creating a VB app. to manipulate an Access database. The database has no form and no user interface. This is done by VB app.
    I need to get the field/column Captions from each table defined in database but they are not available to ADO/ADOX.
    One solution is to copy them to Description property since this one is available to ADO/ADOX. The problem is how to do it. Also, it needs to be done everytime the database is opened by VB app. (other developers here may change the captions and forget to warn).
    I think some VBA code will solve this, but I donīt have any idea how to do it.
    Please, can you help?
    Thanks.

  2. #2
    Try this to get column details in Access

    VB:
     
    Dim DB As ADODB.Connection 
    Dim RS As ADODB.Recordset 
    Set DB = New ADODB.Connection 
    Set RS = New ADODB.Recordset 
    DB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your access database;" 
    Set RS = DB.OpenSchema(adSchemaColumns) 
     
     ' RS contains details of columns
     
    RS.Close 
    Set RS = Nothing 
    DB.Close 
    Set DB = Nothing 
    
    
    Formatting tags added by mark007

  3. #3
    Quote Originally Posted by mohanvijay
    Try this to get column details in Access

    VB:
     
    Dim DB As ADODB.Connection 
    Dim RS As ADODB.Recordset 
    Set DB = New ADODB.Connection 
    Set RS = New ADODB.Recordset 
    DB.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=your access database;" 
    Set RS = DB.OpenSchema(adSchemaColumns) 
     
     ' RS contains details of columns
     
    RS.Close 
    Set RS = Nothing 
    DB.Close 
    Set DB = Nothing 
    
    
    Formatting tags added by mark007
    Sorry, this code is not a solution to this problem. Caption property is not available to Jet. If so I could get it uising my VB app. Only Description is available. Thatīs why I need to copy Captions to Descriptions. Any idea?

  4. #4
    Use DAO instead of ADO

    VB:
     
    Dim DE As DAO.DBEngine 
    Dim DB As DAO.Database 
    Dim DB_Table As DAO.TableDef 
    Dim Col_Caption As Field 
    Dim T_Str As String 
    Dim T_Str As String 
    Set DE = New DAO.DBEngine 
    Set DB = DE.OpenDatabase("F:\Finaln\ff.mdb") 'change to your path
    Set DB_Table = DB.TableDefs("data_led") ' set table name
     
    On Error Resume Next 
     
    For Each Col_Caption In DB_Table.Fields 
        T_Str = Col_Caption.Properties("Caption").Value 'get caption vale
    Next 
     
    On Error Goto 0 
     
    Set Col_Caption = Nothing 
    Set DB_Table = Nothing 
    DB.Close 
    Set DB = Nothing 
    Set DE = Nothing 
    
    
    Formatting tags added by mark007

  5. #5
    Quote Originally Posted by mohanvijay
    Use DAO instead of ADO

    VB:
     
    Dim DE As DAO.DBEngine 
    Dim DB As DAO.Database 
    Dim DB_Table As DAO.TableDef 
    Dim Col_Caption As Field 
    Dim T_Str As String 
    Dim T_Str As String 
    Set DE = New DAO.DBEngine 
    Set DB = DE.OpenDatabase("F:\Finaln\ff.mdb") 'change to your path
    Set DB_Table = DB.TableDefs("data_led") ' set table name
     
    On Error Resume Next 
     
    For Each Col_Caption In DB_Table.Fields 
        T_Str = Col_Caption.Properties("Caption").Value 'get caption vale
    Next 
     
    On Error Goto 0 
     
    Set Col_Caption = Nothing 
    Set DB_Table = Nothing 
    DB.Close 
    Set DB = Nothing 
    Set DE = Nothing 
    
    
    Formatting tags added by mark007
    Perfect!!!
    I canīt believe this is not available in ADO/ADOX.

    Do you know how to get a list of tables using DAO?
    One more question: is there any problem using DAO and ADO/ADOX in the same app in VB?
    Thanks.
    Last edited by jalexm; 03-07-2012 at 09:46 AM.

  6. #6
    To get list of tables

    VB:
     
    Dim DB_Table As DAO.TableDef 
     
    For Each DB_Table In DB.TableDefs 
        MsgBox DB_Table.Name 
    Next 
    
    
    Formatting tags added by mark007
    i think no problem comes if using DAO - ADO - ADOX on same application

  7. #7
    Quote Originally Posted by mohanvijay
    To get list of tables

    VB:
    Dim DB_Table As DAO.TableDef 
     
    For Each DB_Table In DB.TableDefs 
        MsgBox DB_Table.Name 
    Next 
    
    
    Formatting tags added by mark007
    Great!!!
    Last question: in the code above, how to get the type (VIEW, TABLE, etc.) of a table? Is this possible using DAO?
    Many thanks.

  8. #8
    VBAX Mentor Movian's Avatar
    Joined
    Aug 2008
    Location
    Virginia, In a chair... a comfy chair infront of my desk
    Posts
    345
    Quote Originally Posted by mohanvijay
    To get list of tables

    VB:
     
    Dim DB_Table As DAO.TableDef 
     
    For Each DB_Table In DB.TableDefs 
        MsgBox DB_Table.Name 
    Next 
    
    
    Formatting tags added by mark007
    i think no problem comes if using DAO - ADO - ADOX on same application
    I would just like to mention that if you DO use ADO and DAO in the same project its very important to fully qualify everything.

    eg
    VB:
    Dim myrs As New adodb.recordset 
    
    
    Formatting tags added by mark007
    NOT
    VB:
    Dim myrs As New recordset 
    
    
    Formatting tags added by mark007
    As this will cause problems! Aside from that as far as i know there are no other problems.
    "From the ashes of disaster grow the roses of success" - Chitty chitty bang bang

    "I fear not the man who has 10,000 kicks practiced once. I fear the man who has 1 kick practiced 10,000 times" - Bruce Lee

Posting Permissions

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