locked
Queries vs Views RRS feed

  • Question

  • Let me first say, excuse me for asking a rookie question:

    I'm using MS Access as a front end for forms design, and SQL Server as a backend for data storage and querying.

    MS Access will have linked tables.

    The form in question needs to allow staff to do data entry.

    I can't seem to do this with a view as it won't allow the table to auto increment a record ID.

    My problem, and hence the rookie statement, to the best of knowledge, you can't save a query in a SQL DB, so I'm at a loss as to how to allow for data entry from a form, without giving direct access  to the SQL tables or the form.

    I'm trying to make as much work occur on the server as possible (on SQL's side) as opposed to bringing unnecessary work to the client side (on Access' side).

    Any help would be appreciated.

    Saturday, May 20, 2017 3:14 AM

Answers

  • I can't seem to do this with a view as it won't allow the table to auto increment a record ID.

    Hello,

    You can use views in SQL Server to update (edit) data, but there are some more limitation then with views in MS Access, see Modify Data Through a View and see the section 'Updatable Views' in CREATE VIEW (Transact-SQL).


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, May 20, 2017 5:08 AM
    Answerer
  • I'm asking how to set/create the view so that users can use the form to modify records using the form as the interface?


    So I understood it right, you want to modify data via view and there are several restrictions in SQL Server; see the links I posted.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Saturday, May 27, 2017 4:49 AM
    Answerer
  • Let me first say, excuse me for asking a rookie question:

    I'm using MS Access as a front end for forms design, and SQL Server as a backend for data storage and querying.

    MS Access will have linked tables.

    The form in question needs to allow staff to do data entry.

    I can't seem to do this with a view as it won't allow the table to auto increment a record ID.

    My problem, and hence the rookie statement, to the best of knowledge, you can't save a query in a SQL DB, so I'm at a loss as to how to allow for data entry from a form, without giving direct access  to the SQL tables or the form.

    I'm trying to make as much work occur on the server as possible (on SQL's side) as opposed to bringing unnecessary work to the client side (on Access' side).

    Any help would be appreciated.

    Hi DarkAngel,

    Your question is depenign on how you would balance among all jobs between the server and the client.

    1. You can create whatever new views, that can be based on other tables, on the SQL server. And this is the "server" side.
    2. For Queries (to be created inside your Access form), you can create them, that would further "retrieve" from other views (in (1)), and let them only run inside your Access form! This means they're running in the "client" side.


    Many Thanks & Best Regards, Hua Min



    Saturday, May 27, 2017 8:25 AM
  • Actually it's not clear for me what you trying to achieve and what the Problem is. May can you provide some more details and also an example, please?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, June 9, 2017 6:06 AM
    Answerer

All replies

  • I can't seem to do this with a view as it won't allow the table to auto increment a record ID.

    Hello,

    You can use views in SQL Server to update (edit) data, but there are some more limitation then with views in MS Access, see Modify Data Through a View and see the section 'Updatable Views' in CREATE VIEW (Transact-SQL).


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, May 20, 2017 5:08 AM
    Answerer
  • I can't seem to do this with a view as it won't allow the table to auto increment a record ID.

    Hello,

    You can use views in SQL Server to update (edit) data, but there are some more limitation then with views in MS Access, see Modify Data Through a View and see the section 'Updatable Views' in CREATE VIEW (Transact-SQL).


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Saturday, May 20, 2017 5:09 AM
    Answerer
  • I think you misunderstood my question.

    I wasn't looking to update records using a SQL statement in a view.

    We have MS Access Forms that will connect to Linked Tables in the MS Access DB,

    however, the Linked Tables will be linked to views and not the Tables.

    I'm asking how to set/create the view so that users can use the form to modify records using the form as the interface?

    Sunday, May 21, 2017 5:31 PM
  • I'm asking how to set/create the view so that users can use the form to modify records using the form as the interface?


    So I understood it right, you want to modify data via view and there are several restrictions in SQL Server; see the links I posted.

    Olaf Helper

    [ Blog] [ Xing] [ MVP]


    Saturday, May 27, 2017 4:49 AM
    Answerer
  • Let me first say, excuse me for asking a rookie question:

    I'm using MS Access as a front end for forms design, and SQL Server as a backend for data storage and querying.

    MS Access will have linked tables.

    The form in question needs to allow staff to do data entry.

    I can't seem to do this with a view as it won't allow the table to auto increment a record ID.

    My problem, and hence the rookie statement, to the best of knowledge, you can't save a query in a SQL DB, so I'm at a loss as to how to allow for data entry from a form, without giving direct access  to the SQL tables or the form.

    I'm trying to make as much work occur on the server as possible (on SQL's side) as opposed to bringing unnecessary work to the client side (on Access' side).

    Any help would be appreciated.

    Hi DarkAngel,

    Your question is depenign on how you would balance among all jobs between the server and the client.

    1. You can create whatever new views, that can be based on other tables, on the SQL server. And this is the "server" side.
    2. For Queries (to be created inside your Access form), you can create them, that would further "retrieve" from other views (in (1)), and let them only run inside your Access form! This means they're running in the "client" side.


    Many Thanks & Best Regards, Hua Min



    Saturday, May 27, 2017 8:25 AM
  • I read the link for Updateable Views and I haven't breached any of those rules in this view.

    The first link of Modify Data Through a View doesn't apply to what I'm trying to do.

    That explanation had queries that changed data in a table, which isn't the same thing I'm trying to do.

    Thursday, June 8, 2017 11:36 PM
  • I need for most of the work to be on the SQL side due to business requirements.

    When I will use a client side query, it will be after SQL has done most of the work.

    Any suggestions would be appreciated.

    Thursday, June 8, 2017 11:37 PM
  • Actually it's not clear for me what you trying to achieve and what the Problem is. May can you provide some more details and also an example, please?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, June 9, 2017 6:06 AM
    Answerer