asked on
I ll like to know how to update,delete,and add records from an Access form using a linked table to SQL Server. Thanks
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
Microsoft SQL ServerMySQL ServerMicrosoft Access
Last Comment
peterslove
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
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.
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
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
ASKER
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
You MUST have timestamp fields!![timestamp.jpg]()
You must go into SQL Server and create them, and then in Access, use the linked table manager to refresh the linked tables
You must go into SQL Server and create them, and then in Access, use the linked table manager to refresh the linked tables
ASKER
worked fine now thanks. I missed defining the primary key