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
259 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
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

 

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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

Suggested Solutions

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…

810 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