Forum Discussion
Unable to insert records in Linked table from MS Access to SQL Server
Hi,
I have created an MS Access project in 2007-2016 format. The OS is Windows 11. For POC purpose it is a simple form with ID as Autonumber and Name1 with short text fields. Now I am trying to link the MS Access to sql server table. For this I have selected ODBC driver 17 for sql server.
- First I exported table from ms access to sql server to create the table copy in sql server.
- Then added Primary Key in sql server table.
- Then I have right clicked table in MS Access and selected Linked Table Manager to select the table in sql server.
On adding new record and save in ms access form, the table in ms access is adding new records but sql server is not. Earlier while exporting the data was exported successfully but after linking table new records are not getting added.
Actually, there are two things to consider:
- When you linked the Access accdb to the SQL Server tables, were you asked to identify the Primary Keys for those tables? Did you select the appropriate field in the SQL Server tables?
- Also, you still have both the Access table AND the SQL Server table? Why is that? Is your form still bound to the Access table, not to the SQL Server table? If not, how do you expect the data to be added to the SQL Server table?
5 Replies
Actually, there are two things to consider:
- When you linked the Access accdb to the SQL Server tables, were you asked to identify the Primary Keys for those tables? Did you select the appropriate field in the SQL Server tables?
- Also, you still have both the Access table AND the SQL Server table? Why is that? Is your form still bound to the Access table, not to the SQL Server table? If not, how do you expect the data to be added to the SQL Server table?
George Hepworth Thanks for your reply. Yeah I noticed it later that the form was still bound to the Access table and not the new linked SQL table. I had assumed on saving in Access it would update both since it's 'linked'.
"Linked" only means, that Access can see the table.
Bind your form to the linked table, and the form will read and write to that table.
