通过Access VBA展开链接的accde数据库中的文本字段 [英] Expand Text Field in Linked accde Database via Access VBA

问题描述

我有一个广泛分布的Access 2010应用程序，它具有拆分的前端和后端模块（都是ACCDE格式）。因此，必须在Access VBA代码中执行对后端数据库的任何更改。在下一个版本中，我需要在链接数据库中将文本字段的大小从6扩展到20。该字段（OppAbbrev）不是关键字段，也不涉及与其他表的任何关系。尝试扩大字段大小时，我在函数SetPropertyDAO中收到错误OppAbbrev.Size not set 20. Error 0 - 。



我的代码使用我成功用于修改过去其他属性的函数SetPropertyDAO和HasProperty（由Alan Browne创建）：

I have a widely distributed Access 2010 application that has split front end and back-end modules (both are ACCDE format). Any changes to the back-end database therefore must be executed within Access VBA code. With the next release I need to expand the size of a text field from 6 to 20 in the linked database. This field (OppAbbrev) is not a key field nor is it involved in any relationships with other tables. I get an error "OppAbbrev.Size not set to 20. Error 0 - " in the function SetPropertyDAO when trying to expand the size of the field.

My code uses the functions SetPropertyDAO and HasProperty (created by Alan Browne) that I have successfully used to modify other properties in the past:

    Set ws = DBEngine.Workspaces(0)
    Set dbs = ws.OpenDatabase(gcstrDatabaseFileSpec, True)
    Set tdf = dbs.TableDefs("Opponent")
    Set fld = tdf.Fields("OppAbbrev")
    Call SetPropertyDAO(fld, "Size", dbText, 20, strErrMsg)
    dbs.Close
    Set dbs = Nothing

The following code is the SetPropertyDAO() function based upon the work of Alan Browne. Note that the HasProperty() function returns true when used in the function. :
Public Function SetPropertyDAO(obj As Object, strPropertyName As String, 
                               intType As Integer, _
                               varValue As Variant, 
                               Optional strErrMsg As String) As Boolean
On Error GoTo ErrHandler
    'Purpose:   Set a property for an object, creating if necessary.
    'Arguments: obj = the object whose property should be set.
    '           strPropertyName = the name of the property to set.
    '           intType = the type of property (needed for creating)
    '           varValue = the value to set this property to.
    '           strErrMsg = string to append any error message to.

    If HasProperty(obj, strPropertyName) Then
        obj.Properties(strPropertyName) = varValue
    Else
        obj.Properties.Append obj.CreateProperty(strPropertyName, intType, varValue)
    End If
    SetPropertyDAO = True

ExitHandler:
    Exit Function
ErrHandler:
    strErrMsg = strErrMsg & obj.Name & "." & strPropertyName & " not set to " & varValue & _
        ". Error " & Err.Number & " - " & Err.Description & vbCrLf
    Resume ExitHandler
End Function

推荐答案

更好地使用查询：

Better use query:

ALTER TABLE Opponent ALTER COLUMN OppAbbrev CHAR(20);

你可以在这里找到更多： http://msdn.microsoft.com/en-us/library/bb177883%28v=office.12%29.aspx [ ^ ]

这篇关于通过Access VBA展开链接的accde数据库中的文本字段的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！