VBA in Office 2016 vs Office 2003

Topic

New Reply

I recently upgraded from Office 2003 to 2016. My record set macros are not compiling. The Edit command is being flagged as an error. What needs to be changed?

Reply | Quote

Replies

You need to check to see which Reference is missing. We might be able to help if you show the code you have and indicate where it is failing.

Reply | Quote

This is the code that is getting an error. It is the .Edit command that is flagged. Attached is a snapshot of the references. None show as missing.

Function fSetMDBName()
‘See Sub fSetCMLogName()
On Error GoTo fSetMDBName_Err
Dim vLogName As Variant
Dim vMDBName As Variant
Dim mydb As Database
Dim rst As Recordset
Dim strTitle As String

gvCMLogName = gvTgtDBName

‘DoCmd.Hourglass True

Set mydb = CurrentDb()
Set rst = mydb.OpenRecordset(“tbl_Sys”)

rst.MoveFirst
rst.Edit
rst![MDB_Name] = gSysData.TgtFileName
rst![Source Path] = gSysData.TgtPath
rst![File Name] = gSysData.TgtFullFileName
rst![FullPathName] = gSysData.TgtDB
rst![TgtName] = gSysData.TgtFileName
rst![Updated] = Now()
rst.Update
vMDBName = rst![MDB_Name]

Debug.Print rst![Updated], rst![TgtName], rst![FullPathName], rst![File Name], rst![Source Path]

‘ rst.Close
DoCmd.Hourglass False

Set rst = Nothing
Set mydb = Nothing

‘ Set mydb = CurrentDb()
‘ Set rst = mydb.OpenRecordset(“tbl_Sys”)
‘ rst.MoveFirst
‘ Debug.Print rst![Updated], rst![TgtName], rst![FullPathName], rst![File Name], rst![Source Path]
‘ rst.Close
‘ Set rst = Nothing
‘ Set mydb = Nothing

gvTgtDBName = vMDBName

strTitle = vMDBName
Call fSetDatabaseTitle(strTitle)

gvMDBName = vMDBName

fSetMDBName = vMDBName

‘9/12/2002
DoCmd.Maximize
strForm = “frm CII_Menu”
‘Call fCloseForm(strForm)
‘DoCmd.OpenForm strForm, acNormal, “”, “”, , acNormal

fSetMDBName_Exit:
Exit Function

fSetMDBName_Err:
MsgBox Error$
Resume fSetMDBName_Exit

End Function

Reply | Quote

Are you sure you have exclusive access to the database table? It sounds like this is a system table and you may not actually be allowed to edit the table via code.

If this code worked in Access 2013 then it might be related to the format of the DB. Is the format upgraded to 2016 or is there a compatibility mode for using a 2013 version in 2016?

Reply | Quote

Andrew, as far as I’m aware system tables start MSys, not tbl. And his database was 2003 not 2013.

Bluegoose, I can’t see any significant difference between your script and my ones (and mine have been converted from 2003 to 2016 via 2013). Are there any error codes or messages? Is it failing to compile or does it fail when run?

Eliminate spare time: start programming PowerShell

Reply | Quote

It failed in compile

Reply | Quote

I’d say your problem is that you haven’t specified the Recordset type and your ADO reference precedes your DAO one. An ADO recordset doesn’t have an Edit method. Declare your rst variable as DAO.Recordset

Reply | Quote

I changed it to DAO recordset and it compiled okay. Thanks.

Reply | Quote

I am having another compile problem with the following code:

Sub sTableAndFieldList()
On Error GoTo E_Handle
Dim strMsg As String
Dim str1 As String
Dim str2 As String

‘ Application.ScreenUpdating = False

Dim obj As AccessObject
Dim obj2 As AccessObject
Dim dbs As Object
Dim db1 As Database
Dim tdf1 As TableDef
Dim idx As Index
Dim fld1 As Field

str1 = “”

Set dbs = Application.CurrentData
Set db1 = CurrentDb

‘ Search for open AccessObject objects in AllTables collection.
For Each tdf1 In db1.TableDefs
‘ If obj.IsLoaded = True Then
‘ ‘ Print name of obj.
‘ Debug.Print obj.Name
‘ End If

str2 = tdf1.Name

If Not Left(str2, 4) = “MSys” Then
str1 = str1 & vbCrLf & tdf1.Name
str1 = str1 & ” — ”
For Each fld1 In tdf1.Fields
str1 = str1 & vbCrLf & ” ” & fld1.Name
str1 = str1 & ” // ” & fld1.Type
str1 = str1 & ” // ” & fld1.Size
Next fld1
End If
Next tdf1
Call ClipBoard_SetText(str1) ‘Access

sExit:
On Error Resume Next
‘ Application.ScreenUpdating = True
DoCmd.Hourglass False

Exit Sub

E_Handle:
Select Case Err.Number
Case 9999999
‘Tell XXX to ignore this error and move on to the next line
Resume Next
Case Else
‘Pop up a message box with the error and exit the Procedure
strMsg = strMsg & vbCrLf & “Error ” & Err.Number & “, ” & Err.Description & vbCrLf & “sTableAndFieldList”
‘ Call ClipBoard_SetText(strMsg) ‘Excel
‘ Call CopyToClipBoard(strMsg) ‘Word
‘ Call ClipBoardCopyTo(strMsg) ‘PowerPoint
Call ClipBoard_SetText(strMsg) ‘Access

MsgBox “Error ” & Err.Number & “, ” & Err.Description & vbCrLf & “sTableAndFieldList”
End Select

Stop
Resume sExit

End Sub

46487-Error

Reply | Quote

Same problem. An ADODB.Field doesn’t have a Size property. You need to declare it as DAO.Field

Reply | Quote

Thank you. That did the trick.

Reply | Quote