VBA Update

[iwells]

Hi,

I've recently switched jobs and moved out of the IBM world back to the SQL Server world. My new employer uses ACCPAC/Sage 300 ERP extensively (version 5.6 at the moment ... for the most part) and I'm looking for a few quick comments to bring me back up to speed. Please note, I was a VB programmer from 1999-2003 at which point I moved on to some other languages or away from programming totally at times for consulting. With that said, I consider myself a SQL expert and I've done some small things over the years in VB and felt it was mostly like riding a bike. With all that said ...

1) ACCPAC version 5.6A uses VBA 6.4 ... is there any documentation on the ACCPAC objects? Basically anything under AccpacCOMAPI.

2) What version(s) of VB.Net are compatible with VBA 6.4 or is this even an issue? I'm going to assume the New-Program functionality in ACCPAC can be used to point to a custom VB.Net application and that might be better served at times. In particular I'd rather write code in the latest compiler (VS2012) and create an .exe and run from that if possible. I assume as long as the ACCPAC API is accessible then there aren't many issues (my light searching shows some minor compatibility issues here and there). I guess that's the concern ... is there a .dll or something I can register in VS2012 so I can just code in VS2012?

3) I've done some digging and see there are some preferred native functions (like DBlink) which I can probably find in the AccpacCOMAPI (assuming it exists). Are there any other glaring/obvious items I should be aware of?

4) CS0120 ... is this actually necessary if you query the database directly? It seems like a lot of solutions I've read use this tool/query, but I guess I don't understand why people aren't driving right at the tables (again I'm using SQL server so that might have been a factor in the responses).

5) Stored procedures - I've done a lot of work in SQL/TSQL and it doesn't seem like anyone does much manipulation or extraction directly on the server through a procedure.

As you can see I'm trying to use current tools with the application and I appreciate the input.

Thanks,

Ian

 

[vbajock]

There is an ACCPAC forum here on Tek Tips where you would probably find specific answers to your ACCPAC questions. I would assume the first thing you need to find out is how to get ahold of the ver 5.6 ACCPAC SDK if you are planning to directly modify an ACCPAC installation itself, you would probably find the methods and properties available for manipulating ACCPAC via VB in the SDK. If you are integrating an outside application that needs to share SQL data with ACCPAC, then you would probably just need to figure out how to access your app in a menu in ACCPAC, and then from there your application would interact directly with the SQL database via ADO/ODBC.

VBA 6.4 is the version introduced with Office 2003. I would assume it is hung onto ACCPAC as the internal development environment of ACCPAC itself. It would behave like any Excel VBA or Access VBA code environment, again, the SDK would most likely give you the info you need to instantiate ACCPAC as an addressable object in your code. Most ERP systems of this type use this type of VBA environment so you can directly call Office 2003 components into your internal ACCPAC applications as COM objects, so you can present spreadsheets and Word merge docs to your users as part of your internal applications. I doubt this could be coded in VS2012, like all VBA setups of this type, I would assume ACCPAC is going to have it's own integrated VBA code editor that is available to those with Admin rights via a menu or shortcut key from the ACCPAC user interface.

Stored procedures can be called from either VBA via ODBC or VB.NET SQL client, and are used extensively in ERP modifications and development. VB.NET is used extensively if you do not plan to modify ACCPAC itself, and instead your development will consist of simple exits from ACCPAC to another app (which is usually a VB.net executable or a SQL Server Reporting Services application delivered via ASP.NET) that shares data via SQL server. Stored procedures play a powerful role in this type of application development, if you have heard that "it doesn't seem like anyone" is using it, well, I contract all over the Gulf Coast/California refinery/petro chemical world and I assure you stored procedures are in massive use.

Your ACCPAC install is a few versions behind. If it does get upgraded, get ready for major headaches if you or others have modified ACCPAC itself. One of the big arguments for doing as much development as possible outside of any ERP system is that the ERP vendor cares little for the hapless in-house developer, and they will make upgrading your code downright impossible and delete any and all addins without warning, or your addins will introduce major bugs into the new version. Also, if you create any objects, like tables or stored procedures within the ERP database on the SQL Server, the upgrade will usually hose those as well, so make sure you have backups of everything, because a lot of that is very difficult to get from back up tapes if the site is not specifically set up to back those objects up.

 

[iwells]

Thanks VBAjock! That gives me a great starting point and as I'm becoming more familiar with the system it's pretty clear modifications have not been made to the core application itself and I suspect I'll do my work outside the application as well.

One of the things I'm really looking for is the AccpacCOMAPI documentation ... if it exists. I've essentially been trolling this forum to pick up bits and pieces here and there, though that bothers me a bit because there's a lot of personal preference as opposed to documented best practices. Does an AccpacCOMAPI document exist or am I dreaming?

 

[vbajock]

That would be a question for the ACCPAC forum.