Updatable Recordset of ODBC-Connected DAO (1 Viewer)

[keirnus]

Hello Access gurus.

I read an article of Banana regarding RecordSource and its Updatability.

Bound forms' recordsource and updateability

One distinct advantage ADO have over DAO, including ODBCDirect, is it enables us to pass a SQL string in the backend's native dialect and return a recordset that is fully updateable and thus can be bound forms, under certain constraints.

Source: http://www.access-programmers.co.uk/forums/showthread.php?t=172243

QUESTIONs:
Is it possible for DAO to do the same?
Can DAO return a Recordset that is updatable? If so, how?

Please take time to check the code I made:

Private Sub Form_Load_ForPosting()
 
    Dim ws As Workspace
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
 
    Dim sSQL As String
 
    sSQL = "SELECT Table1.Column11, Table1.Column12, Table2.Column21, Table2.Column22 "
    sSQL = sSQL & "FROM Table1 INNER JOIN Table2 ON Table1.Column11 = Table2.Colx;"
 
    Set ws = DBEngine.Workspaces(0)
    Set db = ws.OpenDatabase("MyAccessDB.mdb", , , "ODBC;DSN=DB_NAME;UID=;PWD=")
    Set rs = db.OpenRecordset(sSQL)
 
    'Set the form's Recordset property to the DAO recordset
    Set Me.Form.Recordset = rs
 
    Me.Form.AllowEdits = True
    'Dynaset = 0
    'Dynaset (Inconsistent Updates) = 1
    'Snapshot = 2
    Me.Form.RecordsetType = 1
 
    Me.Form.[Column12].Enabled = True
    Me.Form.[Column12].Locked = False
 
    'Data of Column12 can be displayed but cannot be edited :(
    'If db is using "(CurrentDb)", Column12 can (both) be displayed and edited
    'What did I miss?
    'If the SQL query cannot update the data, how can I make it possible to update the data?
 
End Sub

(so far)
My system CAN DO the ff:
[1] Execute SELECT Query
[2] Execute INSERT Query
[3] Execute UPDATE Query
[4] Execute DELETE Query
[5] Display data in a Datasheet SubForm

My system CANNOT DO the ff:
[1] Edit the data displayed in a Datasheet SubForm

It would be great if you guys could give time on this issue.

 

[Banana]

Just to be 100% sure... the query is this:

SELECT Table1.Column11, Table1.Column12, Table2.Column21, Table2.Column22 
FROM Table1 INNER JOIN Table2 ON Table1.Column11 = Table2.Colx;

I believe if you just omit the join and only focus on one table, it will be updatable. You can represent the other table in a subform. Just to be clear, though, this is what I'd do for a form with subform:

Set the main form's recordsource to this:

SELECT Table1.Column11, Table1.Column12
FROM Table1;

Set the subform's recordsource to this:

SELECT Table2.Column21, Table2.Column22, Table2.Colx
FROM Table2;

Set the subform container's properties to this:

LinkMasterField: Column11
LinkChildField: Colx

Then you have both tables displayed and updatable.

Look at Bob's screenshot explaining how the subform container works, and the fact you're actually seeing two forms, just that one is embedded in a container.

HTH.

 

[keirnus]

Just to be 100% sure... the query is this:

SELECT Table1.Column11, Table1.Column12, Table2.Column21, Table2.Column22 
FROM Table1 INNER JOIN Table2 ON Table1.Column11 = Table2.Colx;

I believe if you just omit the join and only focus on one table, it will be updatable. You can represent the other table in a subform. Just to be clear, though, this is what I'd do for a form with subform:

Set the main form's recordsource to this:

SELECT Table1.Column11, Table1.Column12
FROM Table1;

Set the subform's recordsource to this:

SELECT Table2.Column21, Table2.Column22, Table2.Colx
FROM Table2;

Set the subform container's properties to this:

LinkMasterField: Column11
LinkChildField: Colx

Then you have both tables displayed and updatable.

Look at Bob's screenshot explaining how the subform container works, and the fact you're actually seeing two forms, just that one is embedded in a container.

HTH.

