Form datasource from sql server backend

[John]

Hi

I have a typical front-end/backend access app. The backend is migrated to
sql server. I need to migrate an access query (with several sub queries)
which is currently used as recordsource for a form in front-end. My
questions are;

1. How should I implement the access query and sub queries in sql server; as
view or sp?

2. How can I set the query from sql server (either as view or sp) as
recordsource for the front-end form?

Thanks

Regards

 

[Albert D. Kallal]

1. How should I implement the access query and sub queries in sql server;
as view or sp?

You should use a view. And, then you can setup a link to that view.

On the other hand, your existing queries in ms-access should work. The only
reasons to modify them is if they perform too slow. in other words, you have
the base tables moved and linked to sql server. Your existing queries should
then simply work.

2. How can I set the query from sql server (either as view or sp) as
recordsource for the front-end form?

As mentioned, the existing recordsource should work without any
modifications. so, in fact you don't have to setup or change anything here.

if the form really is based on a query (that has multiple record sources),
then you can continue to use that query. If it performs too slow, then
convert that query to view, and then add a new link in your front end to
that view (this is generally the least amount work). I don't believe it
makes sense to use a stored procedure to create what amounts to a sql query.

If the form is based on one table, and the sub-form on another, then you not
have to do anything here..it should work as before. So if the form +
sub-form was linked to tables, then leave it as is.

If the "main" form is based on a query that incorporates several tables,
then I would take that query and convert it to a view...and then create a
link to that view..and then change the forms data source to that linked
view. Same goes for the sub-form.

However, there is nothing wrong at all basing the main form on the linked
table, and the sub-form on its appropriate linked table also (you not going
to gain peformance here by using a view). So, simply based the form on the
base (linked) table is just fine here. If you base the form(s) on their
approapte tables, then you not have to bother with building any views at all
here.