"This Recordset is not updatable" Using ODBC and Multi-Table SQL Query (1 Viewer)

[keirnus]

Hello gurus,

I am stuck!

[1] I'm accessing the DB via ODBC.
[2] The SQL Query consists of multiple tables.
[3] Each table in the query has primary key.
[4] The query is used for the RecordSource of the SubForm.
[5] The RecordSetType of the SubForm is "Dynaset (Inconsistent Updates)".
I also made the RecordSetType of the MainForm as "Dynaset (Inconsistent Updates)".
[6] The AllowEdits of my SubForm and MainForm are set to "Yes".
[7] My SubForm is in Datasheet View so that I can directly edit the data.
[8] The editable fields in the SubForm are Enabled=Yes and Locked=No.

Now..................

I can easily do SELECT query.
No problem with this.
The data are displayed as expected.

But..................
I can't edit the data.
The status at the bottom says, "This Recordset is not updatable".
why?

Did I miss something?
How can I make the displayed data editable/updatable?

May a good samaritan Access guru gives attention on this matter.
Someone who can provide the panacea of this woe.

 

[Banana]

Suggest that you read this and that.

Short answer is that you really should be avoiding doing multiple-table query and doing several queries, binding each individual query to each form on one-one correspondence and you will find it'll just work.

 

[keirnus]

Suggest that you read this and that.

Short answer is that you really should be avoiding doing multiple-table query and doing several queries, binding each individual query to each form on one-one correspondence and you will find it'll just work.

Thanks Banana for the links.

I guess this is the cause of my problem.

It uses JOINs of different directions on multiple tables in the FROM clause. Remove some tables.

(dou shou kana)

How come things are OK when the DB is locally within the MS Access application but NG if using external DB?

If it is because of different drivers, my external DB is the same MS Access and should not be any different, right? Or maybe more settings are needed in ODBC?

(I am separating the DB from the application for future purposes)

 

[Banana]

Actually, I'm pretty sure the rule is same whether you're using ODBC or Access table. Do you have a practical example of where this is not the case?

While it is theoretically possible to bind a form to multiple-table query and have it updatable, it's quite problematic and for several reasons, it's best that there is only one table (or even better, a query based on a table) for each form (remember subform themselves are forms, so that can count as a separate query).

 

[keirnus]

Actually, I'm pretty sure the rule is same whether you're using ODBC or Access table. Do you have a practical example of where this is not the case?

While it is theoretically possible to bind a form to multiple-table query and have it updatable, it's quite problematic and for several reasons, it's best that there is only one table (or even better, a query based on a table) for each form (remember subform themselves are forms, so that can count as a separate query).

I think I mistakenly explained it in my last post. Sorry.

What I mean was I used DAO when the DB was within the Access.
I encountered the same problem with DAO but fixed it
by setting the Form's RecordSetType into "Dynaset (Inconsistent Updates)".

Recently, I was told to separate the DB from the application so that
there will be a Client application and a DB (in server) separately.
(This is easy for updating the Client app without affecting the data in DB.)

Because of that, I decided to use ADO and ODBC.

Now...if I edit my data using ADO-ODBC, the "This Recordset is not updatable" status is displayed even if the RecordSetType of the SubForm is "Dynaset (Inconsistent Updates)".

So, I'm guessing that setting the RecordSetType to Dynaset in ADO will not let me edit the data eventhough it works in DAO.

In my old app, all of my DB Access are in DAO.
Is there a way to separate a Client app and the DB without changing the DAO DB Connection?

If this is possible, I might not need to change everything to ADO and will not have a problem editing the data in the SubForm (in Datasheet view).

 

[Banana]

Well, ADO doesn't have "Dynaset (Inconsistent Updates)" setting at all. In fact, ADO operates pretty much differently from DAO.

Furthermore, if you're not doing something like this, you aren't in fact using ADO at all!

Private Sub MyForm_Load()

Dim rs As New ADODB.Recordset

rs.Open "SELECT * FROM aTable", "MyConnectionString"

Set Me.Recordset = rs

End Sub

Anything you create via Access UI are DAO, period. ADO can only be used via code, pretty much. Now, you should realize that DAO works just dandy with ODBC so you actually have no need to use ADO just because you're using ODBC. This is why I linked to the guide about ODBC in first place, which explains those things.

 

[keirnus]

Well, ADO doesn't have "Dynaset (Inconsistent Updates)" setting at all. In fact, ADO operates pretty much differently from DAO.

Furthermore, if you're not doing something like this, you aren't in fact using ADO at all!

Private Sub MyForm_Load()
 
Dim rs As New ADODB.Recordset
 
rs.Open "SELECT * FROM aTable", "MyConnectionString"
 
Set Me.Recordset = rs
 
End Sub

Anything you create via Access UI are DAO, period. ADO can only be used via code, pretty much. Now, you should realize that DAO works just dandy with ODBC so you actually have no need to use ADO just because you're using ODBC. This is why I linked to the guide about ODBC in first place, which explains those things.

Darn...I'm so noob on ODBC.

Thank you soooooo much, Banana.

You did enlighten my view on these DAO, ADO and ODBC stuffs.

I thought ODBC can "only" be implemented with ADO.
My mistake.
DAO can "also" use ODBC.

Here's what I researched minutes ago.
(It may help other noobs as well)

What Are DAO and ODBC?


Both Data Access Objects (DAOs) and Open Database Connectivity (ODBC) are APIs that give you the ability to write applications that are independent of any particular database management system (DBMS).

DAO is familiar to database programmers using Microsoft Access Basic or Microsoft Visual Basic. DAO uses the Microsoft Jet database engine to provide a set of data access objects: database objects, tabledef and querydef objects, recordset objects, and others. DAO works best with .mdb files like those created by Microsoft Access, but you can also access ODBC data sources through DAO and the Microsoft Jet database engine.

ODBC provides an API that different database vendors implement through ODBC drivers specific to a particular DBMS. Your program uses this API to call the ODBC Driver Manager, which passes the calls to the appropriate driver. The driver, in turn, interacts with the DBMS using SQL.

Note ODBC is a major part of the Microsoft Windows Open Standards Architecture (WOSA). DAO is optimized around the Microsoft Jet database engine, but you can still access ODBC and other external data sources with that engine, and the distinct ODBC API and the MFC classes based on it are still available and still have their role to play in your selection of database tools.

Source: http://msdn.microsoft.com/en-us/library/et1kh6d3(VS.80).aspx


DAO example - JET Connection

Dim ws as Workspace
Dim db as Database

Set ws=DBEngine.Workspaces(0)
set db=ws.OpenDatabase({databasepath and name})

DAO example - ODBC Connection

Dim ws as Workspace
dim db as database
dim strConnection as string

set ws=DBEngine.Workspaces(0)
let strConnection= "ODBC;DSN=" & DatabaseName & ";UID=" & UserName
& ";PWD=" & UserPassword
set db=ws.OpenDatabase("", False, False, strConnection)

ADO Example

Dim ad as ADODB.Connection

set ad=New ADODB.Connection
Let ad.ConnectionString= "ODBC;DSN=" & DatabaseName & ";UID=" &
UserName & ";PWD=" & UserPassword
ad.Open

Source of Samples: http://www.vbexplorer.com/VBExplorer/vb_feature/june2000/Database_Beginner_ADO_DAO.asp


Now, I know.

Thanks Banana for taking time on looking on my issue.
Rating you up now.

Cheers.