VBA Macro Best Practice - Session Initialization

[iwells]

Hi,

In my first post I asked some fairly generic questions and received some deserved generic answers, but at this point I'd like to post some specific code for your analysis. The new organization I've joined doesn't have any existing modules in place, it appears as though they just copy/paste code from previous macros into new ones or copy the macro itself and edit it. Furthermore, there appears to be a lot of use of ADO.

1) Will I be correct in attempting to steer users away from ADO to AccPac views? The majority of the macros are for reports so I don't think there's any data editing happening. What other reasons might there be other than existing logic in the views to use the views over ADO. Keep in mind you're not selling me, but other users (I see the inherent advantages of the existing methods and objects in the ACCPACCOMAPI).

I'd like to create a module as a sort of best practice to be added to each report and here's what I've seen on the forum so far:

Option Explicit

Public sSession As AccpacCOMAPI.AccpacSession
Public mDBLinkSysRO As AccpacCOMAPI.AccpacDBLink
Public mDBLinkCmpRO As AccpacCOMAPI.AccpacDBLink

Private Sub Test()
    
    Set sSession = New AccpacCOMAPI.AccpacSession
    
    sSession.Init "", "XY", "XY1000", "56A"
    sSession.Open "ADMIN", "ADMIN", "CMPDAT", Date, 0, 0
    
    If sSession.IsOpened Then
        
     Set mDBLinkSysRO = sSession.OpenDBLink(DBLINK_SYSTEM, DBLINK_FLG_READONLY)
     Set mDBLinkCmpRO = sSession.OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READONLY)
    End If
End Sub
[code]

I've not included the .close, = nothing for the session and the error handler yet.

2) This is the beginning of a sample module I want to create for each new report. Is what I've posted above correct? My biggest concern is what happens if a user has multiple sessions open? Also in some people's posts I see the use of AccpacSessionManager and AccpacSession instead of the AccpacCOMAPI ... I'm curious of the differences here if any?

3) The organization has varying versions of Accpac (we're upgrading, but that's an on going project) and it isn't as simple as saying "update them" so I'm wondering if the .Init will fail on previous/later versions? Are there ways around this and related concerns to doing so?

 

[Jay Converse]

You shouldn't be doing any Session.Inits in a VBA macro. Login to an Accpac company, select Macro/Run, and the current session is used automatically.

 

[ettienne]

Without knowing what the macros actually do (there might be some data processing in ADO) it is a crap shoot as to what is best. But as tuba stated you do not need to initialize any sessions if you are running VBA macros in Accpac. If they are running in say Excel then that is a different scenario, then you do need to initialize sessions - however there are easier ways of doing that as well. As far as Accpac data access goes you can extract data using Accpac's views, or alternatively use the CS0120 view to run SQL queries against the Accpac database since you are running 5.6 (I assume from your code).
PS you do not need to open a link to the system database (mDBLinkSysRO), unless you need to get some security related info.

 

[iwells]

Excellent ... I have seen both of your names on the forums and had been hoping either of you would respond (but instead I get both!).

1) Init ... that's perfect because that would have been somewhat of an issue here. The vast majority of what's done here are reports written in Excel and I'm guessing that the report will pickup the company database for the session the macro is run in?

2) Most of the reports are run from VBA in Accpac and then exported to Excel. For the most part I think I can do that without using ADO and I appreciate the point about the system database.

