Go Premium for a chance to win a PS4. Enter to Win

x
?
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
Medium Priority
?
274 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 2000 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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 

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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

783 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