Writing to a sql database in accpac

Question

I've got a VB script that's being run by AccPac. I want to add error logging to the script. I'd love to have that log go to a database. I've heard it's impossible to connect to a SQLdatabase from the VB script. I'd like to prove this impossibility wrong.

So my question is this.
Has anyone run sql to a sqlserver database via an accpac vbscript? If so could they give me a clue as to how they accomplished such an "impossible" task?

Answer 1

Accpac actually uses VBA and not VBScript (although the syntax is almost identical). I have done lots of VBA macros in Accpac that use SQL databases for much more complex tasks than just logging. The process is the same as with MS Office files that have VBA macros. You have to add "Microsoft ActiveX Data Objects 2.8 Library" or similar as a reference via "Tools > References". You then have an ADODB object you can use to connect via ODBC.

Check out this question relating to the same thing with excel Accessing SQL Database in Excel-VBA

Please note that starting from Accpac 6, Sage is moving away from the desktop client and your VBA macros wont work with the portal.

Answer 2

In newer Sage 300 (Accpac) versions, there is a view with Roto "CS0120". It can help you to run SQL queries directly on database (insert/select/update). Just open it as regular view in your VB Code, set your query string as Browse filter and call Fetch.