Thanks Banana.

One table in a RecordSource query really works.
I guess that is the trick to make it work.

Now, my next obstable is displaying retrieved multiple data.
I used Datasheet view so that multiple data can be viewed at one glance.

If the data to be displayed is just one, the trick is easy.
I just followed Bob's technique.

But if the data to be displayed is more than one, haven't got this yet.

I'm having an error when I set these fields.

Set the subform container's properties to this:

LinkMasterField: Column11
LinkChildField: Colx

Error I am having:

Subform Field Linker
Can't build a link between unbound forms.

So, I tried setting the ControlSource of the Child to be the same as the Master's.

Colx.ControlSource=[Forms]![MainForm]![SubForm].[Form]![Column11]

But what happened is all Colx values are the 1st value of Column11.

Instead of this

[Colx] ---- [Column11]
AA ------- AA
BB ------- BB
CC ------- CC

the result is this

[Colx] ---- [Column11]
AA ------- AA
AA ------- BB
AA ------- CC

It is supposed to display data respectively.

How to link the ControlSource of a field with multiple data?

By the way, the data are displayed in Datasheet view.

 

[Banana]

Now, my next obstable is displaying retrieved multiple data.
I used Datasheet view so that multiple data can be viewed at one glance.

If you want more than one datasheet, with that little "+", IIRC, you just drop in a subform inside the form that would be displayed as a datasheet.

If the data to be displayed is just one, the trick is easy.
I just followed Bob's technique.

But if the data to be displayed is more than one, haven't got this yet.

I think we're missing a important point here. The technique Bob illustrated is to enable display of *multiple* data source. You just create as many forms as you need to display the table (or more accurate the logical representation /relationship of various tables). There is no need to dump several tables into a single query and attempt to update that query. This kind of query is appropriate for reporting, but for form/data entry operations, we use a different approach.

Error I am having:

So, I tried setting the ControlSource of the Child to be the same as the Master's.

Colx.ControlSource=[Forms]![MainForm]![SubForm].[Form]![Column11]

Well, I forgot that you were basically setting recordsource of forms via code, which for reasons is still unclear to me. In this case, you would also set the linking in code because setting the property without a recordsource is too premature. A more simpler solution is to just toss the code and set the recordsource of both form and subform accordingly and it'll just work™.

How to link the ControlSource of a field with multiple data?

Wait, wait. I think this is something we've been missing here... Are you using Access 2007? Are you using "multi-valued fields"? Is that what you were talking about "multiple data"?

 

[keirnus]

I think we're missing a important point here. The technique Bob illustrated is to enable display of *multiple* data source. You just create as many forms as you need to display the table (or more accurate the logical representation /relationship of various tables). There is no need to dump several tables into a single query and attempt to update that query. This kind of query is appropriate for reporting, but for form/data entry operations, we use a different approach.

I kinda understand what Bob did. He was displaying one field from the SubForm "one at a time". Since the data are displayed one at a time, the linked data in the Textbox displays the same (one at a time).

What I am displaying in my Datasheet are table of data in X and Y matrix.
So, "all data" are displayed at once.

