From what I've found with the test BE - other than getting an error message when you try to run the query b/c the table field already exists, nothing bad happens. Pat's file says "Something went wrong - restore from the backup and try again." - Probably good advice, but with the test BE, I've often just fixed the query and re-applied it with no issues.
Since they are new fields, with the current version of the FE, nothing. With the pending version of the FE, there would be errors and I would have to figure out which query didn't run and get exclusive access and run it again - hence the desire for error reporting (which it has).
If it works for you without any problems, that's good.
You probably understood what I meant overall.
Maybe I'm too deep into processes that are practical for me.
At this point, I create a script using the “first check then act” method, which is always cumulatively supplemented with further actions. Structural changes to the test backend are carried out exclusively via code and are incorporated directly into this script. This means everything has been tested and can be repeated as desired.
- I can send such a script via email to a distant and relatively inexperienced user and have them execute it. In case of doubt, the user will not be able to access a backup copy independently. The script is sometimes integrated into an installation file (Inno Setup & Co.).
- Such a script, supplemented by dates, also serves as documentation of what exactly was done and when. Errors and problems that occur for unknown reasons are often associated with an update. The developer also needs some self-protection.
Is there a command to open a report via VBA - currently I've used the docmd acViewPreview but this doesn't really solve the problem as the print preview does not give you a very good view of the re...
What happens if the measure is carried out again?
Are you 100 percent aware of the exact status of the working backend?
What happens if an error occurs in the middle of executing the measure and only some of the DDL queries were executed?
I do believe that I mentioned that I personally supervise running the update when it is ACE. If I left it to the user, it would need more error checking. When the BE is SQL Server, the DBA runs the script. Newer versions of the process do include version checks. The point of the conversion is to take the users version of the BE and transform it to the current version. No data is EVER deleted. Not columns, not rows.
@jdraw - Should have replied earlier, but I'm having problems with the load Data Macros routine from Reply #113. (Didn't test till now - didn't save until now either, but save seems to work fine.):
I'm using this:
Code:
Sub Test()
Call DataMacroFiles
End Sub
I changed StrFolder to Optional and hard coded the location to my folder, i.e.:
Code:
Function DataMacroFiles(Optional strFolder As String)
' strFolder = "C:\Users\jp\Documents\" 'used for testing
strFolder = "C:\Users\<MyUserName>\Documents\Access Database Development\DataMacrosTextFiles\"
This code:
Code:
Dim fol As folder 'folder
Dim fil As file 'file
Dim fso As filesystemobject
gave me "User Defined Type Not Defined"
I changed it to:
Code:
Dim fol As Object 'folder
Dim fil As Object 'file
Dim fso As Object
This code:
Code:
'30 Set fso = New FileSystemObject
Set fso = CreateObject("Scripting.FileSystemObject")
Commented line gave me "FileSystemObject" not defined, so I changed it to the line below.
After that, it runs fine and says all table macros were imported, but if I look at the table in design view, I don't see any data macros.
Do you have a reference set for Microsoft Scripting Runtime?? It is required to use FSO.
That routine was looking at text files and also data macros that had been saved as text.
You probably don't need all that logic.
Data macros are associated with tables. When you save to text those data macros associated with Table1, then all the data macros for table1 are saved in the same xml/text file. And all will be loaded from said text file to Table1.
To see what data macros you have in your current database use this sql
Code:
SELECT [Name] FROM MSysObjects
WHERE Len(LvExtra)>32 and Type =1
Still didn't seem to work. I changed everything back and it compiled without errors, and it still says everything was imported, but when I open the table in design view, I still don't see any of the data macros that it was supposed to add ???
I didn't see the SQL in 146 - I think you added it while I was testing. I tried creating a query with it and nothing shows up - which is what I was seeing from the tables in design view. There don't seem to be any DM's, although it says there should be.
I'm in a meeting at the moment, but will check back later. As I said in #146 you may not need all that logic, depending on what you are doing. If you are just loading from text, you don't necessarily need to be checking existing text files etc.
Did you save the DM to text from the testdb?
Can you send me a copy of your copy? So I can see what's going on?
Do you get an error msg and line reference?
I have been away from this for a week and have different db's now.
???Did a quick check. I tried using the front end Database and there are no tables matching the saved data macros.
I need to make sure the tabledefs are in the database being tested. I split my copy of your original test data base, and have not adjusted the code. Will set something up and get back.
No, this is a copy of the production BE. It's not that I don't trust you, but I'd get in trouble with management if I sent it. Even if I deleted all the records first, it still gives the field names and the processes used. If I changed ALL of those, it would probably be okay, but we wouldn't know if that made it work and that caused the problem.
What I can do is try to find the test database I uploaded earlier and see how it works with that and let you know (Reply #69).
@jdraw - Please look at the attached. I took my database from #69 and re-named it as DM_Test_Source.
I made a copy of it and named it DM_Test_Target. I deleted the DM's from DM_Test_Source and imported the VBA from my current test BE. Then I imported the VBA to DM_Test_Target.
I opened DM_Test_Source and ran the save routine and got macrosFor_tblsource.xml - which looks correct to me.
Then I opened DM_Test_Target and ran the loading subroutine and got the message above, but if I try to rename a DM, I get:
I opened both databases and deleted my username in three places, but otherwise they aren't changed.
Marshall, I see you have posted while I was testing and posting.
I have not looked at your latest test database yet--but will shortly.
I did the following before seeing your latest post.
I just did a test using a front end with no user tables. I think this set of routines has the message in correct sot and also that there is now a check to ensure:
-the tables with saved data macros are listed
-there are user tabledefs in the current database
-if there are no user tables, the routine exists with a message as below -if there is at least 1 user table, then an attempt to load data macros associated with tables occurs
- a message saying macros were loaded should occur if they were loaded.
DataMacroFiles "C:\Users\JP\Documents\" ' This is invoking the routine
'below are the tables that have has Data macros saved as text
tabTest macrosFor_tabTest.xml
tblAuditLog macrosFor_tblAuditLog.xml
tblSource macrosFor_tblSource.xml
tCourse macrosFor_tCourse.xml
tCourseContent macrosFor_tCourseContent.xml
tStudent macrosFor_tStudent.xml
tStudentSession macrosFor_tStudentSession.xml
tStudentTestResults macrosFor_tStudentTestResults.xml
tSubject macrosFor_tSubject.xml
'this is the new check to ensure there are nonSystem table(s) in the database being tested. No user tables exist in this database: C:\Users\JP\Documents\DMUtilApp.accdb
===============Here is the revised code ================================
Code:
' ----------------------------------------------------------------
' Procedure Name: DataMacroFiles
' Purpose: Routine to review xml files in a specified folder where datamacros were SaveAsText.
' Filter filenames for "macrosFor_".
' Compare tables identified in xml files with tables in this database. Where names match
' Load the dataMacro(s) from text to the associated table.
' Procedure Kind: Function
' Procedure Access: Public
' Parameter strFolder (String): Fullname of folder to be reviewed
' Author: Jack
' Date: 11-Jan-24
'
' Calls:
' LoadaDataMacro
' CheckForNonSystemTables<------new 30-Jan-24
'
' Requires:
' Reference to Microsoft Scripting Runtime
'
'Sample execution in immediate window
' DataMacroFiles "C:\Users\JP\Documents\"
'
'Revision:30-Jan-24 added
' CheckForNonSystemTables -to ensure there were user tables in the database
' ----------------------------------------------------------------
Function DataMacroFiles(strFolder As String)
10 On Error GoTo DataMacroFiles_Error
' strFolder = "C:\Users\jp\Documents\" 'used for testing
Dim filt As String
20 filt = "macrosFor_" 'unique characters within filenames from SaveAsText
Dim tblName As String 'table associated with TableDataMacro
Dim col As Collection 'collection from review of DataMacro xml files
Dim fol As Folder 'folder
Dim fil As File 'file
Dim FSO As FileSystemObject
30 Set FSO = New FileSystemObject
40 Set col = New Collection
50 Set fol = FSO.GetFolder(strFolder)
'List Tables and Filenames in the existing DataMacros stored via SaveAsText
60 For Each fil In fol.Files
70 If fil.Type = "xml File" Then 'datamacros store as xml files when SaveAsText
80 If InStr(fil.Name, filt) > 0 Then
90 tblName = Mid(fil.Name, Len(filt) + 1)
100 tblName = Left(tblName, Len(tblName) - 4)
110 Debug.Print tblName & String(38 - Len(fil.Name), " ") & fil.Name
120 col.Add tblName & "|" & fil.Path 'store table name "|" filename in collection
130 End If
140 End If
150 Next
'Review NonSystem Tabledefs in this database
Dim X As Integer
Dim db As DAO.Database
Dim tbl As DAO.TableDef
160 Set db = CurrentDb
'CHECK that there are nonSystem tables in the current database
170 If CheckForNonSystemTables Then
' Compare xml files and associated tables with Current database Tables
' where there is a match, Load the DataMacro from the related xml file
180 For X = 1 To col.Count 'xml datamacro info is in Col
190 For Each tbl In CurrentDb.TableDefs
200 If Left(tbl.Name, 4) <> "MSys" And _
Left(tbl.Name, 4) <> "USys" Then
210 If tbl.Name = Left(col(X), InStr(col(X), "|") - 1) Then
220 Debug.Print "Table " & tbl.Name & " has datamacro Text at " & Mid(col(X), InStr(col(X), "|") + 1)
230 LoadaDataMacro tbl.Name, Mid(col(X), InStr((X), "|") + 1)
240 Debug.Print "---Macro was loaded from text"
250 End If
260 End If
270 Next
280 Next
290 MsgBox "Data macros for tables in this database have been LoadedFromText", vbOKOnly
300 Else
310 Debug.Print "No user tables exist in this database: " & CurrentDb.Name
320 MsgBox "No user tables exist in this database: " & CurrentDb.Name
330 End If
340 On Error GoTo 0
DataMacroFiles_Exit:
350 Exit Function
DataMacroFiles_Error:
360 MsgBox "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure DataMacroFiles" _
& " Module Module1 "
370 GoTo DataMacroFiles_Exit
End Function
Code:
' ----------------------------------------------------------------
' Procedure Name: LoadADataMacro
' Purpose: Routine to Load a TableDataMacro from Text
' Procedure Kind: Sub
' Procedure Access: Public
' Parameter tblWithDatamacro (String): Name of Table to receive tableDataMacro
' Parameter FileNameWithMacroText (String): Full path and filename to the TableDtaMacro xml
' Author: Jack
' Date: 11-Jan-24
' ----------------------------------------------------------------
Sub LoadaDataMacro(tblWithDatamacro As String, FileNameWithMacroText As String)
10 On Error GoTo LoadaDataMacro_Error
20 LoadFromText acTableDataMacro, tblWithDatamacro, FileNameWithMacroText
30 On Error GoTo 0
LoadaDataMacro_Exit:
40 Exit Sub
LoadaDataMacro_Error:
50 MsgBox "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure LoadaDataMacro" _
& " Module TestLoadDataMacro "
60 GoTo LoadaDataMacro_Exit
End Sub
Code:
' ----------------------------------------------------------------
' Procedure Name: CheckForNonSystemTables
' Purpose: Routine to check and confirm at least1 nonSystem Tables exists in current database
' Procedure Kind: Function
' Procedure Access: Public
' Return Type: Boolean
' Author: Jack
' Date: 30-Jan-24
' ----------------------------------------------------------------
Function CheckForNonSystemTables() As Boolean
10 On Error GoTo CheckForNonSystemTables_Error
Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim tdCount As Long
20 For Each tdf In CurrentDb.TableDefs
30 If Not tdf.Name Like "Msys*" Then
40 tdCount = tdCount + 1
50 CheckForNonSystemTables = True
60 Exit Function
70 End If
80 Next
90 On Error GoTo 0
CheckForNonSystemTables_Exit:
100 Exit Function
CheckForNonSystemTables_Error:
110 MsgBox "Error " & Err.Number & " (" & Err.Description & "), line " & Erl & " in Procedure CheckForNonSystemTables" _
& " Module ModDMacroUtils "
120 GoTo CheckForNonSystemTables_Exit
End Function
Marshall,
I renamed your databases with leading Marshall.
Databases and xml in "C:\users\jp\documents\"
renamed the old xml with old prefix and txt suffix
Copied latest routines from #157 to module2
set reference to scripting runtime for and using C:\Users\JP\Documents\MarshallDM_Test_Target.accdb
I found an issue with filename and corrected,
This is the line in DataMacroFiles that needed adjusting. This is the revised line that is now working.
'''''' messages ''''''
Table tblAuditLog has datamacro in Text file at C:\Users\JP\Documents\macrosFor_tblAuditLog.xml
---Macro was loaded from text
Table tblSource has datamacro in Text file at C:\Users\JP\Documents\macrosFor_tblSource.xml
---Macro was loaded from text
And below is picture that data macros were loaded.
I'll continue with the other database. Let me know if there is something specific you want me to check.
