Go Back   Access World Forums > Microsoft Access Discussion > Access Web

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-10-2017, 09:36 AM   #1
cigarprofiler
Newly Registered User
 
Join Date: Mar 2017
Posts: 32
Thanks: 20
Thanked 0 Times in 0 Posts
cigarprofiler is on a distinguished road
How to best distribute Access frontend

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?

cigarprofiler is offline   Reply With Quote
Old 06-10-2017, 10:02 AM   #2
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 9,435
Thanks: 36
Thanked 3,048 Times in 2,963 Posts
CJ_London is a jewel in the rough CJ_London is a jewel in the rough CJ_London is a jewel in the rough
Re: How to best distribute Access frontend

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
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
cigarprofiler (06-11-2017)
Old 06-11-2017, 12:56 AM   #3
cigarprofiler
Newly Registered User
 
Join Date: Mar 2017
Posts: 32
Thanks: 20
Thanked 0 Times in 0 Posts
cigarprofiler is on a distinguished road
Re: How to best distribute Access frontend

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 is offline   Reply With Quote
Old 06-13-2017, 11:22 AM   #4
cigarprofiler
Newly Registered User
 
Join Date: Mar 2017
Posts: 32
Thanks: 20
Thanked 0 Times in 0 Posts
cigarprofiler is on a distinguished road
Re: How to best distribute Access frontend

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

    Dim ws As DAO.Workspace 'Needed for transaction handing
    Dim db As Database
    Dim rs As Recordset

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

    On Error GoTo err_handl_trans

    ws.BeginTrans '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
    
    ws.CommitTrans 'Executes transaction
    
    DoCmd.Requery "subfrm_review_items"
       
exit_sub:

    ws.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub
    
err_handl_trans:

    ws.Rollback
    Resume exit_sub
    
End Sub

cigarprofiler is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
distribute access backend link hardyd44 General 4 10-02-2012 11:41 AM
distribute DB with access runtime mickelingon General 1 01-12-2008 08:46 AM
Distribute to many versions of access Rachael General 8 10-08-2007 09:46 AM
Distribute MS Access Harmonic General 9 09-07-2007 04:17 AM
Distribute Access Harmonic General 1 09-06-2007 04:13 AM




All times are GMT -8. The time now is 10:54 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World