The only data displayed in my Datasheet which is from another table is the "Description". Thus, my query is "joined" and has 2 Tables in it (which won't allow me to edit the data in my Datasheet when using ODBC-DAO).

hmmmmmmmmmmmmmmm...

My deadline is near and I need to make a (temporary) workaround on this.

I tried using 1-Table Query and it really works as expected.
But not 2-or-more-Table Query.

I want to consult on something.
Would it be OK to include the "Description" field in the same table?
That way, I will have a 1-Table Query instead of 2-Table Query.

It may take time for me to modify the INSERT, UPDATE queries for this "Description" field but it is easier and faster for me.

But still, I am open for advices from the experienced.

Wait, wait. I think this is something we've been missing here... Are you using Access 2007? Are you using "multi-valued fields"? Is that what you were talking about "multiple data"?

I am using Access 2003.
What I mean about "multiple data" is the data displayed in my Datasheet-view SubForm are in X/Y matrix displaying "all data" at once.
The reason for this is to be able to edit the fields of the data in one display.

 

[Banana]

Normally, if I had a matrix of X/Y data... say a set of coordinates, I'm inclined to want to store them in a single table with two columns, XCoordinate & YCoordinate. There's no need to separate them out into two tables.

This is appropriate for say, Customer and Orders as a customer could have several orders, while a coordinate always has exactly one x-coordinate and exactly one y-coordinate.

I'm not sure why you separated out the Description but I'll venture to guess: It's a memo field and it's linked table os you want to save on overhead. In that case, I'd place a empty subform that loads the Description on demand when user click a certain button.

Otherwise, just put it in one table.

 

[keirnus]

I'm not sure why you separated out the Description but I'll venture to guess: It's a memo field and it's linked table os you want to save on overhead. In that case, I'd place a empty subform that loads the Description on demand when user click a certain button.

Actually, I am putting all "Description" in one table only.
It's like a Master table of an item with Description and ProdCode.
In the other table, there's only the ProdCode.
So, if there are any modifications of the Description of an item,
it should only be done in the Master table of items.

Now...in my Datasheet-view SubForm, I am displaying the ProdCode,
product description and other information (want it to be editable).
So, my query for RecordSource will have 2 Tables.
In the case of ODBC-DAO, we can't edit data coz it is not updatable, right? coz the query got more than 1 Table in it.

My thought:
Access should let users to update data if and only if updating one and only one table in multi-table query and the editable field(s) should not be the one linking/joining the tables.

Anyway, I was thinking of having 2 SubForms (visible and invisible).
The invisible SubForm will use multi-table query and (in my case) retrieve the Description fields of data.
The visible SubForm will use 1-table query and allows the user to edit/modify the data.
The description of the invisible and visible SubForms should be synchronized.
So, I was thinking of linking the "Description" Textbox only of visible SubForm to the one in invisible SubForm.
The "Description" Textbox of visible SubForm will depend on the invisible SubForm.
(At this point, Bob's technique of retrieving data will be used...if possible for mutiple data display)

I haven't tried it yet but will this work?
Are there better ideas on how to accomplish this?

Sorry if I'm new to this ODBC-DAO stuff.

 

[Banana]

What you are thinking is feasible; there are few different techniques to make this happens. One possible technique can be found at FMS Inc, in regards to "synchronizing subforms".

But!

I'm more concerned about the structure. Why a master table of description in first place? If you are capable of designing the structure and can modify it, I'd definitely want to do so as this will be less headache and heartache down the long road. This kind of structure might be OK for metadata such as audit trail but definitely not for where data does have relationship to some other piece of data (e.g. ProdCode in this case). I'm also unclear as to why you have another table that has ProdCode only; if that's the only column for that table, what use is it anyway?

 

[keirnus]

What you are thinking is feasible; there are few different techniques to make this happens. One possible technique can be found at FMS Inc, in regards to "synchronizing subforms".

But!

I'm more concerned about the structure. Why a master table of description in first place? If you are capable of designing the structure and can modify it, I'd definitely want to do so as this will be less headache and heartache down the long road. This kind of structure might be OK for metadata such as audit trail but definitely not for where data does have relationship to some other piece of data (e.g. ProdCode in this case). I'm also unclear as to why you have another table that has ProdCode only; if that's the only column for that table, what use is it anyway?

I think you are right.
Guess I need some learning on DB Structure.

The 1st Table is for product details.
Examples are Description, Weight, Height, Color, etc.
The 2nd Table is for item transaction details.
Examples are Estimated Time of Departure (ETD), ETA, ATD, ATA, etc.

The data in 2nd Table will be displayed for view and updating the data.
The Description is necessary for view only. That's why the query got 2 Tables.

Then again, I need to think for better available options.

Anyway, thanks Banana for the info.
Now I know that JOINED query in RecordSource of a Form causes headache.
It is the reason why I wasn't able to update the RecordSet in the 1st place.
The solution is to use 1-Table query and edit the fields.
For the difficult fields, do the magic trick.

If there are better ways to do it, the Access gurus here are glad to help and give advices.