stevegray
4Penny.net
Points: 55409

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.

and

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.

 

 

4Penny.net
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
3