By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,544 Members | 2,065 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,544 IT Pros & Developers. It's quick & easy.

Data entry continuous subfrom strategy

P: n/a
In a multiuser access/jet database, I need to provide multiple instance
order entry form/continuous subform. I have been looking for best
practice, but there seem to be competing ideas. Want to maintain
performance and prevent bloat.

1. Temporary tables in FE, post to BE when user commits. Use compact on
close to prevent bloat

2. Linked temporary tables in a local DB created/initialized on
startup/demand and compacted/deleted on close. No bloat in FE

3. Transactions ?? only if I use SQL BE?

4. Recordsets, need to program all display and edit of indivdiual rows????

Is there something else? I am not opposed to homework, so I would welcome
a reference recomendation if there are too many issues for a post.

(this may be a duplicate post, my first attempt cannot be found in site
search)

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

"Joe Cajon via AccessMonster.com" <fo***@AccessMonster.com> wrote in message
news:85******************************@AccessMonste r.com...
In a multiuser access/jet database, I need to provide multiple instance
order entry form/continuous subform. I have been looking for best
practice, but there seem to be competing ideas. Want to maintain
performance and prevent bloat.

1. Temporary tables in FE, post to BE when user commits. Use compact on
close to prevent bloat

2. Linked temporary tables in a local DB created/initialized on
startup/demand and compacted/deleted on close. No bloat in FE

3. Transactions ?? only if I use SQL BE?

4. Recordsets, need to program all display and edit of indivdiual rows????

Is there something else? I am not opposed to homework, so I would welcome
a reference recomendation if there are too many issues for a post.

Since you've done almost everything manually in this database already, why
not try making your own electricity?

Join our GROWING mailing list at:
ac***********@hotmail.com


Nov 13 '05 #2

P: n/a
Those are implied OR between the options, not implied AND.

--
Message posted via http://www.accessmonster.com
Nov 13 '05 #3

P: n/a
Joe,
If your backend database is access there are a couple things that will
enhance your performance tremendously.
1. In the front end and backend, set all the subdatasheets to none
(huge performance gain - code at the end)
2. if the user is entering data on the form set the forms data entry
property to true - so you don't pull un-needed records
3. If you can don't use the form filters to limit the form's datasets,
put criteria in the recordsource query. You can probably read directly
from the form forms!formname!controlname
4. I have never found that removing and recreating linked tables helped
significantly with fe bloat
5. If you have an sql BE, you have a host of other options for
performance, passthrough queries, views, and stored procedures. Most
of the time, processing that you can do on the server end will be
faster than on the user end.
6. Controlling your own recordsets will often give you performance
gains, but you will spend more time then you can believe writing the
code to do that. You are better off doing what I suggested above, and
saving your direct recordset manipulation for when you really need it.
7. My Opinion - avoid temp tables if you can, they do cause extra
bloat, and you can't always depend on your users to compact regularly
I hope this helps you
Pachydermitis


Function TurnOffSubDataSheets()

Dim MyDB As DAO.Database
Dim MyProperty As DAO.Property
Dim propName As String, propVal As String
Dim propType As Integer, i As Integer

Set MyDB = CurrentDb

propName = "SubDataSheetName"
propType = 10
propVal = "[NONE]"

On Error Resume Next

For i = 0 To MyDB.TableDefs.Count - 1

If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then

If MyDB.TableDefs(i).Properties(propName).Value <> propVal
Then
MyDB.TableDefs(i).Properties(propName).Value = propVal
End If

If Err.Number = 3270 Then
Set MyProperty =
MyDB.TableDefs(i).CreateProperty(propName)
MyProperty.Type = propType
MyProperty.Value = propVal
MyDB.TableDefs(i).Properties.Append MyProperty
Else
If Err.Number <> 0 Then
MsgBox "Error: " & Err.Number & " on Table " _
& MyDB.TableDefs(i).Name & "."
MyDB.Close
Exit Function
End If
End If

End If
Next i

MsgBox "The " & propName & _
" value for all non-system tables has been updated to " & propVal & "."

MyDB.Close

End Function

Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.