September 17, 2009 at 1:02 am
Hi,
I am trying to decide whether to build a form in Access Project either:
1. using bound controls (ie. text boxes, combo boxes, etc) related to a view in SQL Server 2005, or
2. whether to load the data programatically (using code) into unbound controls on the form, and then saving the data programatically also.
Does anyone have any experience or opinion on pros and cons with either method.
Any assistance would be appreciated.
Thanks
September 18, 2009 at 11:16 am
It depends!
How many users will be using the FE?
How many records will be in the BE?
Your network speed?
Etc.
I make lots of FE applications(over 200 in last 18 months).
I have found that Choice 1 is easiest to work with.
If you have a fast network and less than 40,000 records.
If your BE contains more than 100,000 records, then Choice 2 is the better way to go.
Once you have the SPs for you Add, Delete, and Modify written, and you have your screens designed, it's not to bad.
"When in danger or in doubt. Run in circles, scream and shout!" TANSTAAFL
"Robert A. Heinlein"
September 21, 2009 at 6:37 pm
Hi,
Thanks for the reply. There are currently between 20-50 users (and growing) and number of records is growing and will be above 40,000 is some of the tables eventually. Network speed can be an issue, and would prefer to do all I can to keep this from being an issue (past experiences!).
So option 2 is preferable after your input. Especially as the Stored Procedures will keep things precompiled and faster.
One more question though. When retrieving and updating data using the stored procedure, it seems that I will have to still assign each control with a value, and then retrieve the value from the control when updating. This will take one line of code per control (ie. fieldinstoredprocedure=ors.fields("controlname")). Is there a simpler way to write the code for assigning and retreiving the value from the control?
Thanks again
September 22, 2009 at 2:20 am
This routine does it automatically for you - make the name of the control the same as the related column in the returned recordset, and put the word "Load" in the Tag property of the control.
This example runs the stored procedure, passing a parameter from a combobox, then assigns the recordset to the form, then binds each of the controls to the relevant column. It runs from the AfterUpdate event of the combobox, but you could run it from form load etc.
Private Sub cbobrand_code_AfterUpdate()
On Error GoTo err_handler
Const ProcName = "cbobrand_code_AfterUpdate"
Dim ctl As Control
Dim rst As ADODB.Recordset
Dim strSQL As String
strSQL = "EXEC dbo.usp_SelectBrand @brand_code = '" & cbobrand_code.Value & "'"
rst.Open strSQL, mcnn, adOpenKeyset, adLockOptimistic, adCmdText
Set Me.Recordset = rst
Set rst = Nothing
For Each ctl In Me.Controls
With ctl
'Auto fill the text boxes from the recordset
'where the control's name
'equals the recordset field's name.
If ctl.Tag = "Load" Then
ctl.ControlSource = ctl.Name
End If
End With
Next ctl
End Sub
September 22, 2009 at 2:23 am
oops - duplicate post
September 22, 2009 at 10:57 am
Hi Chris, let's suppose the control name is "Customer", then
ctl.ControlSource = ctl.Name
sets the Customer control's ControlSource to "Customer"
but then...why not just set ControlSource via the control's property sheet?
September 22, 2009 at 5:18 pm
Thanks for your reply Chris, but I think the solution you gave may be for Bound controls (unless I misunderstood).
I am looking to implement unbound controls where they are populated/updated by form code and a stored procedure.
Although, I have a lot of fields in the sp, which means that I would need to have a variable in the sp for every field when updating. Is there an easy way to assign values through an UPDATE stored procedure without creating separate variables for each field in the sp?
Thanks for all the help so far.
September 23, 2009 at 2:47 am
jwellington (9/22/2009)
Thanks for your reply Chris, but I think the solution you gave may be for Bound controls (unless I misunderstood).I am looking to implement unbound controls where they are populated/updated by form code and a stored procedure.
Although, I have a lot of fields in the sp, which means that I would need to have a variable in the sp for every field when updating. Is there an easy way to assign values through an UPDATE stored procedure without creating separate variables for each field in the sp?
Thanks for all the help so far.
The form starts as unbound, but the routine I posted assigns a recordset to the form on change of a combobox, then binds the controls on the fly - it prevents you getting error messages when the form loads, as at that point it doesn't have a recordsource
This is a bit of the best of both worlds - you get the convenience of a bound form, but bound to s stored procedure on the fly - no need to bind to a view or table.
As for saving/updating the data, I do it by stored procedure, passing in each of the text box contents as parameters
Select Case strCRUDType
Case "C"
strAction = "Insert of store"
.CommandText = "dbo.usp_tblStore_insert"
.Parameters.Refresh
.Parameters("@store_number") = txtstore_number
.Parameters("@brand_code") = txtbrand_code
.Parameters("@brand_level_id") = txtbrand_level_id
.Parameters("@store_name") = txtstore_name
.Parameters("@salutation") = txtsalutation
.Parameters("@town") = txttown
.Parameters("@address") = txtaddress
.Parameters("@post_code") = txtpost_code
.Parameters("@iso_country_code") = cboiso_country_code
.Parameters("@telephone1") = txttelephone1
.Parameters("@telephone2") = txttelephone2
.Parameters("@fax") = txtfax
.Parameters("@email") = txtemail
.Parameters("@live_store_ind") = chklive_store_ind
.Parameters("@manual_address_ind") = chkmanual_address_ind
.Parameters("@closed_date") = txtclosed_date
.Parameters("@delivery_type_code") = cbodelivery_type_code
Case "U"
strAction = "Update of store"
.CommandText = "dbo.usp_tblStore_update"
.Parameters.Refresh
.Parameters("@store_number") = txtstore_number
.Parameters("@brand_code") = txtbrand_code
.Parameters("@brand_level_id") = txtbrand_level_id
.Parameters("@store_name") = txtstore_name
.Parameters("@salutation") = txtsalutation
.Parameters("@town") = txttown
.Parameters("@address") = txtaddress
.Parameters("@post_code") = txtpost_code
.Parameters("@iso_country_code") = cboiso_country_code
.Parameters("@telephone1") = txttelephone1
.Parameters("@telephone2") = txttelephone2
.Parameters("@fax") = txtfax
.Parameters("@email") = txtemail
.Parameters("@live_store_ind") = chklive_store_ind
.Parameters("@closed_date") = txtclosed_date
.Parameters("@delivery_type_code") = cbodelivery_type_code
.Parameters("@manual_address_ind") = chkmanual_address_ind
.Parameters("@time_stamp") = txttime_stamp
Case "D"
strAction = "Delete of store"
.CommandText = "dbo.usp_tblStore_delete"
.Parameters.Refresh
.Parameters("@store_number") = txtstore_number
.Parameters("@time_stamp") = txttime_stamp
End Select
.Execute lngRowCount, , Options:=adExecuteNoRecords
If .Parameters("@RETURN_VALUE") 0 Then
ci_UpdateDb = False
blnInTrans = False
mcnnParam.RollbackTrans
'See if a message was returned
strMsg = ""
For Each prm In .Parameters
If prm.Name = "@message" Then
strMsg = prm.Value
Exit For
End If
Next prm
MsgBox strAction & " failed with return value ." & .Parameters("@RETURN_VALUE") & vbCrLf & vbCrLf & strMsg, vbOKOnly + vbCritical, "CI Error"
September 25, 2009 at 1:02 am
Access is notorious for its problems working with a lot of users over LANs (I understand though that you will be using it as a front end only).
I have developed a lot of applications both ways. If you are confortable with VB then I would suggest to go with unbound controls.
Easier to scale, easier to debug, reliable, but longer to develop.
Plus I prefer to have full control over when and what happens in my app.
Having said that, it ultimately your choice !
September 27, 2009 at 6:52 am
Hi,
Thanks for all the responses. I have decided to go with the unbound controls but have another question.
Apart from the benefit of pre-compiling with stored procedures, could I load the data from a view, and then UPDATE on that view to save the data. I have tested this and it seems to work, but thought I heard somewhere that you could not UPDATE a view, or that there may be negative consequences.
Thanks again.
September 27, 2009 at 7:07 am
You can update and insert to views - I do it regularly, but the underlying table must have a unique index to be updatable
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply