Accpac Crash after VB code run

[Tianjin]

I’ve built a production schedule in Excel spread sheet that need to pull MO data into this spread sheet. Three views of MIMOH, MIMOMD, OEORDH are involved in data exporting. This program worked fine in all other aspects. The problem is that as long as I started to run this program, our ACCPAC system became very instable as result of Data corrupting quite often, and recover process was really pain on.
Actually, I used “Set xxxxx=nothing “ to release all the references to ACCPAC at the end of VB code. Even though I completely closed Excel application, when I checked in our server, it show that many ACCPAC objects DLL were referenced under my name. All references could be removed until I shut down my computer. I know that some administrator processes in accpac need every user logging off from accpac and my program for some reasons was putting a hook on accpac in the background. I wonder someone could help me to figure out either error within my code or accpac setup issue, right now I manually did all data entry for our production schedule. Main portion of the code is attached as following(not runnable as data manipulate code is excluded).

Thank you very much in advance.


Private Session As ACCPACXAPILib.xapiSession
Private Company As ACCPACXAPILib.xapiCompany
Private viewOEORDH As xapiView
Private viewMIMOH As xapiView
Private viewMIMOMD As xapiView
Public Sub ExportMoToSh()
'.......
Set Session = CreateObject("ACCPAC.xapisession")
Session.Open AccpacUseName, AccapacPassword, "DNADAT", Date, 0
Set viewOEORDH = Session.OpenView("OE0520", "OE")
Set viewMIMOH = Session.OpenView("MI0046", "MI")
Set viewMIMOMD = Session.OpenView("MI0047", "MI")

With viewMIMOH
strBrowse = "(WOHID>" & MaxMoInSchd & ") AND (WOHID<2)"
.Order = 1
.Browse strBrowse, True
Do While .Fetch
'export data from header.......
With viewMIMOMD
.Order = 1
.Init
strBrowseD = "(" & "WOHID" & "=" & Trim(viewMIMOH.Fields("WOHID").Value) & ")AND(" & "ITEM" & "=" & """LBLAB""" & ")"
.Browse strBrowseD, True
Do While .Fetch
'export data from detail........
loop
End With

With viewOEORDH

.Order = 1
strBrowseOED = "ORDNUMBER" & "=" & Trim(viewMIMOH.Fields("DESCR").Value)
.Init
.Browse strBrowseOED, True
.Fetch
'export data from OE header.........
End With
Loop
End with
EndPoint:
Set viewOEORDH = Nothing
Set viewMIMOH = Nothing
Set viewMIMOMD = Nothing
Set Session = Nothing
End Sub

 

[jayconverse]

What version of SM and Misys?
What service packs?
Which database?
Why are you using Xapi instead of COMAPI?

Jay Converse
IT Director
Systemlink, Inc.

 

[Tianjin]

Thank you for responding timely. I am very new to
accpac and have a little bit experience with VBA
programming. please be patient if question or answer
is not relevant.
1. what is SM?
Misys is 5.2
2. what is service packs refered to? I don't know service packs for accpac, but I haven't updated XP in my computer to service packs 2 that microsoft just issued recently.
3. Database is pervasive SQL v8
4. I've read some threads in this group and knew there
are some arguments about using Xapi and COMAPI. The
reason I picked Xapi as my programming library is that
a couple of threads provides step by step directions
which are very helpful for new user.

Thanks

TianJin

 

[zemp]

SM is the system manager.

The service packs are the ACCPAC service packs. You can check these by opening ACCPAC and going to the 'Help-system information...' menu. All your modules and thier latest service packs will be listed.

An explanation of the difference between xAPI and COMAPI can be found in thread631-874110.

One thing that I would recommend is that you also include all related views in you code. This will ensure that all data is updated correctly and could be a cause of your problems. To see all the related views just record a macro. It will list all the views and how they are composed.

zemp

 

[jayconverse]

Zemp is right. You don't have any compositions in your views, and that can definitely corrupt data.

Jay Converse
IT Director
Systemlink, Inc.

 

[Tianjin]

Hi Zemp,
I read a lot of threads written by you that gave me the chance to move on this wonderful journey and very appreciate all you have done.

jayconverse,
Here are rest of info. about our system
system manager(SM)5.2
service packs for OE is 4.
for MI is 5.

I don't know very well about compose method. Is it similar as relationship between Database tables.

oh! I just got you back, jayconverse. thanks!!!

I have record marco in accpac. Obviously, there are a lot of subviews composed. thus, Your points definitly hit nail right on the head. In other word, I have to compose all the views related and explicitly close all references to all views. I will try it and let you guys know the result.

Thank you so much,

tianjin

 

[zemp]

Thanks for the kind words guys, glad to be of help.

Here is an example of how I declare, set and compose the OE order views for use (using xAPI).

   Dim OEORDheader As ACCPACXAPILib.xapiView
   Dim OEORDdetail1 As ACCPACXAPILib.xapiView
   Dim OEORDdetail2 As ACCPACXAPILib.xapiView
   Dim OEORDdetail3 As ACCPACXAPILib.xapiView
   Dim OEORDdetail4 As ACCPACXAPILib.xapiView

   '// Set the views.
   Set OEORDheader = qmSession.OpenView("OE0520", "OE")
   Set OEORDdetail1 = qmSession.OpenView("OE0500", "OE")
   Set OEORDdetail2 = qmSession.OpenView("OE0740", "OE")
   Set OEORDdetail3 = qmSession.OpenView("OE0180", "OE")
   Set OEORDdetail4 = qmSession.OpenView("OE0680", "OE")
      
   '// Compose the views.
   OEORDheader.Compose Array(OEORDdetail1, OEORDdetail4, OEORDdetail3, OEORDdetail2, Nothing, Nothing, Nothing, Nothing, Nothing)
   OEORDdetail1.Compose Array(OEORDheader, Nothing)
   OEORDdetail2.Compose Array(OEORDheader, Nothing)
   OEORDdetail3.Compose Array(OEORDheader, Nothing, OEORDdetail1)
   OEORDdetail4.Compose Array(OEORDheader, Nothing, OEORDdetail1)

zemp

 

[XenexSue]

Hi Tanjin,

In addition to composing, you might want to make it a habit to Close the views when you are finished with them. On the other hand, it appears your code is only reading the tables. That shouldn't corrupt the data.

Suzette

 

[zemp]

Setting the views to nothing will automatically close them first. It is also a good idea to cancel the view (.Cancel) at the end of the loop. This ensures that the .init starts with a fresh view. Also you set the .Order before the .init, this doesn't always work because the .init sets the view back to it's defaults, including the default .order.

zemp