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

    [vba]

    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
    [/vba]

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

    [vba]

    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
    [/vba]
    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

    [vba]

    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

    [/vba]

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

    [vba]

    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

    [/vba]
    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 10:46 AM.

  6. #6
    To get list of tables

    [vba]

    Dim DB_Table As DAO.TableDef

    For Each DB_Table In DB.TableDefs
    MsgBox DB_Table.Name
    Next

    [/vba]

    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

    [vba]
    Dim DB_Table As DAO.TableDef

    For Each DB_Table In DB.TableDefs
    MsgBox DB_Table.Name
    Next
    [/vba]
    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
    352
    Quote Originally Posted by mohanvijay
    To get list of tables

    [vba]

    Dim DB_Table As DAO.TableDef

    For Each DB_Table In DB.TableDefs
    MsgBox DB_Table.Name
    Next

    [/vba]
    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
    [VBA]Dim myrs as new adodb.recordset[/VBA]
    NOT
    [VBA]dim myrs as new recordset[/VBA]

    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
  •