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
