VBA Access: How to link an updatable recordset to

Question

I retrieve data from a SQL Server through ADO and store it in a ADODB Recordset. Then, I link a FORM to this recordset. And then I use some controls (labels, textboxes, Data field) to show the information from the recordset, in an automatic way (control data source is set to the FORM/Recordset table name). And it shows the info correctly.

The problem comes because it DOES NOT update the changes (for example in the DATA control) into the recordset and/or in the SQL Server. The recordset has a SELECT with a lot of joins, and when I link the tables through ODBC the controls update the info into the SQL Server perfectly. But I CAN'T achieve the same result with recordsets. What am I doing wrong? Is it necessary to create an event (afterupdate or onclick etc) and make a separate query to update the changes into the SQL Server?

Currently it says that the form is read only because the "Unique table" property is not established.

Thanks

Answer 1

Microsoft Access’s single greatest strength is its ability to bring relational databases to the desktop. With its easy-to-use interface and Wizards, almost anyone can build simple, or even very elaborate database applications. Many of these Access applications eventually outgrow their humble origins and are “upsized” to SQL Server.

A common problem when using Access as a front-end to SQL Server is that the original application was never architected with SQL Server in mind. Most people run the Upsizing Wizard and discover that their forms and reports are now running noticeably slower than before and become frustrated. There are several reasons for this, but the biggest issue is Access’ lack of efficient record navigation. The most common way to navigate records in an Access form is to use the built in navigation buttons. Alternatively a combo box can be used to select or filter the records the user desires to see. Both of these methods are likely to return too much data, and the effect is magnified when the data is moved to SQL Server.

The AccessUI offers extremely efficient data navigation that can greatly reduce the amount of traffic between SQL Server and an Access front-end. By building the treeview dynamically, only the first level is built, then all subsequent levels are built on demand minimizing the data transfer. In addition, a developer can create unlimited treeviews with the AccessUI, so they can customize each user’s experience and keep them from wading through unwanted data. When a user selects a node in the Treeview a synchronized form can be loaded displaying only those records associated with that tree node.

The AccessUI does not support using an ADP as a front-end to SQL Server, but uses a traditional environment with an MDB front-end to SQL Server using ODBC connections. This approach is marginally slower, but offers a much quicker development environment. MDBs have the added advantages of offering both local and linked tables, the option to use local queries or SQL Server Views, ADO and DAO data access methods, and the use of SQL Server stored procedures and user defined functions.

Advantages of using SQL Server as a back-end include:
◾Scalability – Databases can be multiple terrabytes
◾Performance – With tuning, factor of 10 improvements can often be achieved
◾Reliability – SQL Server corruptions are very rare
◾Security – much more robust than Access User Level Security for tables
◾Backup - Automated backup and recovery processes

In addition, this is probably worth a read-through for you.

Pros and Cons of Access Data Project (MS Access front end with SQL Server Backend)

You should check this out too.

http://www.techrepublic.com/article/using-access-to-build-a-front-end-for-sql-server/

Answer 2

The easy and native method is to link the tables via ODBC, and then have a bound form using the linked tables and your query.

Of course, the query must be updateable if you wish to be able to alter and save data from the form.