Solved

I ll like to know how to update,delete,and add records from an Access form using a linked table to SQL Server. Thanks

Posted on 2014-11-27
6
253 Views
Last Modified: 2014-11-27
I ll like to know how to update,delete,and add records from an Access form using a linked table to SQL Server. It might be through a stored procedure or pass through query. I have a user friendly front end Access form which supports an SQL server database. i need to be able to query, update, delete records all through the access form. For linked tables, the traditional methods for manipulating records like delete does not work. Thanks
0
Comment
Question by:peterslove
  • 3
  • 2
6 Comments
 
LVL 26

Accepted Solution

by:
Nick67 earned 500 total points
ID: 40469557
For linked tables, the traditional methods for manipulating records like delete does not work.
Au contraire.
They work just fine, BUT you must have your ducks in a row.
The linked tables MUST have a TimeStamp field in each and every table you wish to manipulate.
Linked tables without timestamp fields are read-only.
Linked tables without a primary key are also read-only

If you moved the data from Access to SQL Server, the (by-far) preferred method to do so is via the SQL Server Migration Assistant for MS Access (SSMA).  YOU MUST use the version of that utility that matches the bit-ness of Access.  32-bit Access --> 32 bit SSMA.

Once you have keys and timestamp fields, all traditional data manipulation methods work.

ADP's -- which are now deprecated -- are a completely different matter
0
 
LVL 18

Expert Comment

by:SimonAdept
ID: 40469558
All those operations should work if you have appropriate permissions on the SQL server back-end database AND the tables have unique indexes on them.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40469565
In VBA code, any recordset setups
Dim rs = db.OpenRecordset("Some SQL", dbOpenDynaSet) must be amended
Dim rs = db.OpenRecordset("Some SQL", dbOpenDynaSet, dbSeeChanges)  is the new syntax

If you were sloppy and tested like so

If SomeBoolean = -1 then
'code
End if

You must change that to
If SomeBoolean = True then
'code
End if

Most everything else holds up pretty good.
SSMA will add the timestamp fields if you use it
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

Author Comment

by:peterslove
ID: 40469571
The table has primary keys and i have full previliedge in SQL server to delete, update and add records, but when i link the table in MS Access, all the update or add records dont exist and i cannot change anything on the table. I linked table using ODBC
0
 
LVL 26

Expert Comment

by:Nick67
ID: 40469590
You MUST have timestamp fields!timestamp.jpgYou must go into SQL Server and create them, and then in Access, use the linked table manager to refresh the linked tables
0
 

Author Closing Comment

by:peterslove
ID: 40469600
worked fine now thanks. I missed defining the primary key
0

Featured Post

Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

Join & Write a Comment

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

760 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

17 Experts available now in Live!

Get 1:1 Help Now