I don't know if you can run code in one database to change
the size of a text field in another database when the code
is executed in a runtime version of Access. As you know,
the Access runtime does not allow database modification at
the user interface, but I don't know whether that applies
when the modification is executed in code.
I recommend you try running the code below (or something
like it) on a typical target machine (with only Access
runtime installed) before distributing it. I'd be glad to
hear if it does, or doesn't work.
The database in which the code runs does not require a
linked table to the backend table.
If the database is not installed in the same folder on every
machine, there is a workaround, such as assuming both
databases are in the same folder.
You may like to examine Allen Browne's code examples at:
http://allenbrowne.com/tips.html
Follow the links for:
DDL Query
ModifyFieldDDL()
That code example applies to changing a field's size in the
current database.
The code below opens the backend database - in memory, not
in the user interface - and makes the required change using
DDL (Data Definition Language).
Copy and paste the following into a new blank module of a
new blank database.
Option Compare Database
Option Explicit
' This module requires a reference to
' Microsoft DAO 3.6 Object Library
' (In VBA editor, Tools > References).
Public Sub ChangeZipFieldSize()
' Purpose:
' To change the size of the Zip field
' in the backend database from 10
' characters to 11 characters.
Const strcBackendDBFullPath As String = _
"C:\My Documents\MyDatabaseName.mdb"
Const strcTableName As String = "MyTableName"
Const strcFieldName As String = "MyFieldName"
Const strcNewSize As String = "11"
Call ChangeZipField( _
strcBackendDBFullPath, _
strcTableName, _
strcFieldName, _
strcNewSize)
End Sub
Private Sub ChangeZipField( _
strBackendDBFullPath As String, _
strTableName As String, _
strFieldName As String, _
strNewSize As String)
Dim objWS As DAO.Workspace
Dim objDB As DAO.Database
Dim objTBL As DAO.TableDef
Dim objFLD As DAO.Field
Dim strSQL As String
' Initialize the SQL statement that will
' alter the field's size:
strSQL = "ALTER TABLE " & strTableName _
& " ALTER COLUMN " & strFieldName _
& " TEXT(" & strNewSize & ");"
' Open the database and point to the field
' that needs its size changed:
Set objWS = DBEngine.Workspaces(0)
Set objDB = objWS.OpenDatabase(strBackendDBFullPath)
Set objTBL = objDB.TableDefs(strTableName)
Set objFLD = objTBL.Fields(strFieldName)
' Ensure the field is a text field and,
' if it is, change its size:
If Not objFLD.Type = dbText Then
Call Msg1(strBackendDBFullPath, _
strTableName, strFieldName)
GoTo Exit_ChangeZipField
Else
objDB.Execute strSQL, dbFailOnError
Call Msg2(strBackendDBFullPath, _
strTableName, strFieldName, strNewSize)
GoTo Exit_ChangeZipField
End If
Exit_ChangeZipField:
On Error GoTo Abort_CleanUp_ChangeZipField
' Destroy DAO objects:
Set objFLD = Nothing
Set objTBL = Nothing
If Not objDB Is Nothing Then
objDB.Close
Set objDB = Nothing
End If
Set objWS = Nothing
Exit Sub
Abort_CleanUp_ChangeZipField:
Exit Sub
Error_ChangeZipField:
MsgBox "Error No: " & Err.Number _
& vbNewLine _
& "Description:" & vbNewLine _
& Err.Description, _
vbExclamation + vbOKOnly, _
"Error Information"
Resume Exit_ChangeZipField
End Sub
Private Sub Msg1(strDbName As String, _
strTableName As String, _
strFieldName As String)
MsgBox "Database:" & vbNewLine _
& strDbName & vbNewLine & vbNewLine _
& "Table: " & strTableName & vbNewLine _
& "Field: " & strFieldName _
& vbNewLine & vbNewLine _
& "The above field is not a Text field. " _
& "Therefore, the field's size " _
& "was not changed.", _
vbInformation + vbOKOnly, _
"Information"
End Sub
Private Sub Msg2(strDbName As String, _
strTableName As String, _
strFieldName As String, _
strNewSize As String)
MsgBox "Database:" & vbNewLine _
& strDbName & vbNewLine & vbNewLine _
& "Table:" & vbTab & strTableName _
& vbNewLine _
& "Field:" & vbTab & strFieldName _
& vbNewLine _
& "New Size:" & vbTab & strNewSize _
& vbNewLine & vbNewLine _
& "The size of the above text field " _
& "has been changed.", _
vbInformation + vbOKOnly, _
"Information"
End Sub
Geoff.
> snipped.
>
> The error message is as follows: "Error # 3280 ... Cannot
> delete a field that is part of an index or is needed by
> the system."
>
> I checked, and the only field in that table that has a
> relationship is the primary key field (which is an
> autonumber). Besides the primary key, there are 7 or 8
> other fields (all text). This table is used to hold
> addresses. The field I'm trying to play with is ZIP code.
>
> Here's the environment.
>
> The database from which I'm trying to delete the field is
> in Access 2000 format. It's used as the back-end database
> of a front-end / back-end setup.
>
> I need to expand the field (text) from 10 to 11 characters
> (the application is used in many countries, and I just got
> my first user in Kenya ... which I discovered has an
> 11-digit postal code that needs to be stored in this
> field). So I sent the user a little mdb written in Access
> 2003 that connects to the back-end and which will delete
> that field and redefine it as 11 characters. The "delete"
> is what's failing. [p.s. if I could just code something
> to expand the length of the ZIP code field in place
> without having to delete it and re-define it, that would
> be far more excellent.]
>
> Anyway, the computer on which it's running (the user's
> computer) has Access 2007 Runtime installed (the latest
> version that MS updated, in April 2009 if I recall).
>
> bob
Similar Threads
