Search the GP Tables
Search by TABLE NAME
Search by FIELD NAME
These are our SOP scripts. All of our scripts for Dynamics GP can be found here

Table Edits Forum Articles
Table Edits 0 Table Edits 0 Table Edits 0
Sign Up Now!Log In
SteveGray 1/24/2014 11:23:54 AM

VBA ADODB Paramaterized Query Example

* This article, and all our great VBA documentation, Is available on the VBA/Modifier menu

This is a sample of a piece of code that executes a SQL statement in VBA and uses an ADODB parameter.

Why go to all the trouble? Google 'SQL Injection Attack' and you'll find out. Using an ADO Parameter object will protect you.

We're frequently called upon to write simple validations behind forms in Dynamics GP, this piece of code is boiler plate for us - cut and paste, edit the SQL, done.

For the record, we're a 'stored procedure only' shop... but for things as small as this the direct SQL make sense. The  query is small, and the customer doesn't have to maintain the stored procedure.                        

Note that there are no quotes around the parameter question mark... that took me a while to get. Using this method, you don't use quotes even for string params.

Along the way we caused a few errors:

Run-time error '3708': Parameter object is improperly defined. Inconsistent or incomplete information was provided.


Run-time error '1006': Unsafe Operation. This operation cannot be performed in the target field's AfterUserChange, BeforeLostFocus, or AfterLostFocus Events. This operation could compromise the integrity of the application.
Version: GP 9,GP 10,GP 2010,GP 2013
Section: VBA

Table Definition Quick Links
All Tables
SOP Tables
RM Tables
GL Tables
POP Tables
HR Tables
PM Tables
UPR Tables
IV Tables
Olympic Tables