Help with ADO open method best practice

[duluter]

Hi.

In my VBA modules, I connect to my data through ADO. I would like some feedback on the way I do things to see if I could be doing them "better".

Background:
The database is used in a small business, with usually no more than one person accessing the data at a time. Occassionally, there may be up to three people working with the data, but that would be unusual and they almost certainly would be working with unrelated records.


Here's some typical lines from my VBA:

Dim rs1 As ADODB.RecordSet
Set rs1 = New ADODB.RecordSet
rs1.Open "[...sql query goes here...]", CurrentProject.Connection, adOpenStatic, adLockOptimistic
...
...
rs1.close
set rs1 = nothing


I've read about the CursorType and LockType parameters, but the descriptions don't seem to mean that much to me, I'm afraid.

I typically use adOpenStatic. Does it matter what type of cursor I use in my environment? Because I usually open recordsets with SQL statements, do I have to worry about changes that other users may have made to the data?

For my LockType, again, in my environment, does it matter which one I use? For recordsets that I'm only reading from, and not updating or adding to, would it be better to use adLockReadOnly?


BONUS QUESTION:

Here's how I generally would add a record to a table via ADO. Please advise because I have a feeling that I am being stupid:

Set rs1 = New ADODB.RecordSet

'Sometimes I might throw a WHERE clause in here to reduce the number of records returned.
rs1.Open "SELECT FarmName, FarmDescription, Acreage, PrimaryStateID, PrimaryCountyID, ClientID FROM tblFarm", CurrentProject.Connection, adOpenStatic, adLockOptimistic

rs1.AddNew

With rs1

'Add data to each field in the new record.
![FarmName] = Trim(Me.txtFarmDataName.Value)
![FarmDescription] = Trim(Me.txtFarmDataDescription.Value)
![Acreage] = Trim(Me.txtFarmDataAcreage.Value)
![PrimaryStateID] = Me.cmbFarmDataState.Column(0, Me.cmbFarmDataState.ListIndex)
![PrimaryCountyID] = Me.cmbFarmDataCounty.Column(0, Me.cmbFarmDataCounty.ListIndex)
![ClientID] = Form_frmFarms.ClientIDF

.Update

End With

rs1.Close
Set rs1 = Nothing



Any tips would be well received. And I give you advance permission to make fun of me, if you want to.


Duluter

 

[LPurvis]

Hi there.
I assume you're using an MDB (ACCDB) linked to another MDB file (as opposed to a SQL Server or other RDBMS back end)?
(If you're not operating in a split data environment then that's something to address - the only FAQ I know for that is found over here.)

Though you might imagine you're opening a recordset with a Static type cursor using
rs1.Open "[...sql query goes here...]", CurrentProject.Connection, adOpenStatic, adLockOptimistic
that you're using CurrentProject.Connection means that your connection is going to be server side - and since not specified in your code that your recordset cursor is set to be clientside then you'll not get a Static cursor but a KeySet instead.
ADO silently replaces properties to values it can support for a given request.
In this case you can't open a Static cursor (with non-readonly locking) on a server side cursor.
So ADO returns a Keyset instead.
Similarly, if you had specified a client side cursor, then you'd have been able to acquire nothing but a Static cursor type (no matter what other options you selected!)

So... does this matter?
Chances are you won't see any difference.
If your recordset is particularly large (at least 10's of thousands of rows) then yes loading all those rows into the local cache would have a nociable overhead (and hence a Static cursor isn't what you want - and ending up with a KeySet is a good thing).
You say other users aren't really a concern. Since you're not dealing with open forms here, but apparently immediate recordset updates, then your contention is likely to be low (obviously "likely" is a dangerous word at times ;-)
But a Keyset will partially reflect the behaviour of other users actions on the database - as only the key of the recordset is held in your local recordset service - the rest of the data fetched as required, hence other users actions can have an effect on what you're doing - for example deletion of records).

Ultimately - I think you'll be just fine, though I'd suggest that you might as well request the type of cursor you're actually getting anyway...
rs1.Open "[...sql query goes here...]", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Onto your middle question (yes not the bonus one yet!)
The locktype does change things considerably.
If you're not performing any updates then there is no reason at all to open an updatable recordset.
Indeed, depending upon what you're doing with the recordset then you might request a Forward Only recordset too - and hence be as efficient as possible.
In a Jet backend scenario you're only going to get a ForwardOnly recordset type if you request a Read Only lock type. But you need to be sure that the type of cursor is appropriate (i.e. will one pass through the recordset be enough)?

