Copying Caption to Description

[jalexm]

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.

[mohanvijay]

Try this to get column details in Access

 
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 tags courtesy of www.thecodenet.com

[jalexm]

Quote:   Originally Posted by: mohanvijay Try this to get column details in Access 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 tags courtesy of www.thecodenet.com

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?

[mohanvijay]

Use DAO instead of ADO

 
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 tags courtesy of www.thecodenet.com

[jalexm]

Quote:   Originally Posted by: mohanvijay Use DAO instead of ADO 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 tags courtesy of www.thecodenet.com

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.

[mohanvijay]

To get list of tables

 
Dim DB_Table As DAO.TableDef 
 
For Each DB_Table In DB.TableDefs 
    MsgBox DB_Table.Name 
Next 
 

VBA tags courtesy of www.thecodenet.com

i think no problem comes if using DAO - ADO - ADOX on same application

[jalexm]

Quote:   Originally Posted by: mohanvijay To get list of tables Dim DB_Table As DAO.TableDef For Each DB_Table In DB.TableDefs MsgBox DB_Table.Name Next VBA tags courtesy of www.thecodenet.com

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.

[Movian]

Quote:   Originally Posted by: mohanvijay To get list of tables Dim DB_Table As DAO.TableDef For Each DB_Table In DB.TableDefs MsgBox DB_Table.Name Next VBA tags courtesy of www.thecodenet.com 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

Dim myrs As New adodb.recordset 

VBA tags courtesy of www.thecodenet.com

NOT

Dim myrs As New recordset 

VBA tags courtesy of www.thecodenet.com

As this will cause problems! Aside from that as far as i know there are no other problems.