Best/propper way to open reports 1

[bwelmers]

First post ever so a little background on my access skills: I am a self-taught coder who has created a number of access databases before. Skill level somewhere around advanced to expert (no one else in the IT department can help me with access related questions)

I am currently developing a database that has 80,000 + records and am trying to figure out the best way (ok the proper way) to get the project to work. The database is currently working but I want to improve it and to optimize the functionality.

My main problem at this time is figuring out how to display information in reports. (In some ways it also will relate to forms)

What I am wondering is what is the best (or proper) way to display a subset of the records (created from a number of tables joined together currently in a view). The data that is displayed would need to be user selectable (I do this currently through a form) but not all the criteria fields need to be entered. Meaning the possible fields are [Year], [Semester], and [CourseID] where [CourseId] is not always entered.

The way that I currently am doing this is through the DoCmd.OpenReport function with criteria. I am guessing this is not the best method. The other methods I have seen but do not totally understand yet are through <Input parameters> and possibly setting the <server filter>. But I want to know what the best method would be.


Thanks for any pointers to the best method or sources to learn how to do this kind of work better.

Bernard Welmers

 

[cmmrfrds]

What version of Access? Access project (adp) or Access database (mdb)? What database?

 

[bwelmers]

I am using access 2000 and SQL server 2000.

I am really trying to use access data projects for it since that looks like it will run a whole lot better.

 

[cmmrfrds]

I take it the report is based on a query which you send the where clause through the DoCmd.OpenReport function. It may take sql server 1 or 2 seconds to optimize the query while a stored procedure will be pre optimized. So, the stored procedure will give the fastest response. For a 30 second query this probably does not make a lot of difference in a system that does not have a lot of concurrent users. If there are a lot of concurrent users or the query needs to respond in a second or two then go with the stored procedure. Both reports and Forms can be based on stored procedures. An Access project will be faster and cleaner than an Access mdb since it is base on OLEDB and avoids the overhead of using ODBC.

 

[bwelmers]

So waht you are saying is that in the perfect situation it would be better to send the data request through a stored porcedure?

What I am doing with the DoCmd.OpenReport function is opening a report that is based on a View. Having done some more work with it since the initial post, I think that opening the Report (and Forms) in this way does open them with a server filter, wich is already quite helpfull (though I can't seem to tell if the report opens that way - the forms at least show that they open with a server filter.)

So it looks like if I am willing to loose a bit of time by opening the reports in this way works even though the best way is to open it through a stored procedure.

Thank you for your help cmmrfrds.

 

[cmmrfrds]

Yes, the stored procedure will be the most efficient. In addition to Views, I sometimes build the SQL dynamically for the data source on Forms and Reports. This is probably comparable to the performance using Views so, if needed, that is another option. I don't think there is much advantage using server filters since sql server needs to parse the SQL and build a query plan in either case. This may come into play if you are requerying the recordset (not sure), but in Access most of the time the recordset is dealt with on the client side for updateable recordsets anyway.

 

[bwelmers]

Yap that is what I am doing.

you just gave me an other slight question. I am going to guess that by creating the SQL dynamically you are using the SetRecordSource property. Is it possible to use it from a form that opens the Report? I will leave the project working the way it is for now. That way I can go on to the next one but it would be nice to know if there is a good way of opening forms/reports with a dynamic SQL statement.

Thank you very much for your help. It is interesting becoming the resident expert on the interaction between Access and SQL. Especially since I have never actually taken any courses on either of them.

 

[cmmrfrds]

There are probably different ways of doing it, but typically I build the SQL statement in the on open event of either the Form or Report. This is done with the record source. The basic syntax is:
Me.RecordSource = "select * from table"

In Access 2000 the Form also has a RecordSet method that can be set. The Basic syntax is:
Dim rs as New ADODB.Recordset
Dim mysql as string
mysql = "select * from table"
rs.Open mysql, myconnection, mycursor, mylocktype
Set Me.Recordset = rs
This is limited in Access 2000 and you need to use a special provider to make the recordset updateable. The Report does not have a Recordset method. Later version of Access have more robust Recordset capability. This could be useful if you need to get data from another data source, such as, Oracle or Excel or any other data source that has an OLEDB Provider which is almost anything.