Karen Schaefer
asked on
Post Err.Number & Message into Memo Field
What is the proper syntax to include the actual Error Msg into a Memo via an Update query.
Here is what I have so far:
strSQL2 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = Err.Number & ' ( '& Err.Description & ')' " & _
" WHERE tblAPList.ApNo= '" & gApNo & "'"
curdb.Execute strSQL2
Getting syntax error msg
Here is what I have so far:
strSQL2 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = Err.Number & ' ( '& Err.Description & ')' " & _
" WHERE tblAPList.ApNo= '" & gApNo & "'"
curdb.Execute strSQL2
Getting syntax error msg
strSQL2 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = " & _
Err.Number & "'" & Err.Description & "'" & _
" WHERE tblAPList.ApNo= '" & gApNo & "'"
curdb.Execute strSQL2
Err.Number & "'" & Err.Description & "'" & _
" WHERE tblAPList.ApNo= '" & gApNo & "'"
curdb.Execute strSQL2
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Here is my code as is - I could use your assistance in cleaning up my code.
To restate the purpose of my code is:
I am attempting to export several types of objects- based on a table contain the list of objects & object Type from 1 database into another - The objects have been import into the ExportTool.mdb, Then repeat this process for a selected list of databases.
My current issues is
1. The object does not exist in the receiving database, how do I modify my code to handle the import/export of this object. ie. table is new to the receiving database I need to add it.
2. Logging error handling - Update a table with the actual error number & message, then allowing for the code to continue on thru the remaining items in the list of databases.
3. Clean my error handling code to allow for moving to the next item in list. I found the code for
4. Compact & compile the receiving database after export has been processed.
Unsure how to implement this code:
To restate the purpose of my code is:
I am attempting to export several types of objects- based on a table contain the list of objects & object Type from 1 database into another - The objects have been import into the ExportTool.mdb, Then repeat this process for a selected list of databases.
My current issues is
1. The object does not exist in the receiving database, how do I modify my code to handle the import/export of this object. ie. table is new to the receiving database I need to add it.
2. Logging error handling - Update a table with the actual error number & message, then allowing for the code to continue on thru the remaining items in the list of databases.
3. Clean my error handling code to allow for moving to the next item in list. I found the code for
4. Compact & compile the receiving database after export has been processed.
Unsure how to implement this code:
' Purpose : Compile newly update external database
'---------------------------------------------------------------------------------------
'Shell """C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE""" & " " & """Path Name of Database""" & " /x mCompile"
Public Function fCompile()
On Error GoTo fCompile_Error
RunCommand acCmdCompileAllModules
DoCmd.Quit
On Error GoTo 0
Exit Function
fCompile_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fCompile of Module modExecuteExport"
End Function
Public Sub ExportDBObjects()
Dim curdb As Database
Dim gApNo As String
Dim rs As Recordset, rs1 As Recordset, rs3 As Recordset
Dim strsql As String, strSQL1 As String, strSQL2 As String, strSQL3 As String, strSQL4 As String
Dim qdf As DAO.QueryDef
Dim nObjName As String, nObjType As Integer
On Error GoTo ExportDBObjects_Error
Set curdb = CurrentDb()
strsql = "Select APNo, ApDbms_Db from tblAPList WHERE Active = Yes"
Set rs = curdb.OpenRecordset(strsql)
rs.MoveFirst
Do Until rs.EOF
gApNo = rs.Fields("apno").Value
Debug.Print gApNo
gAPFilePath = rs.Fields("apdbms_db").Value
'Updates Version 6 and greater databases - previous versions < version 6 will not be updated
' Shell """C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE""" & " " & """ & gAPFilePath & """ & " /x mCompile"
strSQL1 = "SELECT [RevMaj], [ChgMadeBy]" & _
" FROM TS_DB_Revisions IN '" & gAPFilePath & "'" & _
" WHERE ([RevMaj] Like '6')"
Set rs1 = curdb.OpenRecordset(strSQL1)
If rs1.RecordCount > 0 Then
strSQL3 = "Select ObjectName, ObjectType from tblExportList"
Set rs3 = curdb.OpenRecordset(strSQL3)
rs3.MoveFirst
Do Until rs3.EOF
nObjName = rs3.Fields("ObjectName")
nObjType = GetObjectTypeConstant(rs3.Fields("ObjectType"))
'Replaces existing Database objects with revised versions.
DoCmd.TransferDatabase acExport, "Microsoft Access", gAPFilePath, nObjType, nObjName, nObjName
rs3.MoveNext
Loop
End If
strSQL2 = "INSERT INTO TS_DB_Revisions ( RevMaj, RevEnhancement, RevBugFix, Dt, [Desc], ChgMadeBy ) IN '" & gAPFilePath & "'" & _
" SELECT Last(TS_DB_Revisions.RevMaj) AS LastOfRevMaj, Last(TS_DB_Revisions.RevEnhancement) AS LastOfRevEnhancement," & _
" Last(TS_DB_Revisions.RevBugFix) AS LastOfRevBugFix, Last(TS_DB_Revisions.Dt) AS LastDate," & _
" Last(TS_DB_Revisions.[Desc]) AS LastOfDesc, Last(TS_DB_Revisions.ChgMadeBy) AS LastOfChgMadeBy" & _
" FROM TS_DB_Revisions"
curdb.Execute strSQL2
strSQL2 = "UPDATE tblAPList SET tblAPList.Active = No" & _
" WHERE tblAPList.ApNo= '" & gApNo & "'"
curdb.Execute strSQL2
rs.MoveNext
Loop
On Error GoTo 0
Exit Sub
ExportDBObjects_Error:
If Err.Number = 3024 Or Err.Number = 3011 Then
strSQL4 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = " & _
Err.Number & "'" & Err.Description & "'" & _
" WHERE tblAPList.ApNo= '" & gApNo & "'"
curdb.Execute strSQL4
Err.Clear
Resume Next
' ElseIf Err.Number = 3011 Then
' DoCmd.TransferDatabase acExport, "Microsoft Access", gAPFilePath, nObjType, nObjName
'
' Err.Clear
' Resume Next
Else
strSQL4 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = " & _
Err.Number & "'" & Err.Description & "'" & _
" WHERE tblAPList.ApNo= '" & gApNo & "'"
curdb.Execute strSQL4
' MsgBox "Error " & Err.Number & " (" & Err.Description & _
") in procedure ExportDBObjects of Module modExecuteExport"
End If
End Sub
ASKER
Still having issues with my error handling. I want to be able to write to a log all error handling issues, and also if Error happens, then to clear the error and resume the code to continue looping thru the list.
Also how do Transfer database object when the object does not exists in the receiving database?
Also how do Transfer database object when the object does not exists in the receiving database?
ExportDBObjects_Error:
If Err.Number = 3024 Or Err.Number = 3011 Then
strSQL4 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = " & _
Err.Number & "'" & Err.Description & "'" & _
" WHERE tblAPList.ApNo= '" & gApNo & "'"
curdb.Execute strSQL4
Err.Clear
Resume Next
Else
strSQL4 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = " & _
Err.Number & "'" & Err.Description & "'" & _
" WHERE tblAPList.ApNo= '" & gApNo & "'"
curdb.Execute strSQL4
Err.Clear
Resume Next
' End If
End Sub
ASKER
Solutions can be found on https://www.experts-exchange.com/questions/28124674/Concatenation-w-Text-variable-Variable-data-contains-Quotes.html
Use Replace function around the data containing the quotes.
Use Replace function around the data containing the quotes.
" WHERE tblAPList.ApNo= '" & gApNo & "'"