This then raises the question, what are you doing? How big is the recordset?
You mention you're opening a query - so not a full table? Good.
How limited it it? Obviously only one record and you can be as efficient as can be. A small footprint and nowhere to navigate to within it anyway ;-)
Fetching anything more than you intend to use is pointless, wasteful and can harm concurrency.

This then brings us on to your "bonus" question.
There's nothing fundamentally wrong with adding a record through a recordset.
Many will argue it should be done through an INSERT SQL statement - and there's a good case for that.
But if your needs are complex (and you're not confident with SQL) then it can help the maintainability of your coding to do the append in a recordset - for example you don't need to worry about datatype delimiters etc.

In your recordset insert example, you open an unfiltered table.
This is the situation I was describing as non-ideal previously, and in the case of when you're appending records is especially wasteful. You don't intend to do anything with the table full of data you've just loaded - so why load it?
"SELECT FarmName, FarmDescription, Acreage, PrimaryStateID, PrimaryCountyID, ClientID FROM tblFarm WHERE 1=0"
gives you an immediately empty recordset.
Whereas DAO supports an append only recordset, ADO only does so under very specific circumstances (you need a client side cursor for a start).
Using an "empty" SQL statement as above gives you effectively the same concept - fetching no records so as to only append new ones.

And again - it's worth specifying the correct type of recordset (adOpenKeyset in this case).

Cheers.

 

[duluter]

LPurvis:

Wow. Nice, detailed response. You've given me a lot to think about.

Yes, my database is split into a front and back end (2 MDBs), with linked tables.

"Since you're not dealing with open forms here, but apparently immediate recordset updates"
That is correct, all of my forms are unbound. When the user does something on the form, all the work is done in the form's VBA module. I open the recordset, do what I need to do, then close it.

Because the business is so small, I really do not think that there will be problems if I use an adOpenKeyset cursor, as you suggest.

Regarding the LockType:

"If you're not performing any updates then there is no reason at all to open an updatable recordset."
That is entirely reasonable. I will use adLockReadOnly when I'm simply populating my form with values. I will use one of the updateable recordset locks when I am updating or adding records.

"You mention you're opening a query - so not a full table? Good."
I believe so, yes. All my ADO open statements use sql SELECT statements. I don't ever just use the name of the table as my parameter--I always use an SQL statement.

The size of the returned recordset varies depending on what I'm doing. But generally I'm only requesting a few rows at a time from the database because mostly I'm just getting values to populate my forms, such as the names of all the farms registered to a particulat client. We're talking 20-30 records at a time. But in one form, I might request the names of all the clients, which may be a couple hundred to a few thousand records. I'm not sure what this has to do with what CursorType or LockType I use though. Could you clarify?


Regarding my bonus question:

"There's nothing fundamentally wrong with adding a record through a recordset.
Many will argue it should be done through an INSERT SQL statement - and there's a good case for that."
Maybe I should try Googling this to see the different points of view. Do you know of any particularly good threads on this issue?

"SELECT FarmName, FarmDescription, Acreage, PrimaryStateID, PrimaryCountyID, ClientID FROM tblFarm WHERE 1=0"
Yes, I've done something similar in the past. I wasn't sure if this was a great way to do it or not.


If anyone else has anything to add, I'd be happy to hear additional comments.


Thanks,

Duluter

 

[LPurvis]

>> I believe so, yes. All my ADO open statements use sql SELECT statements.

That's fine - but the use of a SQL statement itself over a table name isn't what I was wondering about - but that you use a statement which limits the results being returned.
It seems that you do that when appropriate though.


>> I'm not sure what this has to do with what CursorType or LockType I use though. Could you clarify?

I was explaining that the cursor used affects whether the recordset's cache is fully loaded locally with records. If you have many thousands of rows and have a client side, static, cache then your recordset has all that overhead to process filling that cache (when you may or may not be visiting all rows - indeed with a very large recordset it's surely fairly unlikely you'll visit every one).
The Keyset that you're getting is a good workhorse, and a client side static is an option when it's appropriate (as it can come with some overhead side-effects).


Re: should be done through an INSERT SQL statement
>> "Maybe I should try Googling this to see the different points of view"

Not especially necessary unless you desperately want to - as I wasn't particularly giving you my viewpoint, just a viewpoint.
The consensus of opinion (you can trust me on this or not) is that updates and inserts are best performed through SQL statements.
I hold to this too.
However that doesn't mean there's anything wrong with performing them through recordsets. It's just not necessarily advisable if you're reaching for the maximum performance you can possibly acquire (usually most of an issue in Client Server development).
The SQL statement can be executed more quickly by the database engine than the API processing of the recordset (which has to wrap up into an update statement itself beneath the covers).

You mention occasionally fetching several rows.
Do you update those and then send individual updates in the recordset to the database? If so then a BatchUpdate recordset lock type would likely be of use to you.
Again it wraps up updates into SQL statements (the number of which depends upon the amount of rows to update) - but is designed for much of the purpose you're describing.

Bear in mind that many would ask you why you're using unbound forms at all - when Access' default binding offers much of its advantage in RAD over other environments.
Do you unbind everything as a matter of course - or on a case by case basis when it's justified?


Re: WHERE 1=0
>> I wasn't sure if this was a great way to do it or not

There's nothing wrong with it - and it is largely the way to return an empty recordset (I'm refering here to the "impossible" criteria concept rather than that particular example).
You could try, as some do, for a condition that is equally impossible (or just very unlikely), such as
SomeFieldID = -1
where SomeFieldID is an incrementing (autonumber) PK field that was seeded at 1 (or zero). Obviously that isn't completely reliable as it's entirely possible for an autonumber value to be -1 (just comparatively unlikely).
However I've known it have performance advantages over a literal expression such as 1=0 (as the engine is making use of the primary key index to make the fast selection of zero rows).


