View Full Version : Copying Caption to Description
jalexm
03-07-2012, 04:58 AM
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
03-07-2012, 06:20 AM
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
jalexm
03-07-2012, 06:27 AM
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
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
03-07-2012, 08:05 AM
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
jalexm
03-07-2012, 10:29 AM
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
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
03-07-2012, 08:48 PM
To get list of tables
Dim DB_Table As DAO.TableDef
For Each DB_Table In DB.TableDefs
MsgBox DB_Table.Name
Next
i think no problem comes if using DAO - ADO - ADOX on same application
jalexm
03-08-2012, 08:02 AM
To get list of tables
Dim DB_Table As DAO.TableDef
For Each DB_Table In DB.TableDefs
MsgBox DB_Table.Name
Next
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
03-08-2012, 08:36 AM
To get list of tables
Dim DB_Table As DAO.TableDef
For Each DB_Table In DB.TableDefs
MsgBox DB_Table.Name
Next
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
NOT
dim myrs as new recordset
As this will cause problems! Aside from that as far as i know there are no other problems.
Powered by vBulletin® Version 4.2.5 Copyright © 2025 vBulletin Solutions Inc. All rights reserved.