MS Access Forms to Update/ Insert data in Sql Server 2005

Posted on 2009-12-22
Last Modified: 2013-11-28
Hi I have my Data base on Sql Server 2005 and My data Base Constitutes of Some 20 Tables. Now I have to Design a Form(s) in MS Access 2007 to insert/ Update the data into the Database. I Can any one suggest a  good Material or Some Links or any idea on Doing this task. Thanks in Advance..

My Requirement is Like

1) User Wants to Select a table From the Drop Down List
2) User Wants to Select the Keyvalue  in that Table
3) So that all the Information that was there for that key record should be Shown
4) User wants to Modify/ Change the record
5) User has to Update the Record.
What all I have to do is to create a Form/ Macro/Design a Page Where user wants to do all these in Access and the Data will be in Sql server.
Question by:KuldeepReddy
    LVL 46

    Accepted Solution

    LVL 84

    Assisted Solution

    by:Scott McDaniel (Microsoft Access MVP - EE MVE )
    To be a bit more in detail:

    You can connect your Access database to a SQL Server in various ways. The recommended method is to use Linked Tables. In most cases, it's better to build the tables directly on the server using SQL Server Management Studio (SSMS), and then use File - External Data - Link Tables to build the links to those tables.

    Once you do that, the tutorials suggested by Gary should get you started.  Note also that is you are using Bound forms (that is, your form is built based on a table, and all your controls have a ControlSource listed), then Access will handle the updating and such of the data - you don't have to do anything.

    I'm not sure about your concept of 'select a table' ... generally, tables in a relational database are not interchangeable, which means you need a different form for each table/query. If you have multiple tables with identical structures, then you have a design flaw.

    Please further clarify what you mean by "select a keyvalue" ...

    Author Comment

    I have Linked my Sql Tables in Access using Linked Tables, How ever the problem is that I have 25 tables in sql server that needs to be Updated and the Column names that needs to be updated in these 25 tables are the same. Is there a way That I can Design a Form Like

    1) Select a table from a List Of available tables.
    2) Update the required info

    Key value here is nothing but a value or an Id column in that particular table to select a record from that table to Update the Info.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
    Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now