Query Question: Looking for Insight wrt linking Subforms

[Toga]

I have this posted in the forms section as well, but the more I think about it, it's really a query question.

I have a question wrt the underlying recordsource of a sub form.....but I'll start with what I found with a standard form linked to 50000 records. From the testing I've done with a command such as

stLinkCriteria = "[CustOrderID]=" & Me![CustOrderID]
DoCmd.OpenForm stDocName, , , stLinkCriteria

The access form needs to load all of the records (or there indexes) into memory before the form actually opens with the one record I was looking for. The form actually opens faster if I don't specify a particular record.

Lets say a subform is also tied to a table of 50000 records. If I link the master & child fields, does it also have to load all 50000 records before it actually does the search for the say 5 records my main form may be linked to? Does anyone happen to know? If it does, is there an alternate way? I've experimented using a list box which does give me the speed I want.....but I can't seem to format / justify the fields the way I want.

Speed on a database server is my issue where I'm using the Access forms as a front end and Sybase as the database.

 

[Kubilus1]

My advice to you is to never have a form use a link table directly if the table contains over a few hundred records, if speed is any issue at all.

You should consider using a pass-through query to get the data you want and link the form and subform to the pass-through query. Keep in mind, if you link a subform to a pass-through query, you CANNOT link the main form and subform by child/master fields.

Hope this is some help,
Matt

 

[Toga]

Thanks Matt... I've been learning your good advice the hard way I'm afraid. Access makes it all too easy to fall into the trap of doing things the wrong way.....that is if you ever intend to have alot of data or access your data from Sybase or something like that.

I'm not knocking Access.....I'm amazed really at it's power ...... you just need to be careful which path ya choose to do something.

I've never actually used a pass-through query but will take a look and see if I can get one to work for a test. I actually got some pretty good advice in the forms section as well and seems to be along the same lines....

In a nutshell, don't link your forms but set the recordsource to the records you want when you open the forms. If you get a chance, take a look at that posting as I would be interested to know how that approach would compare to that of a pass-through query.

Thanks....Toga

 

[Kubilus1]

I agree with you 100 percent. It's easy to do things the wrong way in Access.

The difference between linking a recordsource to a query in Access (as spelled out in the post in the forms section), and linking a recordsource to a pass-through query, is in where the query is executed.

Let me explain:

Using linked data complicates things. Keep in mind that Access does not directly link to another database engine like Sybase, but uses another program such as an ODBC or OLE DB datasource, adding a step (and therefore time) to the communication process.
If you have a linked table of 50000 records named Products in Sybase (or SQL, or whatever), and you have a query in Access that wants 25 records from that table this is what happens. First Access tells the ODBC or OLE DB that it wants to use the Products table. Sybase then sends the entire table through the ODBC/OLE DB to Access. Access runs the query, chooses the 25 records it wants and throws away the 49975 records it doesn't need. This is slow and wasteful.
A pass-through query works differently. Access simply sends the query to Sybase through the ODBC/OLE DB untouched. Sybase recieves the query, chooses the 25 records needed and sends these back to Access.

Take it from me, using a pass-through query is FAR faster, and really is the only way to go if using large linked tables of any kind. You'll thank yourself for learning this powerful database tool.

Good luck,
Matt

 

[Toga]

Thanks for the explanation...

We have some apps that are split access databases.

So let me ask this because I only see references to databases such as SQL-server & Sybase wrt pass-through query's. Can you / should you use pass-through query's on a split Access database instead of linking the tables in?

If not, is there a better (Faster) way than linking the tables into the forms database so I only retrieve the records I want? Even the split Access databases run slower than I would like on the network.

Opinions anyone????

Thanks....Toga

 

[Toga]

I should provide a little more insight wrt my question I think. The reason I ask is this.

* Our IS&S group manages our Database server and really don't want anyone in there since they are responsible for it....which I really don't have a problem with. We all need to work together!

* But I often work on my apps at home as well as at work. I don't have a database server at home....and I really don't want to get in to that.

* When I'm done with an app, I then would like it put on the server.

So the root of my question really stems from wanting to develop the app on Access in such a way that it's easy to port to the server when I'm done....and have it written in such a way that I don't have to redo all my querry's to optimize it.

I understand that I probably won't be able to utilize the advantages of stored procedures doing this....but I don't know how to get around that at this point.....unless I can somehow name / define an access query in a table and then switch where it looks for that query to the server at a later point.

 

[zztop]

Is it possible to pass a value (date) from an Access form into a pass-through query? Or is what you write in the pass-through query sent directly to the backend database.

Here is what I want my end result to be:

Exec uspGetSummaryByState '07/01/2001', '07/31/2001'

Here is what I'm attempting to accomplish this:

Exec uspGetSummaryByState 'CDate(([forms]![frmReportMenu]![txtStartDate]))', 'CDate(([forms]![frmReportMenu]![txtDate]))'

Thanks
CJ

 

[Kubilus1]

Yes it is very much possible, and my databases are largely built around this, but there is a trick to it. To answer your question, Yes everything in a pass-through query is sent directly to SQL. So this is what we have to do . . .

Let's say I have a form call Orders. On this form I can choose a begining date, BeginDateP, and an ending date, EndDateP.

There would then be a command button that would take BeginDateP and EndDateP and create a SQL string to execute my stored procedure getOrderDates. Such as:

SQLstring = "getOrderDates '" & BeginDateP & "', '" & EndDateP

(If you plan to have many queries like this I recommend using a seperate module containing code for all you parameterized pass-through queries. It will make code easier to maintain for you, and prevent headaches from anyone else how may need to perform database work in the future.)

I would then take this string and send it to a module that drops an existing query and replaces it with a query containing this new string.

Look here for some info on this:

thread701-91679 you have a query that returns a result set from the parameters you have chosen. Every time you need to change the parameters you will have to drop and re-create this query, but don't worry it happens really fast, and you don't have much of another choice.

Happy coding,
Matt