To give you a little more context, the organization I work for has over 20 sites around the world where each has their own system and company database (I'm not sure why and I'm not in a position to change this ... I'm not the boss), but I do see opportunities for improvement right away. First is to improve the consistency for report creation, but I'd like both of your input very specifically on the second item. There are discussions going on here regarding the use of SSRS as the main reporting too and I'm wondering if either of you have any pros/cons to this approach? Personally I've used it before and prefer it, but wanted some input from the people who seem to be the resident Accpac pros.

 

[ettienne]

I've looked at SSRS but never felt a need to use it, Crystal does most of what I need in reporting and the gaps are filled with either some custom program or other reporting tool. Take a look at Biznet

http://www.biznetsoftware.com/

as well, brilliant piece of software IMO.

 

[Jay Converse]

1. If you use Excel VBA, then you have to .Init a session. If you use Accpac VBA, then you don't.
2. Yes, sounds like extra work. I write direct to Excel via ADO all the time.
3. Each company does NOT need it's own system database, that's a mistake rookie consultants make.
4. SSRS is fine, but I prefer Crystal, because you can drop them on the Accpac desktop

 

[iwells]

Ettienne ... as far as our organization is concerned we have SSRS already (we host all our servers on SQL servers) so that's the direction we're headed. I'm not in a position to influence that direction.

Tuba2007 ... yes and no on the rookie mistake. As a previous consultant for Dynamcics GP I agree 100% with what you're saying, but our organization has acquired many of the other offices over time so it wasn't initially setup to be this way. With that said they could have brought them all on board, as they were acquired however due to the remote locations of some of our offices I think there were large connectivity concerns that were very valid (areas of Africa and South America where there is virtually no internet access or we've installed satellite at a high cost already and the speed still isn't enough for users) and also resource requirements and massive growth at the time.

Ultimately we want to get to a single area for all reports that's easier to manage and distribute.

 

[Jay Converse]

Ettienne and I both know Africa. I hope your remote offices are on RDP or Citrix.

 

[iwells]

Hi,

I've been sidetracked with a few other reports in SSRS, however I've come back to this and here's my question:

Do I need to declare and assign a session variable at all if I'm writing VBA in an Accpac Macro?

This is going to be ultra noob and I've played with this for a bit, but here's where I am:

mdlAccpac

Option Explicit

Public DBLinkCmpRW As New AccpacCOMAPI.AccpacDBLink
Public Sub InitializeMacro()

Set DBLinkCmpRW = AccpacSession.OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
End Sub
Public Sub TerminateMacro()

Set DBLinkCmpRW = Nothing

Unload frmMain
End Sub

mdlMain

Option Explicit

Public GLPOST As New AccpacCOMAPI.AccpacView

Public Sub InitializeProgram()

mdlAccpacMacro.InitializeMacro

DBLinkCmpRW.OpenView "GL0030", GLPOST
End Sub

Public Sub TerminateProgram()

Set GLPOST = Nothing

mdlAccpacMacro.TerminateMacro
End Sub

What has me confused is clearly this doesn't work, but I was told not to Init a session in a macro, but do I still need to open it? If so, do I need to pass the userID and PW?

 

[ettienne]

Do I need to declare and assign a session variable at all if I'm writing VBA in an Accpac Macro?
No, the AccpacSession is automatically created.

 

[ettienne]

What has me confused is clearly this doesn't work, but I was told not to Init a session in a macro, but do I still need to open it?
No

 

[ettienne]

It's good programming practice (GAPP) to close what you open, i.e.:
DBLinkCmpRW.Close
GLPOST.Close

I know that using Set GLPOST = Nothing implicitly closes GLPOST, but I follow the practice to explicitly close everything.

 

[iwells]

I copy past your example, switch the views and it fails ...

 

[iwells]

Yea I'll do the closing once I can actually open Sorry ... very frustrated at the moment.

 

[ettienne]

I don't know why but I generally avoid using public variables. I think somewhere a couple of decades ago I was taught that it is bad programming practice, but I suppose it's a matter of opinion.

 

[ettienne]

Not all your code is there, so it's not easy to follow.
Do yourself (and us ) a favor and create a test procedure that contains everything (all you dims, opens, closes, etc) and then try to get that working first. Post the code here if you get stuck.

 

[iwells]

I can use them in the procedure and it still doesn't work.

 

[ettienne]

Okay, you're going to have to be more specific than that.

 

[iwells]

I understand you'd like all the code, but I'm not a fan of advancing through code if I'm stuck at an error.

Private Sub InitializeMacro()

Dim mDBLinkCmpRW As AccpacCOMAPI.AccpacDBLink
'Dim GLPOST As AccpacCOMAPI.AccpacView
Dim ASCOPY As AccpacCOMAPI.AccpacView

'Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
'mDBLinkCmpRW.OpenView "GL0030", GLPOST

Set mDBLinkCmpRW = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
mDBLinkCmpRW.OpenView "AS0025", ASCOPY
End Sub

If I run that it works, if I comment out the ASCOPY view and use mine it doesn't. Does it work for you?

 

[ettienne]

Here's a simple code example to open GLPOST, loop through it and close it.


Sub MainSub()

Dim mDBLink As AccpacCOMAPI.AccpacDBLink
Dim GLPOST As AccpacCOMAPI.AccpacView

On Error GoTo ACCPACErrorHandler ' Set error handler

Set mDBLink = OpenDBLink(DBLINK_COMPANY, DBLINK_FLG_READWRITE)
mDBLink.OpenView "GL0018", GLPOST

Do While GLPOST.GoNext
Debug.Print GLPOST.Fields("ACCTID").Value
Loop

GLPOST.Close
mDBLink.Close

Set GLPOST = Nothing
Set mDBLink = Nothing

Exit Sub

ACCPACErrorHandler:
Dim lCount As Long
Dim lIndex As Long
If Errors Is Nothing Then
MsgBox Err.Description
Else
lCount = Errors.Count
If lCount = 0 Then
MsgBox Err.Description
Else
For lIndex = 0 To lCount - 1
MsgBox Errors.Item(lIndex)
Next
Errors.Clear
End If
Resume Next
End If
End Sub