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
261 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: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
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

830 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