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

Senin, 18 April 2016

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

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
Next
Rst.Close
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.
Rod