I'm working on an application that will export and translate an Access database into a web-based (PHP/MySQL/Javascript) application. Small tests are going well, but when I try it on the 'production' scale database that is the true source for conversion, I repeatedly get the following error:
Run-time error '-2146500594 (800f000e)':
Method 'Item' of object 'Forms' failed
It's happening as I am trying to get the name of the 28th out of 38 forms that need to be converted. If I take that form and copy it lock, stock and barrel into a small test database, then there is no trouble. So it's obviously (I think) a scaling issue.
However, I can find no reference to this particular run-time error. I wonder if anyone has any ideas or pointers?
Is error trapping being used?
If it is across the network, perhaps there is a network timeout or code Stack issue.
In general: Use error trapping to isolate where the error is taking place.
Use the Err.raise to customize where the error takes place. http://support.microsoft.com/kb/146864
If the errors have a Hot Spot (a place they seem to occur more often) then add more detailed error trapping, and add more Debug.Print "Modulex The big text For-Next code"
This can provide a type of data to give an indication.
Once the hotspot is determined, step through the code. In the Debugger, turn on the Call Stack viewer. This shows the execution stack where one procedure, calls another procedure, that calls another procedure...
This can sometimes uncover the rare instance where code works great in short local spurts, but fails as computer resources or network delays build up for larger runs.
Then when you get a chance, please share what you are doing. It sounds interesting.
The environment is a local database (Access) that was used to construct a proof-of-concept tool for pitching to an organization in the UK. I now need to create the tool that will build at least the framework of the new environment (XAMPP). The easiest part, of course, is creating SQL to create and populate the new database (which will be MySQL).
The trickier parts will be the forms - which must be translated from Access to HTML5 (with CSS and JavaScript).
So far I have code which looks like this:
Code:
Sub SetUpProject(Optional BasePath As String = "")
' Create a Project folder, and a project control file
' Create sub-folders for:
' Forms
' Modules (except -DBTranslate__)
' Classes
Dim ProjectName As String
Dim ProjectFileName As String
Dim TableFileName As String
Dim TableFile As Long
Dim FormFileName As String
Dim FormFile As Long
Dim CSSFile As Long
Dim JSFile As Long
Dim PHPFile As Long
Dim ModuleFileName As String
Dim ModuleFile As Long
Dim ClassFileName As String
Dim ClassFile As Long
' Test setup
ProjectName = GetCurrentDBName()
ProjectPath = ProjectName
If BasePath > "" Then
On Error Resume Next
MkDir BasePath
On Error GoTo 0
ProjectPath = BasePath & Separator & ProjectName
End If
TablePath = ProjectPath & Separator & "Tables"
FormPath = ProjectPath & Separator & "Forms"
ModulePath = ProjectPath & Separator & "Modules"
ClassPath = ProjectPath & Separator & "Classes"
On Error Resume Next
MkDir ProjectPath
MkDir TablePath
MkDir FormPath
MkDir ModulePath
MkDir ClassPath
On Error GoTo 0
ProjectFileName = ProjectPath & Separator & ProjectName & ".pcf"
ProjectControlFile = FreeFile
Open ProjectFileName For Output As #ProjectControlFile
Print #ProjectControlFile, "; # Forms= " & CurrentProject.AllForms.Count
ScanForms
Print #ProjectControlFile, "; # Modules= " & CurrentProject.AllModules.Count
ScanModules
Close #ProjectControlFile
End Sub
ScanForms looks like this:
Code:
Sub ScanForms()
Dim FormName As String
Dim FormModule As String
Dim FormSubPath As String
Dim frm As Form
Dim f As Long
For f = 0 To CurrentProject.AllForms.Count - 1
FormName = CurrentProject.AllForms(f).Name
FormSubPath = FormPath & Separator & FormName
On Error Resume Next
MkDir FormSubPath
On Error GoTo 0
' Load the form
DoCmd.OpenForm FormName, windowmode:=acHidden
Set frm = Application.Forms(FormName)
Print #ProjectControlFile, "Form:", f, Format(FormName, fmtObjectName), frm.Controls.Count
ScanFormControls frm
CreateFormFile FormSubPath, FormName
If Header.hasModule = "M" Then
FormModule = frm.Module.Name
CreateFormModuleFile FormSubPath, FormName
End If
If Header.hasCSS > "" Then
CreateCSSFile FormSubPath, Header, Controls
End If
If Header.HasPHP > "" Then
CreatePHPFile FormSubPath, Header, Controls
End If
If Header.hasJS > "" Then
CreateJSFile FormSubPath, Header, Controls
End If
Set frm = Nothing
DoCmd.Close acForm, FormName
Next f
End Sub
The error, when it occurs always occurs in ScanForms, as I am about to execute the code:
Code:
Set frm = Application.Forms(FormName)
The first thing I checked was that I'm resetting all objects to nothing, because over a lot of iterations that can really blow memory management. The text of the message "Method: Item failed" leads me to think that I've got a corrupt collection in Application.Forms. However, It should only ever have a single entry at any given time. When I try and do some simple analysis on the collection, I get this:
Code:
? application.Forms.Count
1
?application.Forms(0).Name
/* Same error message here! */
The earlier forms are being analyzed exactly as I'd hope, including a dump of the vba code to handle the events (which will be the basis for the JavaScript file and part of the CSS file). I suppose I'll have to read up on using the other tools that you suggest - a quick scan for Call Stack Window suggests that it is part of Visual Studio. I'm actually working within Access at present (it's one less level of reaching out across the borders between applications ;-)
Any suggestions (other than switching to pure VB.Net?)
Here's an update on my problem. I considered several possibilities;
1) Some Form_Load event handler was screwing me up
2) The problem was volume related (ie after 28 forms being opened, the whole thing just hiccupped)
3) The problem was form-related (ie there is something about the form that Access just doesn't like)
4) Some other problem.
To test (1) I added Exit Sub after every Form_Load procedure, before it had a chance to do anything. I uncovered a few strange things (to do with unexpected Nulls since the form 'initialization' was not being executed {although since I'm examining the form not executing it, I don't see how that can be})
To test (2) I created a test db with 50 forms (mostly copies, I'll admit), and ran the procedure there. Worked fine!
To test (3) I copied the form and it's module into the test database. No issues. (As a footnote, I actually did this before test 2, so there were several copies of the crashing form in the test database!)
Which leaves (4) - Some other problem.
I noticed when running the procedure in question, that the first form examined came up very fast, and then there was a long wait before the next 14 or so came up all in a mad dash. So I added a debug statement to see how long each was taking. Turns out it must be a buffering problem, because (see below) the delta of Timer() is reasonably consistent.
Then I wondered if the collection Application.Forms was getting screwed up. The error message, remember, says "Method 'Item' of object 'Forms' failed!"
So I extended the debug line to get the index of the form within Application.Forms. For some reason they are (almost) all index 2 (which means the 3rd entry, right? Wonder what the first two are...) HOWEVER the last form is index 1, so something must have happened.
Here's where it gets really weird.
I tried adding code to print the caption of the 0th and 1st forms, but it failed with the same Run-Time error (and a description of automation error).
When stepping through, form by form, even though the count is 2, the effective index is 1 (not 0 ??), until we get to the problem form.
I'm totally flummoxed, and without some sort of ability to peek inside the stack I can't figure out what is going wrong.
(BTW, I disabled On Error GoTo 0 to see what else would happen. Everything works fine, except the index is now consistently 1, rather than 2)
Any ideas?
Code:
44695.5 Form 0, ID_IdentityDictionary Index into Forms is 2
44695.58 Form 1, SYS_InterfaceMaintenance Index into Forms is 2
44695.66 Form 2, DefaultConfig Index into Forms is 2
44695.73 Form 3, DefaultUserForm Index into Forms is 2
44695.84 Form 4, DEM_ConfigForm Index into Forms is 2
44695.91 Form 5, LAB_TestUniqueIDForm Index into Forms is 2
44696 Form 6, MEM_ConfigForm Index into Forms is 2
44696.06 Form 7, STDY_ConfigForm Index into Forms is 2
44696.14 Form 8, STDY_StudyUserControl Index into Forms is 2
44696.2 Form 9, STDY_SurveyBrowseForm Index into Forms is 2
44696.3 Form 10, SYS_ConfigForm Index into Forms is 2
44696.38 Form 11, SYS_CreateIdentityForm Index into Forms is 2
44696.45 Form 12, SYS_ValidateIdentityForm Index into Forms is 2
44696.52 Form 13, LEARN_ConfigForm Index into Forms is 2
44696.59 Form 14, STDY_StudySponsorForm Index into Forms is 2
44696.69 Form 15, STDY_SurveyExtractForm Index into Forms is 2
44696.78 Form 16, SYS_UserControl Index into Forms is 2
44696.86 Form 17, SYS_ViewForm Index into Forms is 2
44696.94 Form 18, LEARN_StudentControl Index into Forms is 2
44697.02 Form 19, LEARN_TeacherControl Index into Forms is 2
44697.09 Form 20, SYS_Tables Index into Forms is 2
44697.14 Form 21, LEARN_RegisterStudent Index into Forms is 2
44697.23 Form 22, ID_IdentityUsers Index into Forms is 2
44697.3 Form 23, wizExportWizard Index into Forms is 2
44697.38 Form 24, wizImportWizard Index into Forms is 2
44697.44 Form 25, SYS_AdminConsole Index into Forms is 2
44697.5 Form 26, DB_InterfaceObjectForm Index into Forms is 2
44697.59 Form 27, DB_OrganizationForm Index into Forms is 2
44697.63 Form 28, DB_OrganizationOfficersForm Index into Forms is 2
44697.64 Form 29, DefaultObjectForm Index into Forms is 1
-2146500594 Method 'Item' of object 'Forms' failed
44697.66 Form 30, STDY_QuestionDesignForm Index into Forms is 1
44697.8 Form 31, STDY_StudyDesignForm Index into Forms is 1
44697.89 Form 32, STDY_SurveyDesignForm Index into Forms is 1
44697.98 Form 33, SYS_ClassMasterForm Index into Forms is 1
44698.09 Form 34, SYS_ClassParentForm Index into Forms is 1
44698.13 Form 35, CCCBR Master Index into Forms is 1
44698.25 Form 36, SYS_UserInterfaces Index into Forms is 1
44698.34 Form 37, DEM_DemographicDictionary Index into Forms is 1
), and ran the procedure there. Worked fine!