locked
Can Access 2007 Front-End forms Add, Delete, Modify fields in SQL Server 2008 Back-End table ? RRS feed

  • Question

  • Hello,

    Hoping you can clarify something for me as follows ...

    I am planning to implement the following architecture:

    Access 2007 Front-End Forms.
    SQL Server 2008 Standard Edition Back-End Tables.
    Desktop operating system is XP Pro SP2.
    Server Operating System is Windows Server 2003 Standard.

    My question is ...

    Will the Access 2007 Front-End forms be able to add, delete, or modify records in the SQL tables ?

    For example, one of the tables will consist of user data (First name, last name, etc.).  If I want to add a new user, edit a user's information, or delete a user using an Access 2007 Front-End form, will the SQL 2008 table be updated with the new information ?

    I've read the following aritcles stating the following:

    "When you link to a table or view in a SQL Server database, Access creates a new table (often referred to as a linked table) that reflects the structure and contents of the source object. You can make changes to data either in SQL Server, or in Datasheet view or Form view from within Access. The changes that you make to data in one place are reflected in the other. However, if you want to make structural changes, such as removing or changing a column, you must do so from within the SQL Server database, or from within an Access project that is connected to that database. You cannot add, delete, or modify the fields in a linked table while working in Access."

    http://office.microsoft.com/en-us/access/HA102004941033.aspx

    I look forward to your help !

    Jason

    Monday, November 30, 2009 7:10 PM

Answers

  • Hi Jason

    the article is refering to data changes and to database structural changes

    you'll be able to use the linked tables as if they were in access. ie you can insert, update, read and delete data

    you can't change the structure of the linked tables from access, if you need to alter tables you'll have to use sql server management studio

    hope that clears it up for you
    Tuesday, December 1, 2009 12:44 AM

All replies

  • Hi Jason

    the article is refering to data changes and to database structural changes

    you'll be able to use the linked tables as if they were in access. ie you can insert, update, read and delete data

    you can't change the structure of the linked tables from access, if you need to alter tables you'll have to use sql server management studio

    hope that clears it up for you
    Tuesday, December 1, 2009 12:44 AM
  • Thanks GT OZ,

    That helps A LOT !!

    Appreciate all your help.

    Best regards,

    Jason
    Tuesday, December 1, 2009 4:06 PM
  • Also, if you do change your structure of the linked tables, you'll have to either refresh them or delete them and add them in again. I believe however there is a setting that will check the schema upon loading but I'm not entirely sure.
    Tuesday, December 1, 2009 4:08 PM
    Answerer