Changing the Description of a DAO (1 Viewer)

[aziz rasul]

I want to change the description of a table to take on the value of the number of records in that table using VBA. Is this possible?

 

[Drevlin]

Ok I hacked and slashed this code from the help files of access.

Help index keyword = CreateProperty -> Example

It will now do exactly what your asking. (for a single table, you can change it to work with more then one)

Sub ChangeDescr()

Dim dbs As DAO.Database
Dim tblDef As DAO.TableDef
Dim prpLoop As DAO.Property
Dim prpLoop1 As DAO.Property
Dim strRecordCount As String

Set dbs = CurrentDb
Set tblDef = dbs("YourTableName")

'Create your Description with record count of table
strRecordCount = "Records in Table: " & tblDef._
Properties("RecordCount").Value
' Set the Description property.
SetProperty tblDef, "Description", strRecordCount



End Sub

Sub SetProperty(dbsTemp As DAO.TableDef, strName As String, _
strTemp As String)

Dim prpNew As DAO.Property
Dim errLoop As Error

' Attempt to set the specified property.
On Error GoTo Err_Property
dbsTemp.Properties(strName) = strTemp
On Error GoTo 0

Exit Sub

Err_Property:

' Error 3270 means that the property was not found.
If DBEngine.Errors(0).Number = 3270 Then
' Create property, set its value, and append it to the
' Properties collection.
Set prpNew = dbsTemp.CreateProperty(strName, _
dbText, strTemp)
dbsTemp.Properties.Append prpNew
Resume Next
Else
' If different error has occurred, display message.
For Each errLoop In DBEngine.Errors
MsgBox "Error number: " & errLoop.Number & vbCr & _
errLoop.Description
Next errLoop
End
End If

End Sub


Enjoy.

 

[aziz rasul]

Many thanks.