>> If anyone else has anything to add...
Indeed - go for it folks.

 

[duluter]

Leigh:

Thanks again for the further clarifications.

>>"The consensus of opinion (you can trust me on this or not) is that updates and inserts are best performed through SQL statements."

Would this be through CurrentDB.Execute? Is that the way to best perform inserts and updates through SQL statements?


>>"Bear in mind that many would ask you why you're using unbound forms at all - when Access' default binding offers much of its advantage in RAD over other environments. Do you unbind everything as a matter of course - or on a case by case basis when it's justified?"

I think I decided on unbound forms because my top priority is providing the user with exactly what they need in order for them to work efficiently. If I was better at Access, I could probably come up with a scheme that met this goal and also used bound forms, but I was more comfortable with code than with the Access front end, so I decided to use unbound forms. With unbound forms I can do anything I can think of doing. With bound forms I have to figure out how to make my vision fit into what Access will allow me to do. Does that make sense?


>>"I've known it have performance advantages over a literal expression such as 1=0 (as the engine is making use of the primary key index to make the fast selection of zero rows)"

That's a very useful tip.


>>"ADO silently replaces properties to values it can support for a given request. In this case you can't open a Static cursor (with non-readonly locking) on a server side cursor."

This was also a great tip from your first post.


Thanks very much for all your suggestions.


Duluter

 

[LPurvis]

