How to best distribute Access frontend (1 Viewer)

cigarprofiler

Registered User.
Local time
Today, 08:06
Joined
Mar 25, 2017
Messages
32
My project has a postgresql backend hosted on Amazon Web Services, for which I am developing a frontend in Access. With your help, I'm making good progress, so I started to think about the best way to make the application available to multiple users (think of them as consumers, so no company network or anything like that).

When the weather is good, I like to sit outside and do my coding while smoking a cigar. Since that means working via wifi, the connection sometimes drops and Access restarts. A bit of a nuisance, but manageable. For users, however, this would be unacceptable.

I was planning on streaming the frontend via Amazon Appstream, but the other day, I found out about Access Runtime, so I added that to my list of options. Now I have the following questions:

1. Does anyone have experience with Appstream? Does the "no wifi" principle apply in such a scenario? The user would access the frontend via Amazon, and connect to the backend via Amazon too. My guess is that Amazon has the whole thing cabled up, but there may be some remote technologies involved to make matters more complicated.

2. Is a Runtime application equally susceptible to the wifi-related instability as the full Access application?

3. Am I right in assuming that since each user would be executing their own Runtime package, multiple users at the same time are not an issue (since that is managed on the postgresql backend)?

Any thoughts?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 15:06
Joined
Feb 19, 2013
Messages
16,555
1. don't know, but access needs a client based app to run (same as excel, word or any other application)

2. runtime is just a cutdown version of the full version - no navigation pane, ribbon, shortcut menus. Also, you cannot open a form/query/report in design view although (not tested) in vba you might a able to make and save design changes. So yo answer you question, yes it will be equally susceptible.

3. correct. SOP is that each user has their own copy of the front end, regardless of what the backend is.

The instability you refer to is in connection with an access backend. If an update transaction is interrupted, it is the backend that might be left in an uncertain state. Enterprise scale backends such as sql server, azure are more robust and can handle these interuptions. That is not to say that you don't need error handling code in the front end to handle an interruption. - You should investigate the begintrans, committrans and rollbacktrans options
 

cigarprofiler

Registered User.
Local time
Today, 08:06
Joined
Mar 25, 2017
Messages
32
Thanks for the pointers, CJ. I found that Access automatically uses transactions in Make table, Append, Update and Delete queries.

I found this web page that explains transactions in simple terms.

Now I'm thinking if it would be a good idea to replace some of my VBA code with calls to transactional queries instead of writing to a recordset in a sub...
 

cigarprofiler

Registered User.
Local time
Today, 08:06
Joined
Mar 25, 2017
Messages
32
So, I read up on transactions and modified my code as shown below (added the bolded parts). I am not sure if it works, though, because nothing happens - that is to say, the code is executed as expected, but I don't "see" the transactional aspect of it.

Am I doing it right? I used the code on this page for reference.

Also, I wonder if this is not a bit over the top for writing three values to one table, I keep reading that transactions are meant for updating multiple tables in one go. The problem I originally set out to solve was connection interruptions to the backend DB due to wifi use.

Code:
Private Sub Observation()
'Logs the observation that was made by pressing a button

    [B]Dim ws As DAO.Workspace[/B] 'Needed for transaction handing
    Dim db As Database
    Dim rs As Recordset

    [B]Set ws = DBEngine(0)[/B] 'Needed for transaction handling
    Set db = CurrentDb
    Set rs = db.OpenRecordset("Select * from public_tblreview_items")

    [B]On Error GoTo err_handl_trans[/B]

    [B]ws.BeginTrans[/B] 'Start of transaction
        
    rs.AddNew
    
    rs("review_id") = Me.txt_reviewid
    rs("review_item_type_id") = ReviewItemType 'ReviewItemType is set in the OnClick-event of the button, after which this sub is called
    
    Select Case ReviewItemType
    
        Case 2452 'wrapper
        rs("review_item_value") = [Forms]![frm_review]![cmb_wrapper].[Value]
                
        Case 2453 'band
        rs("review_item_value") = [Forms]![frm_review]![cmb_band].[Value]
        
        Case 2457 'remark
        rs("review_item_value") = 1
        rs("review_item_text") = [Forms]![frm_review]![txt_remark].[Value]
        
       '...

        Case Else
        rs("review_item_value") = 1
        
    End Select
    
    rs.Update
    
    [B]ws.CommitTrans[/B] 'Executes transaction
    
    DoCmd.Requery "subfrm_review_items"
       
exit_sub:

    [B]ws.Close[/B]
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
    
[B]err_handl_trans:

    ws.Rollback
    Resume exit_sub
[/B]    
End Sub
 

Users who are viewing this thread

Top Bottom