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
268 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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:Simon
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
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…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

734 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