Selasa, 19 April 2016

Re: [MS_AccessPros] Help changing field type on linked table and field format property



You need to open the back end database directly, then execute against that:

Dim dbBack As DAO.Database

    Set dbBack = DBEngine(0).OpenDatabase(strBEFullName)

.. then execute your SQL:

    dbBack.Execute "ALTER TABLE " & "[" & TableName & "]" ALTER COLUMN [" & f.Name & "] DateTime;", dbFailOnError

BUT that won't run unless you can get exclusive control of the table, and that won't happen because you have a recordset open on it.  It might work if you run through all the fields and load the field names you want to change into an array, then close the recordset and then process each name you found from the array.  Note that you can also rumble through the field names by opening a TableDef object on the linked table and loop through the Fields collection, but you must be sure to set the TableDef object to Nothing before attempting your ALTER commands.

John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications 
SQL Queries for Mere Mortals 
(Paris, France)

On Apr 19, 2016, at 5:29 AM, [MS_Access_Professionals] <> wrote:

I am trying to alter a table field in a link table.  The subroutine being used is
as follows:

Private Sub FieldNames(TableName As String)
Dim Rst As Recordset
Dim f As Field
Dim strBEFullname As String
Dim strDDLSOL As String

Set Rst = CurrentDb.OpenRecordset(TableName)

For Each f In Rst.Fields
If (InStr(f.Name, "Date") > 0) Then
   strBEFullname = "C:\Audit Tracking\AuditTracking_5.15\AuditTracking_be.accdb"
   strDDLSQL = "ALTER TABLE [" & strBEFullname & "].["& TableName & "] ALTER Column [" & f.Name & "] DateTime;"
   MsgBox strDDLSQL
   CurrentDb.Execute strDDLSQL, dbFailOnError
   '   Call SetFieldProperty(f, "Format", dbText, "Short Date")
End If
End Sub

The message box displayes:

ALTER TABLE [C:Audit Tracking\AuditTracking_5.15\AuditTracking_be.accdb].[New_License] ALTER Column [Run Date] DateTime;

Not sure of the issue.  Receive the following error:

Error 3371: Cannot find table or constraint

Also want to add a change in field property; i.e. change from test to short date.  The table exist and so does the field.

Using Access 2010.

Any suggestions would be greatly appreciated.  


Posted by: John Viescas <>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)



Tidak ada komentar:

Poskan Komentar