Can anyone tell me the best way of mixing SQL and code to create a 

recordset for a report or form.

At the moment I am using SQL to add records to a 'holding' table, then 

creating a recordset from the 'holding table' using DAO and then using 

code to update other fields in the 'holding table'.

Is this a good way of going about this as it seems inefficient to 

constantly add and delete records from the holding table.

I have heard of stored procedures but I am not sure these can be used in 

Access.



Any ideas would be most appreciated.



Cheers

Jason

Jason,



Access does indeed support stored procedures. They are a good way to

retrieve just the records you are looking for by passing variables to the

parameters of the stored procedure. Once the stored procedured is saved in

the MDB, you can call it any time. Recommend reading up on "parameters" &

"stored procedures".



Walt



Following is a DAO example:



Executing a parameterized stored query:



A parameterized stored query is an SQL statement that has been saved in the

database and requires that additional variable information be specified in

order to execute. The following code shows how to execute such a query.



DAO



Sub DAOExecuteParamQuery()

  Dim db As DAO.Database

  Dim qdf As DAO.QueryDef

  Dim rst As DAO.Recordset

  Dim fld As DAO.Field



  ' Open the database

  Set db = DBEngine.OpenDatabase _

    (".\NorthWind.mdb")

  ' Get the QueryDef from the

  ' QueryDefs collection

  Set qdf = db.QueryDefs("Sales by Year")



  ' Specify the parameter values

  qdf.Parameters _

    ("Forms!Sales by Year Dialog!" & _

    "BeginningDate") = #8/1/1997#

  qdf.Parameters _

    ("Forms!Sales by Year Dialog!" & _

    "EndingDate") = #8/31/1997#



  ' Open the Recordset

  Set rst = qdf.OpenRecordset _

    (dbOpenForwardOnly, dbReadOnly)

  ' Display the records in the

  ' debug window

  While Not rst.EOF

    For Each fld In rst.Fields

      Debug.Print fld.Value & ";";

    Next

    Debug.Print

    rst.MoveNext

  Wend

  'Close the recordset

  rst.Close



End Sub

Well, typically you can just bind your report or form directly to a table or

query object (e.g. name a table in the RecordSource property during design

time), in which case you don't need to go to all the trouble of having a

holding table, etc..  There are times though when you want to have that kind

of fine-grained control tho--maybe you can say some more about your app?



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

Extension 8487



-----Original Message-----

From: Jason@C... [mailto:Jason@C...]

Sent: Tuesday, November 13, 2001 2:15 AM

To: Access

Subject: [access] Mixing SQL and VBA code





Can anyone tell me the best way of mixing SQL and code to create a 

recordset for a report or form.

At the moment I am using SQL to add records to a 'holding' table, then 

creating a recordset from the 'holding table' using DAO and then using 

code to update other fields in the 'holding table'.

Is this a good way of going about this as it seems inefficient to 

constantly add and delete records from the holding table.

I have heard of stored procedures but I am not sure these can be used in 

Access.



Any ideas would be most appreciated.



Cheers

Jason

Roy



Thank you for your reply



Basically i create a rather complicated union query based on information on

rental equipment.

I then need to perform some if statements to split the cost between various

units, and put the results in another field, and also add additonal records

if required.  The process is too complicated to put in one SQL statement,

therefore i update the 'holding table' so i can then bind this table to a

report.  Is this correct or can i bind the report some other way.



Thanks

Jason



-----Original Message-----

From: Pardee, Roy E [mailto:roy.e.pardee@l...]

Sent: 14 November 2001 19:06

To: Access

Subject: [access] RE: Mixing SQL and VBA code





Well, typically you can just bind your report or form directly to a table or

query object (e.g. name a table in the RecordSource property during design

time), in which case you don't need to go to all the trouble of having a

holding table, etc..  There are times though when you want to have that kind

of fine-grained control tho--maybe you can say some more about your app?



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

Extension 8487



-----Original Message-----

From: Jason@C... [mailto:Jason@C...]

Sent: Tuesday, November 13, 2001 2:15 AM

To: Access

Subject: [access] Mixing SQL and VBA code





Can anyone tell me the best way of mixing SQL and code to create a

recordset for a report or form.

At the moment I am using SQL to add records to a 'holding' table, then

creating a recordset from the 'holding table' using DAO and then using

code to update other fields in the 'holding table'.

Is this a good way of going about this as it seems inefficient to

constantly add and delete records from the holding table.

I have heard of stored procedures but I am not sure these can be used in

Access.



Any ideas would be most appreciated.



Cheers

Jason

Well, there's nothing necessarily 'wrong' with what you're describing--it's

hard to evaluate w/out lots of detail (and that would probably not be

productive for list discussion).  If it's working and gives acceptable

performance, then *I'm* not going to criticize it. 8^)  Something that

complex is likely to be harder to maintain &/or extend in the future, but

sometimes your app really needs to be complicated.



I think if you wanted to, you could probably unbind your form &

populate/update controls/fields programmatically, but that's also a lot of

work & I don't know that you'd gain anything by it.



FWIW, when I find myself going through lots of gyrations in code, I

frequently take it as an occasion to rethink my table/relationship design.

A lot data wrangling that I end up having to do in code (esp non-SQL code)

is just working around a clunky table design.



Cheers,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

Extension 8487



-----Original Message-----

From: Jason Walsh [mailto:Jason@C...]

Sent: Thursday, November 15, 2001 1:38 AM

To: Access

Subject: [access] RE: Mixing SQL and VBA code





Roy



Thank you for your reply



Basically i create a rather complicated union query based on information on

rental equipment.

I then need to perform some if statements to split the cost between various

units, and put the results in another field, and also add additonal records

if required.  The process is too complicated to put in one SQL statement,

therefore i update the 'holding table' so i can then bind this table to a

report.  Is this correct or can i bind the report some other way.



Thanks

Jason



-----Original Message-----

From: Pardee, Roy E [mailto:roy.e.pardee@l...]

Sent: 14 November 2001 19:06

To: Access

Subject: [access] RE: Mixing SQL and VBA code





Well, typically you can just bind your report or form directly to a table or

query object (e.g. name a table in the RecordSource property during design

time), in which case you don't need to go to all the trouble of having a

holding table, etc..  There are times though when you want to have that kind

of fine-grained control tho--maybe you can say some more about your app?



HTH,



-Roy



Roy Pardee

Programmer/Analyst

SWFPAC Lockheed Martin IT

Extension 8487



-----Original Message-----

From: Jason@C... [mailto:Jason@C...]

Sent: Tuesday, November 13, 2001 2:15 AM

To: Access

Subject: [access] Mixing SQL and VBA code





Can anyone tell me the best way of mixing SQL and code to create a

recordset for a report or form.

At the moment I am using SQL to add records to a 'holding' table, then

creating a recordset from the 'holding table' using DAO and then using

code to update other fields in the 'holding table'.

Is this a good way of going about this as it seems inefficient to

constantly add and delete records from the holding table.

I have heard of stored procedures but I am not sure these can be used in

Access.



Any ideas would be most appreciated.



Cheers

Jason