Open another form if unable to connect to shared folder (2 Viewers)

[theinviter]

Hi teams:
Good afternoon

i need a kind support on below:
I have a form connected to shared folder, sometimes has a problem with network which can stop staff from entering the data.
so I want to do if has network issue and user can't login to Open another form which place in frontend and if newt wok work again then updated the record in backend file.

So can you please advise me?

 

[Ranman256]

if you cannot access the server file, then add record locally, then export to excel to be added later:

'see if you can connect

if Dir("\\server\folder\myDb.accdb") = "" then

  'export the record to a folder to be added later when you have connection

vFile = "C:\mypath\files\ExportFile_recID" & txtID & ".xlsx"
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12xls, "qsMyQuery", vFile, True

endif

 

[GPGeorge]

Hi teams:
Good afternoon

i need a kind support on below:
I have a form connected to shared folder, sometimes has a problem with network which can stop staff from entering the data.
so I want to do if has network issue and user can't login to Open another form which place in frontend and if newt wok work again then updated the record in backend file.

So can you please advise me?

Your problem is not which form to use. Your problem is whether to use tables in a Back End accdb in a Shared Network location, or local tables in the Front End accdb on the users' computers, or in some other local accdb or even a spreadsheet.

The proposed plan, however, is more complicated and potentially dangerous than you seem to anticipate.

If there are multiple users involved, each user would potentially be creating new records in their own local accdb, updating other records in their own local accdb, deleting records in their own local accdb. Then later, all those different additions, changes and deletions from multiple sources would have to be merged and reconciled with the master tables in the shared Back End accdb. And that merging and reconciliation is not a simple task.

That means you must start with a procedure that can reliable merge and reconcile data before worrying about switching to an alternative, local set of tables.

And even before that, I would imagine the more productive approach would be to address and correct the network reliability problem in the first place. What is it that causes the network not to be reliable?

 

[The_Doc_Man]

I have a form connected to shared folder, sometimes has a problem with network which can stop staff from entering the data.

This needs further explanation. Is this over a Wi-Fi connection? Or do you have a physical cable connection but the routers are flakey? Are we talking about outright network connection losses or just some slowdowns? If you are running Access over a wi-fi or WAN connection that is prone to network losses, you are risking the stability of your data every time you open the app.

A big part of the problem here is that the answer to your question depends on who's (actually, what's) in charge at the moment. The three most likely answers to that question are Access, your event code, or Windows. Two out of three of those answers are not good for you.

Here's the WORST case scenario and an explanation. Suppose your back-end was tested and found to be available at the start of your app, and you therefore started your app in its "normal" operation. You open your form, navigate to a new or old record, and start entering data. If, in the middle of data entry you NOW get a network drop, it is possible that NONE of your code is in control at the moment.

Form designers typically don't involve the _Change event during data entry due to the overhead costs of having _Change code sitting behind ever individual control involved in data entry, and no other event code is likely to be running at the time. That means that either the Windows keyboard driver and window painter are running (actually, most likely case) or MSACCESS.EXE (i.e. Access itself) is in control if you just changed focus to another control. None of your event code is running in either case.

If so, it means you can't intercept this condition until your session has already taken an error. At this point, it might be too late to save anything, depending on which error handler takes control. There IS a Form_Error event which might give you a chance if the form was open at the time, but otherwise your code won't be running anyway. It also is possible that you WERE in your own event code and will have to handle the error, potentially from EVERY CONTROL's event code. If your handler can't handle that error, it will resignal the error up the program stack and in short order, your session will be lost. Even if you DID have an _Error event on the form, that event would have to be able to decide whether the form content is complete and worth saving.

In the better scenarios, when the app starts, the startup code detects the connection problem and therefore COULD initially redirect the critical table links to a secondary .MDB or .ACCDB file as a "side-end" DB to temporarily hold results. You could later consult that secondary file for deferred data uploads so you could play "catch-up." That poses another set of problems but has been known to work. The biggest problem HERE is that you need to not only redirect the main data links, but you also have to have alternate sources for anything that you would normally have to look up during validation or translation of inputs. Like table-driving combo lookups. Therefore, this IS an extensive and complex error handler situation.

The REAL solution is to recognize that if you cannot trust your network's stability, running Access over it is not a good choice. There are alternatives but all of them cost something in time, money, hardware, or software. Oh, and your hair may also fall victim to the problem when you tear it out after a particularly bad problem.