Rod-
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, desertscroller@cox.net [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> 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
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
__._,_.___
Posted by: John Viescas <johnv@msn.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (2) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar