Access/Sql Client Server Database

[Guest]

Hi everybody

I have couple of Access Databases, and need to be converted to the Client/Server database using Sql server as backend and Access as frontend. This is new to me, and I couldn't find lot of information about many of the detail functions of the two systems working together.

I tried to use views, functions and store procedures in Sql server to get the best performance of the system, but I have problem updating, inserting or deleting records through views when the views have multiple base tables. Most forms were built on quries on multiple tables, and the controls are bound controls, users need to update, insert or delete data through the forms. What can I do in this case? Any ideas and answers will be greatly appreciated!

 

[Tony]

-----Original Message-----
Hi everybody,

I have couple of Access Databases, and need to be

converted to the Client/Server database using Sql server
as backend and Access as frontend. This is new to me, and
I couldn't find lot of information about many of the
detail functions of the two systems working together.

I tried to use views, functions and store procedures in

Sql server to get the best performance of the system, but
I have problem updating, inserting or deleting records
through views when the views have multiple base tables.
Most forms were built on quries on multiple tables, and
the controls are bound controls, users need to update,
insert or delete data through the forms. What can I do in
this case? Any ideas and answers will be greatly
appreciated!

.

Hi Shelly,

Have you tried to delete, insert or modify these records
straight from the SQL Server? Typically, views are read
only and strictly for viewing that data. I would start by
verifying that the Views are working correctly. Also, is
this an ADP database?

Tony

 

[Albert D. Kallal]

Most views from sql server should be updatable....

If the query in ms-access was updateable, then it most certainly should be
updateable in sql server.

I would make sure you include the timestamp field, as ms-access can use that
to help it figure out what to update.

Also, I am sure most of your joins are left joins...right?

I mean, if you just use a standard join, then, likely a customer may not
have a invoice yet, and thus you can't display/edit that customer. If you
use a left join, then you don't force the requite that the customer have/has
a invoice to edit.

However, since 90% of my quires in ms-access have had to be left joins...
then you would have had to do the same in sql server. So, if those joined
tables and quires were updatable in ms-access, then they should be
updateable in sql server. (unless of course you are talking about new views,
and not existing quires). So, when you created views for a form to edit,
you created the equivalent of the current query from ms-access...right? I
mean, if you just have a ms-access form working on table, then you actually
don't need a view.

Anyway, most views/join quires in sql server are updateable, just like they
are in ms-access.

However, most one to many relationships in ms-access are edited via a form
to a sub-form setup. This means you did not use a join query in ms-access,
and you don't have to change this fact when using sql server. How did, or
how do you plan to add child records? In ms-access we use sub-forms, and
they don't requite views, or queries with a join.

So, forms/sub-forms setups will work just fine with sql server, but you do
want to expose the timestamp fields to the sub-forms. In fact, exposing the
timestamp fields to the main form also is a good idea. By expose, I simply
mean to include the timestamp field in your views/quires that you use.

For just quires/views where you don't edit data, but just display
(reports/combo boxes etc), then you don't really need the timestamp field,
but when you want to edit the data in ms-access, then the timestamp fields
SHOULD BE included in the view.

 

[Guest]

Thanks Tony. That's very helpful.

Our database is not ADP database, does it make difference?



----- Tony wrote: -----
Hi Shelly,

Have you tried to delete, insert or modify these records
straight from the SQL Server? Typically, views are read
only and strictly for viewing that data. I would start by
verifying that the Views are working correctly. Also, is
this an ADP database?

Tony

 

[Guest]

Hi Albert ,

Thanks so much for your help. Your information is really helpful.

Some of my joins are standard join (like getting customer name, product description,etc.), and lot of them are left join. In Access, they are all working well. I am in the process of learning and testing the convertion, I am going to start allover again with these information.

I did a test, I create a view on a single table, and tried to update, delete or insert to the view through Access, it said "recordset not updatable?"

Since passthrogh query is not updatable, so I can only update, insert or delete data through the view by links?(link the view through Access)

Another question here: are the following 3 methods have the same functions and performance:

1. Create a view, and have a passthrough qry to execute the view, form's record source is the Query
2. Create a passthrough qry based on the tables tables (as the above view), form's data source is the Query.
3. Create a view, and have Access to link to the view, form's record source is the Query

Can I know which of the above is the best way, or common way to do? Which one is not the right practice?

 

[Albert D. Kallal]

As mentioned, I don't know why those tables are not updateable.

Have your tried viewing/editing data using the Enterprise manager (EM)?

Further, do most, if not all of your tables have a timestamp field exposed
in the views? (ms-access really does need the timestamp field. So, if you
been missing this from the tables, then it is time for you do to a bit of
reading on sql server).

I did a test, I create a view on a single table, and tried to update,

delete or insert to the view through Access, it said "recordset not
updatable?"

Perhaps your permissions on the sql server are messed up. Remember, all
security, and permissions to the views and tables are now controlled by sql
server. Can you edit the tables via the EM?

Another question here: are the following 3 methods have the same functions and performance:

1. Create a view, and have a passthrough qry to execute the view, form's record source is the Query
2. Create a passthrough qry based on the tables tables (as the above view),

form's data source is the Query.

3. Create a view, and have Access to link to the view, form's record source

is the Query

Gee, you can do none of the above, and simply link the form directly to a
view, and not even use a query.


You have to be VERY CLEAR on what you mean by "query" now. We have:

linked tables (ms-access tables...linked to sql-server tables)
linked views (ms-access tables...but they are linked to sql views)
Saved Sql Quires in ms-access (These queries can operate on either of the
above linked tables, or linked view. I mean, all of the linked views, and
linked tables appear in the table views).

Remember, in sql server, a view is really the same idea of a saved query in
ms-access. However, since we are now linking to sql server, then you have
another layer here.

So, when you say a forms record source is a query, I assume you mean a query
on the ms-access side? It is not clear when you say:

"forms's reocrd souce is the query"


I mean, using a pass-through query to a sql server table, or a pass-through
to a view is really only a matter of your choice. (it clearly would be no
different to ms-access). It also makes no difference from a performance
point of view.

I mean, in ms-access, you can use a table, or a query (your code, forms,
reports don't care if you use a query, or a table).
The same is for sql server (views are identical to saved quires in
ms-access. So, you can use a view in place of a table anytime you wish.
Since you were asking about a pass-through, then there going to be no
performance issue here).

So, I would just simply link the form to the linked sql table, or to the
linked sql view. You not going to see any performance difference, and going
to all the trouble to use a pass-through to a view is not going to help
(aside of the issues of joins).

You can easily use a linked table, or a linked view as the source for the
form. If you are restricting the records to that form, then you can
successfully use the "where" clause, and thus DO NOT need a access query for
the source. Of course, if you have a rotten design, and do not restrict the
records loaded to a form (I usually restrict them to ONE record), then you
may get some gains by using pass-through queries, but it should not be
needed.

Using a linked table, or linked view will NOT really hurt performance much
from a speed point of view. So, really, you are free to use either approach.
However, as mentioned, if the sql HAS ANY JOINS etc, then you REALLY need to
use a view, as you don't want ms-access to try and "join" two tables on the
access side (it is very slow). So, once again to be clear, don't try and
join tables with access quires.

So, further using a ms-access query based on the linked view, or a ms-access
query based on the linked table is not going to perform any different, and
you are really free to choose either one. Of course, if there is any
restrictions/conditions, or sort order stuff in the query, then OF COURSE it
is best to put the sorting, and the restrictions in a view, and simply link
the form directly to that view. (so, if you can eliminate the use of a query
on the ms-access side,a and link directly to the view, that is the best).

However, you certainly get away using a access query for the forms source
(and, as above that query can be on a linked table, or linked view). Since
you ARE NO DOUBT restricting the records loaded to the form via the where
clause, then one (or very few) records are going to be loaded into the form
anyway. (the form will load very fast). Since the form load is gong to be
fast, then you will likely use the approach that takes the least amount of
work (that means you link the form directly to the sql table).

So, of your three options, linking a form directly to a linked view is the
best. The real question is how do you now restrict records loaded into a
form now? I mean, as mentioned, no one just opens a form without some
restrictions to the record being loaded.

I mean, you prompt the person for invoice number, and then load up the form
with that invoice number...right? Try testing a few forms. You will in
general find that using the form attached to a linked table, or linked view
is just fine. The real trick is assuming that you have a good design that
asks the user what they want BEFORE you load the form.

-
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn

 

[sedira rachid]

salut comment vas tu

Hi Albert ,

Thanks so much for your help. Your information is really helpful.

Some of my joins are standard join (like getting customer name, product

description,etc.), and lot of them are left join. In Access, they are all
working well. I am in the process of learning and testing the convertion, I
am going to start allover again with these information.

I did a test, I create a view on a single table, and tried to update,

delete or insert to the view through Access, it said "recordset not
updatable?"

Since passthrogh query is not updatable, so I can only update, insert or

delete data through the view by links?(link the view through Access)

Another question here: are the following 3 methods have the same functions and performance:

1. Create a view, and have a passthrough qry to execute the view, form's record source is the Query
2. Create a passthrough qry based on the tables tables (as the above

view), form's data source is the Query.

 

[Guest]

Thanks Albert. Good information. I am going to try again.