>> Would this be through CurrentDB.Execute?
Well - the SQL statements need to be executed somehow and that's certainly the standard way. (Not necessarily using CurrentDb but some DAO database object).
You could use ADO (CurrentProject.Connection.Execute) still which your recordset preference seemed to be.
It should be pointed out that, with Jet data, ADO doesn't usually quite match DAO's performance. (It's usually a very, very subtle difference if at all - but worth mentioning).

Obviously there are Access methods, like DoCmd.RunSQL, which are usually avoided by developers - though not always with good reason.


>> With unbound forms I can do anything I can think of doing.
Except continuous or datasheet forms of course ;-)
I don't disagree that unbound forms offer some advantages. I use them when I deem it appropriate - though if I used them exclusively then I'd possibly price myself out of much work (if I don't already <shrug> lol) due to the extra time they usually require.
I don't even hold to the axiom that if you use unbound forms exclusively then you should be using Visual Studio or some other development environment. I personally do think Access has much more to offer than just it's convenient default binding functionality - but it is a big advantage, especially from a development time viewpoint.

>> That's a very useful tip.
NP, just don't take it as a rule of thumb. Ordinarily they should be much of a muchness. But at times the optimiser might have too much to do already, and it's then likely easier to include another index prioritisation into the mix than a literal expression - albeit an "impossible" one.

Cheers!

 

[April15Hater]

I wanted to stick my head in on this one to comment and ask another question.

My philosophy thus far has been fairly similar to duluter and have found that bound is a bit hard to work with. Then again, I have not come to a point in which I need a continuous or datasheet forms. I did however, have to bite the bullet once I started making reports. That being said, I find it easier to program vb to return what I want rather than to form an SQL statement as a recordset to do what I want (So I just avoid reports like the plague).

With all of that in mind, my forms are becoming fairly complex (in my own little newbie terms), and I'm finding myself pulling sometimes 3 and 4 ADO recordsets at a time just on one procedure. My question: Is there anything wrong with running that many at a time? In other words, am I going to get to a point in which after implementation, the queries become so burdensome that it just takes forever to run? I posted a code example below of where I will be using 4. Just for some background, I have about 40 users that will be accessing the back end (not all at once[wipes sweat off forehead]) so I will certainly have more than 10,000 rows. I do make use of the cursor and lock types, but they only seem to be subtle differences at this point.

Thanks,

Joe

Private Sub Form_Load()
Dim rsProduction As ADODB.Recordset
Dim rsSubFunction As ADODB.Recordset
Dim rsFormSetup As ADODB.Recordset
Dim rsAppendTracking As ADODB.Recordset
If IsNull(Me.OpenArgs) = True Then MsgBox "Error! No ProductionID assigned.", vbCritical: DoCmd.Close acForm, "frmProductionStep3b": Exit Sub
Set rsProduction = New ADODB.Recordset
Set rsSubFunction = New ADODB.Recordset
Set rsFormSetup = New ADODB.Recordset
Set rsAppendTracking = New ADODB.Recordset
With txtProductionID
    .Value = Me.OpenArgs
    .Enabled = False
End With
With txtFunctionTrack
    .Enabled = False
End With
With rsFormSetup
    .ActiveConnection = CurrentProject.Connection
    .Source = "SELECT tblFunction.FunctionID, tblFunction.JobID, tblProductionInput.ProductionID, tblFunction.[Function Name], " _
            & "tblProductionTracking.TrackingNumber, tblProductionInput.Status, tblFunctionTracking.FunctionTrackingID, " _
            & "tblFunctionTracking.Hierarchy, tblFunctionTracking.TrackableItem " _
            & "FROM (tblFunction INNER JOIN (tblProductionInput INNER JOIN tblProductionTracking ON " _
            & "tblProductionInput.ProductionID = tblProductionTracking.ProductionID) ON " _
            & "tblFunction.FunctionID = tblProductionInput.FunctionID) INNER JOIN tblFunctionTracking ON" _
            & "(tblFunctionTracking.FunctionTrackingID = tblProductionTracking.FunctionTrackingID) AND" _
            & "(tblFunction.FunctionID = tblFunctionTracking.FunctionID) " _
            & "WHERE tblProductionInput.ProductionID = " & Me.OpenArgs
    .CursorType = adOpenDynamic
    .LockType = adLockReadOnly
    .Open
    'Disable FunctionID Dependent Text Boxes that have no record in tblContractorFunction
    Call ClearFunctionDependent(CStr(!FunctionID))
    'Populate various items on the form
    txtProductionID = !ProductionID
    txtFunctionTrack = !TrackingNumber
    lblFunctionTrack.Caption = FindFunctionTrack(!FunctionID, 2)
    lblFunction.Caption = ![Function Name]
    fmeStatus = !Status
    With rsProduction
        .ActiveConnection = CurrentProject.Connection
        .Source = "TRANSFORM Sum(tblProductionInputDetail.ProductionUnits) AS SumOfProductionUnits " _
                & "SELECT tblProductionInputDetail.ProductionID " _
                & "FROM tblContractorFunction INNER JOIN tblProductionInputDetail ON " _
                & "tblContractorFunction.ContractorFunctionID = tblProductionInputDetail.ContractorFunctionID " _
                & "WHERE (tblContractorFunction.FunctionType = " & Chr(34) & "Aerial" & Chr(34) & " OR " _
                & "tblContractorFunction.FunctionType = " & Chr(34) & "Underground" & Chr(34) & " OR " _
                & "tblContractorFunction.FunctionType = " & Chr(34) & "Unit" & Chr(34) & " OR " _
                & "tblContractorFunction.FunctionType = " & Chr(34) & "setup" & Chr(34) & ") And " _
                & "tblProductionInputDetail.ProductionID = " & Me.OpenArgs & " " _
                & "GROUP BY tblProductionInputDetail.ProductionID " _
                & "PIVOT tblContractorFunction.FunctionType;"
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open
        'Populate Aerial Footage Textbox
        If ProductionDetailExists(ContractorFunctionIDLookup(rsFormSetup!FunctionID, "Aerial"), Me.OpenArgs) Then
            With txtAerial
                .Value = rsProduction!Aerial
                .Enabled = True
            End With
        End If
        'Populate Underground Footage TextBox
        If ProductionDetailExists(ContractorFunctionIDLookup(rsFormSetup!FunctionID, "Underground"), Me.OpenArgs) Then
            With txtUnderground
                .Value = rsProduction!Underground
                .Enabled = True
            End With
        End If
        'Populate Unit Footage TextBox
        If ProductionDetailExists(ContractorFunctionIDLookup(rsFormSetup!FunctionID, "Unit"), Me.OpenArgs) Then
            With txtUnit
                .Value = rsProduction!Unit
                .Enabled = True
            End With
        End If
        'Define rowsource for cboTrackableSel
        With Me.cboTrackableSel
            .RowSource = "SELECT tblFunctionTracking.FunctionTrackingID, tblFunctionTracking.Hierarchy, " _
                & "tblFunctionTracking.TrackableItem, tblFunction.JobID " _
                & "FROM tblFunction INNER JOIN tblFunctionTracking ON tblFunction.FunctionID = tblFunctionTracking.FunctionID " _
                & "WHERE tblFunctionTracking.Hierarchy < " & rsFormSetup!Hierarchy & " AND " & "tblFunction.JobID = " & rsFormSetup!JobID
        End With
        
        .Close
    End With
    ActivateSubFunctions (!FunctionID)
End With

'Populate SubfunctionData
With rsSubFunction
    .ActiveConnection = CurrentProject.Connection
    .Source = "SELECT qtrProduction.ProductionID, qtrProduction.FunctionType, qtrProduction.ProductionUnits " _
            & "FROM qtrProduction " _
            & "WHERE ProductionID = " & Me.OpenArgs & " AND " _
                & "FunctionType <> " & Chr(34) & "Aerial" & Chr(34) & " AND " _
                & "FunctionType <> " & Chr(34) & "Underground" & Chr(34) & " AND " _
                & "FunctionType <> " & Chr(34) & "Unit" & Chr(34) & " AND " _
                & "FunctionType <> " & Chr(34) & "Setup" & Chr(34)
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open
    If Not .EOF Then
        Do
            If !FunctionType = "House Count" Then
                With Me.txtHouseCount
                    .Enabled = True
                    .Value = rsSubFunction!ProductionUnits
                End With
            End If
            If !FunctionType = "Commercial Count" Then
                With Me.txtCommercialCount
                    .Enabled = True
                    .Value = rsSubFunction!ProductionUnits
                End With
            End If
            If !FunctionType = "MDU Count" Then
                With Me.txtMDUCount
                    .Enabled = True
                    .Value = rsSubFunction!ProductionUnits
                End With
            End If
            .MoveNext
        Loop Until .EOF
        .MoveFirst
    End If
    For IntX = 1 To 13
        If Not .EOF Then
            If !FunctionType <> "House Count" And !FunctionType <> "Commercial Count" And !FunctionType <> "MDU Count" Then
                Me.Controls("lblSubFunction" + Format$(IntX)).Caption = !FunctionType
                Me.Controls("txtSubFunction" + Format$(IntX)).Value = !ProductionUnits
                .MoveNext
            Else
                With Me.Controls("lblSubFunction" + Format$(IntX))
                    .Visible = False
                    .Caption = "SubfunctionType" & IntX
                End With
                With Me.Controls("txtSubFunction" + Format$(IntX))
                    .Enabled = False
                    .Visible = False
                    .Value = Null
                End With
            End If
        End If
    Next IntX
End With
'''''''APPEND QUERY''''''''
With rsAppendTracking
    .ActiveConnection = CurrentProject.Connection
    .Source = "INSERT INTO tblProductionStep3blstAssigned " _
            & "SELECT ProductionTrackingID, FunctionTrackingID, TrackingNumber, ProductionID " _
            & "FROM tblProductionTracking " _
            & "WHERE ProductionID = " & Me.OpenArgs
    .CursorType = adOpenDynamic
    .LockType = adLockBatchOptimistic
    .Open
End With
Me.cboTrackableSel.Requery
End Sub

 

[LPurvis]

It's hard to offer an opinion with any knowledge of the overall objective (or even with a little knowledge really)...

Opening four recordsets to complete a task isn't a problem per se - unless the task doesn't need them.
If it's complex enough to deserve such resources - then there's no problem.

What about binding are you finding particularly problematic?
Are you just displaying data to the user? Allowing updates? (Presenting pivoted data implies that it isn't being updated...)

As far as making "use of the cursor and lock types" - it's not really a question of making "use" of them, they're aways there. It's a case of appropriate selection.
For example - in your example code, if you're connecting to SQL Server data, then you're using a adOpenDynamic cursor type. This is the most functional and memory hungry type. If you use it, it should be for a good reason. If you're using this against a Jet BE (MDB/ACCDB) then you'll not be getting a Dynamic cursor as the Jet OLEDB provider doesn't support the adOpenDynamic cursor at all. So ADO will perform its silent substitution and return to you a Keyset instead.
There's nothing wrong with this really, and as you point out - there's only minimal performance advantage to be rung out of such choices with limited size recordsets...
(I say limited size as, even though you mention 10,000 rows - which isn't particularly a lot - you're also using SQL statements to open your recordset with criteria to limit the results, which is exactly what you should be doing even though you say earlier
"I find it easier to program vb to return what I want rather than to form an SQL statement").

So I don't really have any advice to offer - due to a lack of inside knowledge of the requirements. But binding isn't the devil - reports certainly aren't the devil - they're one of Access' best features!
And for those who start working unbound, it may feel like a safety blanket that you don't want to let go of... But you're missing out on much of the convenience, speed of development, and functionality that Access has to offer by avoiding it as a matter of course - rather than considering each scenario on its own merits.

Cheers.

 

[April15Hater]

(Presenting pivoted data implies that it isn't being updated...)

This is precisely where my problem lies. Much of what my forms are doing would require the pivot setup. For example, I have a form to setup the different functions that our in-house employees (CAD engineers) can do for a particular job. The employees get paid not on the function, but on one of the three subfunction rates: Aerial, Underground, and Unit.

I think an example may help make things clear: an employee can do say functions "design cad" or "strand cad" for Job A. Of those two functions they are getting paid on either the Aerial, Underground, or Unit rate listed in the subfunction table.

structure:
-tblFunction - FunctionID(P), JobID(F), FunctionName
-tblContractorFunction(subfunction) - ContractorFunctionID(P), FunctionID(F), FunctionType(Aerial, Underground, Unit), Cost

So since there is a subfunction record for each the Aerial, Underground, and Unit rates, I just have three textboxes and a seperate query for each with the WHERE clause either Aeiral, UG or Unit. Clear as mud? lol

 

[LPurvis]

Not entirely clear I'm afraid no.
I'm fully familiar with scenarios where normalised data means a bound form prevents a convenient UI for users to work with. (I've implemented several over time).

For example different pricing structured depending on some other factor than a simple item (i.e. a 2D grid that a spreadsheet typically and trivially models all the time - but isn't relational or normalised).

I think that's what you're getting at above.
But without familiarity with your application - it's hard to be sure.

Cheers.

 

[LPurvis]

For example - there's a themed example in the last (or is it second last) demo in the examples page linked to in my sig below.
(It's probably in one of the "Examples" links actually).

It has that kind of normalised data displayed non-bound for the user scenario.

 

[April15Hater]

I think you are right on target. The items aren't simple: all subfunction prices are negotiated per job therefore the employee can be doing the exact same function for 2 different jobs, yet there can be two different prices (and the prices are stored in the subfunction table). If I were to bind forms, I'd have to have one form where the user enters in first the function data, then another bound form for the user to enter in the subfunction data, and it would end up being to much work on the user side.

(i.e. a 2D grid that a spreadsheet typically and trivially models all the time - but isn't relational or normalised).

Maybe this is where I'm going wrong. This seems to be the most logical way to set it up, but I normalized everywhere possible so that things would be less complex, yet it seems like I made things way more difficult in the long run. So if I understand you correctly, there may be times when we don't want to normalize?

I'm gonna check out that example later this week. Taxes are coming in full speed now, argh!!! But i saw that it shows in the description that you can bind to an ADO recordset? That's something I'd definetely be interested in learning about.

 

[LPurvis]

There are times when we choose not to normalise.
They are rare occurances - and are for those experienced enough in having normalised dozens of applications to then take an overview of when it's appropriate to not do so.
This 2D grid of data is certainly not one of those times.
It can be represented nicely with an unbound form and a bit of coding effort.
Or represented for data entry on a less friendly form - but represented none the less.

The data is preserved in a normalised state and should remain that way regardless of UI concerns. It's up to the developer to determine ways (as inventive or standard as you like) to allow the user to interact with that data in the form.

I'll leave it with you to investigate when you have the time.
(Note the ADO recordset method is one way of doing this - but both it and the unbound form methods are offered in the example).

Cheers.

 

[April15Hater]

This 2D grid of data is certainly not one of those times.
It can be represented nicely with an unbound form and a bit of coding effort.

That is music to my ears. Glad I didn't miss the boat on that one. Maybe it's coincidental, but it seems as if the M-M relationships are where I start to run into the problems with binding forms. None-the-less, thanks for the advice! I can always count on taking away some new insight from the AWF posts.

Joe

 

[LPurvis]

Many to many relationships are traditionally handled in bound forms with a parent form, a subform (which is bound to the link table) and a combo in the subform (which is bound to the other many table).
This works fairly well for most data entry tasks.
But some situations offers a more intuitive UI using an unbound "grid" type form.

However Many to Many relationships don't generally make up the lion's share of an application's schema.
There are many scenarios where bound forms offer quick, simple and effective data entry of single entities.
IMHO unbinding every form is something of an endulgance unless you have a pre-created set of functions/classes that facilitate such development more rapidly.

Cheers.

 

[April15Hater]

Many to many relationships are traditionally handled in bound forms with a parent form, a subform (which is bound to the link table) and a combo in the subform (which is bound to the other many table).

Wow, that totally blew my mind. I never would have thought to do that.

There are many scenarios where bound forms offer quick, simple and effective data entry of single entities.
IMHO unbinding every form is something of an endulgance unless you have a pre-created set of functions/classes that facilitate such development more rapidly.

That's actually how I've found myself setting these up. I'll make a public function basically running a quick ado query that will return the value, or whether the record exists, etc.

 

[LPurvis]

>> Re Subforms and combos.
Then have a play with it. Knowing the options is what gives you power. Not a particular technique.

>> That's actually how I've found myself setting these up...
Yeah - I was meaning more a set of tools built up, possibly over years, that allow such development to happen almost without thought.
But every re-usable bit you do create is worthwhile.

Cheers.

 

[duluter]

April15Hater:

Sounds like you and I are peas in a pod. I'm coming to the realization that I took my strength (coding--a relative strength, mind you) and ran with that rather than fully learning all that Access can do for me with its built-in tools. By doing everything ourselves behind the scenes, we're probably wasting tons of time and energy. Speaking for myself, if I had just spent more time up front learning the Access paradigm (as an interface tool, not just as a place to store data), I might have more to show for myself at this point. Oh well, it's never too late to learn.


Duluter

 

[LPurvis]

If I could sum up one thing that Access offers which makes it comparatively powerful right out of the box (i.e. without coding - or at least very little coding) it would be "subforms".
Especially in continuous or datasheet view.

Sure they have their limitations - but what they offer is almost uniquely feature full.
The means to re-use the same form in several locations.
The means to show related data - and change the source of that related data depending on other factors.
A way of displaying bound, editable lists.

Obviously they're only one part of what you can do - but they alone offer much.
To avoid them is to lose out on some real benefits that you could be making use of (and a VAST majority of Access developers make use of).