parameterized action queries that reference form values

Question

Post reply

parameterized action queries that reference form values

mfromer

Mr or Mrs. 500

Points: 548
More actions
July 13, 2005 at 6:29 am

#66730

I am moving my FE/BE linked MDB application to SQL server. I have countless parameterized action queries that reference form values. Stored procedures and views cannot reference MS Access form values, so how can you reference form values in an ADP which has no local queries?

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

AJ Ahrens SSC-Insane Points: 20676 More actions · Answer 1

Research ADODB parameters. There is decent help for them online and in the HELP file.

Good Hunting!

AJ Ahrens

webmaster@kritter.net

mfromer Mr or Mrs. 500 Points: 548 More actions · Answer 2

mfromer

Mr or Mrs. 500

Points: 548

July 13, 2005 at 6:59 am

#573852

Where online and which help file?

Richard Moldwin SSCommitted Points: 1872 More actions · Answer 3

Off the top of my head (I'll try to be accurate), try this:

For forms, you can use the InputParameters property of the form, and include question marks for the parameters in the recordsource line:

SELECT A, B FROM C WHERE D_int = ? AND E_char = ?

The input parameters property will look something like:

? int = MyForm!MyCtrl1, ? char = MyForm!MyCtrl2

(This approach appears to be safe from SQL injection, but you should still be careful.)

IIRC, to use a SP (preferred), you can use the parameter values directly in the recordsource property:

MySP MyForm!MyCtrl1, MyForm!MyCtrl2

IIRC, You can use InputParameters with named SP parameters instead of question marks:

Recordsource = MySP

InputParameters = @Param1 = MyForm!MyCtrl1, @Param2 = MyForm!MyCtrl2

I can't remember if you can use variable types (@Param1 int = MyForm!MyCtrl1) in these statements - you can experiment.

Never use dot notation as recordsource properties; only bang notation will work! Also, Parent notation does not work in my hands. You need actual form names.

I think you can also use VBA functions instead of controls to populate the parameters, but you will have to experiment yourself.

ComboBox row sources work in a similar manner to the above.

For some things, SQL Server user defined functions (UDFs) are very useful, like SELECT * FROM MyUDF(Param1, Param2) WHERE ...., and they work fine in ADPs.

You need the Access 2002 Enterprise Developer's Guide. MS documentation is almost worthless for ADPs.

HTH,

Rich

mfromer Mr or Mrs. 500 Points: 548 More actions · Answer 4

Will I experience any problems with a MDB FE linked to a SQL Server 2000 BE using ODBC? I have around 15 concurrent users.

Also, has anyone experienced any problems using the image datatype in SQL Server to store excel and word documents?

Richard Moldwin SSCommitted Points: 1872 More actions · Answer 5

"Will I experience any problems with a MDB FE linked to a SQL Server 2000 BE using ODBC?"

Please see the previous threads, as this is discussed in some detail, and it's a big subject.

"Also, has anyone experienced any problems using the image datatype in SQL Server to store excel and word documents?"

I do this all the time. Despite all the prophets of doom out there, it works just fine. It makes your database REALLY BIG, though (especially if you use audit tables!) You might want to consider linking to files instead, if space is a problem. Also, for normal operations, you should always design views that do not include these huge objects, so you don't inadvertenly send a table-load of them over the network! Only bring over one OLE (image) object at a time at a time, unless you have a specific need for more.

HTH,

Rich