Creating queries in an .ADP 4

[Orion45]

I am in the process of converting a large Access 2000 based application to Access 2002 and .adp file format. I've noticed that in .adp local queries have been replaced by views and stored procedures. I'll admit views are better for returning data from the SQL server but many of my forms rely on queries which run on parameters from other forms. A view will not understand a where clause which gets criteria from other forms. Does anyone know of a way to create a traditional query in a .adp file? Here is an example of the kind of SQL statement I am talking about.

SELECT School_LKP.SchoolName FROM School_LKP WHERE (((School_LKP.Accredited)=Abs(Forms!AddSchool_subform!radAccredited)) And ((School_LKP.ForeignSchool)=Abs(Forms!AddSchool_subform!radforeign))) ORDER BY School_LKP.SchoolName;

Any suggestions are appreciated. Thanks,
Ryan

 

[cmmrfrds]

If you used the upsizing wizard it would convert your queries to stored procedures(SP's). SP's can take paramters and be used as the record source for a Form or Report. At the very minimum run the upsizing wizard to generate the SP's. I would use the upsizing wizard to convert the entire App.

 

[Orion45]

I did use the upsizing wizard to convert to Access 2002 format. None of my queries were converted to procs, instead they seem to have just disappeared. I tried recreating several of the needed queries as both views and stored procs but I kept getting the message that my syntax was incorrect where I used field references. I have been looking into known error reports on Microsoft's Knowledge Base but so far haven't found anything. For now, my only other option is to create a recordset and assign it to the forms recordset in the onLoad event.

 

[cmmrfrds]

I recently upsized an app on Access 2000 and all my queries were generated as stored procedures or views. I looked at the upsizing report and there were some queries that had errors but nothing major. So, in theory they should have upsized.

When the stored procedure is used as the record source of a Form the parameters can be added in the Parameter property under the data tab. Use the format like so:

@var1=Forms!myForm!myField,@var2=Forms!myForm!myfield2

or make you variables public and return them from a function. Like so;

@var1=ReturnVar1(),@var2=ReturnVar2()

 

[Orion45]

Looks great, thanks for your help!

 

[Bensta]

Is there any way we can create queries locally in an ADP files? I don't want to use a sp or view for some of my processes. Is it possible?
I would truly appreciate any input
Thank you

 

[cmmrfrds]

Can you explain why the queries need to be local and will the query be a record source for a Form or Report.

 

[BSman]

If you're trying to do the same thing that I wanted to do when I looked into converting an Access/SQL application to Access Project, I think you are out of luck.

I have an application where I have a form designed so the users can basically build their own queries and then see the results (both a full query and a grouping query based on the sort columns they selected) in the spreadsheet type display format that is readily available in Access.

What happens in my application is the query is created by the form as a pass through query (using Access 97 linked to SQL Server (7, we don't have 2000 yet)) and then executed.

The problems with Access Project are the following:

1. You can't create a "pass through query" since there are no Access type queries.

2. Although you can create a view using Access 2000, according to what I've read Access 2002 does not allow you to create views, procedures, etc., from within Access Project. You have you use the SQL Enterprise manager.

3. Views in SQL (7) do not include sorting, which I need to have in the query/view.

I got really inventive and set up a table with a column that would contain the entire SQL statement. I then created a procedure that would read that column from the (1 row) table and execute it. This method works great...until the second time you run it in Access. Access remembers the column layout that was returned the first time...and insists on trying to display the same columns the second time. Since the second time might involve a different source table or view, the columns might be very different.

The result of all of my attempts to get around this problem ended up with my staying with Access 97/2000 linked to SQL Server and using pass through queries.

 

[Bensta]

The reason why I want some queries to be stored locally is because the sql server will not be accessible to everyone in the company. Some users will need to run some queries against the database for thier reporting needs. They will not be able to do so since thier access rights are limited.
I also heard that it is beneficial to run some queries locally for effeciency and storage space purposes. In other words, I want to maintain a thin server and client at the same, if it is a good idea of course.
The other thing I noticed with adp files is that filtering reports based on a value on a form can be very tricky. It does not work all the times. Is there an easier way to filter a report based on a value on a form. Is it also possible to group by on field of type bit? Every time I try to goup by I get an error message " you can not group by on field of type bit" I can goup by on bit values in access with no problem?
Thank you very much in advance.
Sincecrely,

 

[BSman]

ADP does not have an Access database, so there are no "local" queries. Forms, Reports, Modules, and Macros are still in the Access ADP database, but the other objects are in the SQL database: tables, views (the SQL version of queries), stored procedures (which are not in Access MDB).

Running a view(query)/stored procedure in SQL Server is much more efficient than running it at the client. Not only should it run faster on the server, but the only network traffic is the set of data that is the result of the selection criteria contained in the view/stored procedure.

If you want to limit the use of a view or stored procedure, incorporate either different user rights to the SQL objects or include in your application different forms for the different groups of users where each different form lets the users who can open the form run the appropriate limited access views/stored procedures.

Use stored procedures to use a filter on a form. Set the variables to be passed to the stored procedure to the values on the form. Stored procedures also have the advantage that you can include in them SQL code that will select using the passed along variable values and you can also sort the results (which you may not be able to do with a view).

For the bit value for grouping you might try having the query turn it into a Y or N based on the value of the bit, so it will be a text field for grouping.

 

[Bensta]

Thank you very much for you inputs BSman. I truly appreciate your help. I will try following your instructions.
Thank you

 

[cmmrfrds]

Filter.
Use a server side filter in ADP.

Bit Field.
You should be able to Convert or Cast the bit field to integer. Look up the syntax - I believe it is.

Group by Convert(int,mybitfield) as myfield

 

[Bensta]

First of all, thank you cmmrfrds for your input.
I have two more questions on adp files. What would be the best way to deploy an ADP application in a networked environment? How to make sure that my ADP application will perform very effeciently in terms of speed and robustness regadless of the number of users and network traffic?
Thank you in advance.