Class Module Choices

[JimS]

I'm developing a class module. Not sure it's necessary, but it's good
practice. Class is called clsBillofMaterial.

One method in the class is called "clone". Its job is to clone a bill of
material from one work order to another. I can do it in sql, though the
statement is long and requires parameterization. So, I have a couple
questions:

1. Should I create the action queries to insert the header and detail, then
simply refer to them (docmd.runsql...) from the routine, or should I set up
the action query from scratch in the routine to include the parameters (Model
ID, New ID, etc.)?
If I should create those queries in the db and refer to them, how do I
assign values to the needed parameters in the runsql command? I'm also
comfortable with ADO commands if that makes more sense....(.command and
..execute methods...)

2. Would it be more sensible to instantiate ADO recordsets in the class
initialization routine and use them in loops rather than using sql statements?

Jim

 

[Klatuu]

Use the SQL.
Use Class properties to gather your parameter values.
Use the Clone method to write the SQL using the parameter properties.
Execute the SQL from the class.
Don't use the RunSQL method. A much faster and better method is to use the
Execute method. It would look like:

Currentdb.Execute(strSQL, dbFailOnError)

The dbFailOnError is important because if you don't use it and an error
occurs, you will not know an error occured. The Execute method bypasses the
Access UI and goes directly to Jet. In addition to execution speed, you
don't have to mess with SetWarnings because they happen in the UI.

 

[JimS]

Dave, thanx for the succinct answer. So, I think you're saying I should
assemble the SQL within the class module as opposed to storing it as a stored
procedure. Is that right? Of course, if I do that, the parameters won't be an
issue (as you stated...)

Thanks again.

 

[Klatuu]

You didn't say you were using SQL Server, so that would change things. You
can use the same concept except that you would use ADO to pass the parameters
to the Stored Procedure. That would be faster strill (in almost all cases)

 

[JimS]

Dave, sorry, not using SQL server. A2007 and jet.

When I said stored procedure, I mis spoke. I meant a stored query in Access.
So, absent the need for parameters, I could code:

currentdb.execute "qryInsertSomeStuff",dbFailOnError

But the insert query would need to be parameterized ("Where tbl1.x =
[Param1]")

I don't know how to assign a value to Param1 within the execute command. Is
there a way?

 

[Klatuu]

Okay. SQL Server has Stored Procedures. Jet (now named Ace in 2007) does
not. Perhaps you meant Stored Query with is different. In Jet, a stored
query is just an SQL query that is saved as a query object as opposed to
writting your own query.

You can modify a stored query during runtime, but it is easier just to write
it in code.
--
Dave Hargis, Microsoft Access MVP

Dave, sorry, not using SQL server. A2007 and jet.

When I said stored procedure, I mis spoke. I meant a stored query in Access.
So, absent the need for parameters, I could code:

currentdb.execute "qryInsertSomeStuff",dbFailOnError

But the insert query would need to be parameterized ("Where tbl1.x =
[Param1]")

I don't know how to assign a value to Param1 within the execute command. Is
there a way?
--
Jim

You didn't say you were using SQL Server, so that would change things. You
can use the same concept except that you would use ADO to pass the parameters
to the Stored Procedure. That would be faster strill (in almost all cases)

 

[Marshall Barton]

Dave, sorry, not using SQL server. A2007 and jet.

When I said stored procedure, I mis spoke. I meant a stored query in Access.
So, absent the need for parameters, I could code:

currentdb.execute "qryInsertSomeStuff",dbFailOnError

But the insert query would need to be parameterized ("Where tbl1.x =
[Param1]")

I don't know how to assign a value to Param1 within the execute command. Is
there a way?

Dim db As Database
Dim qdf As QueryDef
Set db = CurrentDb()
Set qdf = db.QueryDefs(qryInsertSomeStuff")
qdf.Parameters!Param1 = whatever
qdf.Execute dbFailOnError