I have written a program that has been used by a number of people for five or six years now. Originally it was written in Access/VBA 2003, but I have recently upgraded it to Access 2010. That's when the problem started. The program runs fine on my PC. When I distribute the program the users all receive an error:
"An error has occurred in your application.13"
"Error Description: Type mismatch"
I can't duplicate this error on my PC. I met with one of the users and compiled the program on his PC. It initially told me one of the references was invalid and I deleted it. I can't be sure which one as it's several months ago now, but from memory I am pretty sure that I then deleted that reference on my PC also. He and the other users can now run the version compiled on his PC.
I have now altered the program, so compiled the update on my PC but once again the users still get the original error message. Someone suggested using the package wizard as that would set the references when they ran the setup, but still the same problem.
I then installed Access 2010 my other PC and found that it ran the accdb without error and no References were missing, but when I ran the accde I got the same error message as the users receive. I then compiled the code into an accde on that PC and it ran fine. Question is, why doesn't it run when compiled on my main PC as the other PC is quite old and slow, and for practical purposes I need to use the main PC.
The references that I used have are:
Visual basic for applications
OLE Automation
Microsoft Access 14.0 Object Library
Microsoft ActiveX Data Objects 2.1 Library
Maybe it has nothing to do with references at all. One would assume there is a problem with a setting on my PC, but I have no idea where.
Each time the error message occurs the common code seems to be running is:
'Create a new instance of a recordset
Dim cnn1 As ADODB.Connection
Set cnn1 = CurrentProject.Connection
Dim MyRecordSet As New ADODB.Recordset
MyRecordSet.ActiveConnection = cnn1
'declare cursor type and lock type
MyRecordSet.CursorType = adOpenDynamic
MyRecordSet.LockType = adLockOptimistic
My main PC runs Windows 7. My other PC runs XP. The user whose PC I compiled it on runs Windows 7, so it worked on both an XP and Win 7 PC.
If anyone can help it would be greatly appreciated as I have run out of ideas.