[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


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

Posted on 2009-12-22
Medium Priority
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

tbsgadi earned 1000 total points
ID: 26110931
LVL 85

Assisted Solution

by:Scott McDaniel (Microsoft Access MVP - EE MVE )
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1000 total points
ID: 26111728
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

ID: 26131525
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.

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

873 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