Control a from on another Access file, on another PC

[Acke]

Yes, that would be the first thing I would try. So, replace this:

set AccessApp = Getobject(, "Access.Application")

with something like this instead:

set [CODE]AccessApp = Getobject("C:\FolderName\Filename.ACCDB")

[/code]
Hope that helps...

When only one DB is opened and when the code is:

 set AccessApp = Getobject(, "Access.Application")

it is working well.

In case of

AccessApp = Getobject("C:\FolderName\Filename.ACCDB")

, it opens the same file again.

We are getting closer, but still no solution.

 

[theDBguy]

...it opens the file again.

Interesting... Sorry!

 

[Isaac]

When only one DB is opened and when the code is:

 set AccessApp = Getobject(, "Access.Application")

it is working well.

In case of

AccessApp = Getobject("C:\FolderName\Filename.ACCDB")

, it opens the same file again.

We are getting closer, but still no solution.

I've never tried to use GetObject in a case where I had to differentiate between multiple instances of an open application, so unsure how to directly solve that, but, I can think of yet another approach you might try.

Have your end user click on the VBScript, and only the VBScript.
Code the VBScript to 1) fire up an Access application itself, 2) open the target database, 3) open the necessary form, and then begin acting on it.

I guess this thread is maybe kind of an example of why when people talk about GetObject vs. CreateObject - and sometimes they're very focused on using something like:
If GetObject returns something then
'use it
Else
'create it using CreateObject
End If

..Whereas I have had sporadic and unreliable results with GetObject, and I recommend just always creating it, if there's no particular reason not to. In your case, this might be the only option.

 

[Acke]

I think we have a winning combination. The loop is working. It is initiated from form timer event, but with the timer running only for a moment at the beginning

The code is simple:

Private Sub Form_Timer()
Dim varNr As Long

varNr = 1

Do Until VarNr = 2
varNr = varNr + 1
me.TimerInterval = 0
RefreshFormFunction ' sepparate function that refreshes the form and stops the loop if the form is not loaded
Pause (0.1) ' Function "Pause" written separately to delay for 0,1 sec.'
Loop
End Sub

Summary...

The task: refresh the information on the continuous subform on a remote computer after new data addition.

Considered solutions:
1. Trigger from PC1 to update remote PC2 and vice versa - The most practical solution as there is no procedure running in the back all the time. Even if possible, it would require serious skills.
2. Timer - a solution with setbacks. During 5 days working with "Timer", we noticed three setbacks. A - Working on the VBA is annoying if the timer form is loaded. B - timer form was interfering with DoCmd.GotoControl command from other forms resulting in bugs. C - application on one of the PCs was unacceptably slow. On the PC1 there are three copies of the same application (copies with different file names) running all the time. When all three task forms on the PC1 are open and running the timer, the application on the PC2 is next to unusable.
3. Loop - To work with the VBA it is necessary only to hit the reset button to stop the procedure. No more bugs with DoCmd.GotoControl procedure. Application on the PC2 works the same regardless of any or all three task forms are opened on the PC1. The forms are being updated almost instantly.

This is the day one. So far neither one setback is noticed with the Loop. I hope it will stay that way.

There was some misunderstanding about the reason the imidate update was a requirement. An instant update of the task is not significant. As mentioned for example in my post #55: " We can wait even a full minute before the task appears on the other computer". A fast form update will be required in the next step of the project with building the chat. I hope this clarifies the speed issue.

I want to thank everybody involved in this thread. Especially @The_Doc_Man for helping me understand access functioning on a deeper level, and also to @Isaac for handing out the solution.

 

[Isaac]

I'm glad you got it working. Sounds like you didn't use the VBS but it was fun and eye opening to see the possibilities.
I'm surprised the in-VBA infinite loop doesn't just freeze everything up and make it impossible to work in the database. Guess I learned something here too! I am a little confused on how what you posted keeps going, since varNr will equal 2 after one round.

 

[theDBguy]

I think we have a winning combination. The loop is working. It is initiated from form timer event, but with the timer running only for a moment at the beginning

The code is simple:

Private Sub Form_Timer()
Dim varNr As Long

varNr = 1

Do Until VarNr = 2
varNr = varNr + 1
me.TimerInterval = 0
RefreshFormFunction ' sepparate function that refreshes the form and stops the loop if the form is not loaded
Pause (0.1) ' Function "Pause" written separately to delay for 0,1 sec.'
Loop
End Sub

Summary...

The task: refresh the information on the continuous subform on a remote computer after new data addition.

Considered solutions:
1. Trigger from PC1 to update remote PC2 and vice versa - The most practical solution as there is no procedure running in the back all the time. Even if possible, it would require serious skills.
2. Timer - a solution with setbacks. During 5 days working with "Timer", we noticed three setbacks. A - Working on the VBA is annoying if the timer form is loaded. B - timer form was interfering with DoCmd.GotoControl command from other forms resulting in bugs. C - application on one of the PCs was unacceptably slow. On the PC1 there are three copies of the same application (copies with different file names) running all the time. When all three task forms on the PC1 are open and running the timer, the application on the PC2 is next to unusable.
3. Loop - To work with the VBA it is necessary only to hit the reset button to stop the procedure. No more bugs with DoCmd.GotoControl procedure. Application on the PC2 works the same regardless of any or all three task forms are opened on the PC1. The forms are being updated almost instantly.

This is the day one. So far neither one setback is noticed with the Loop. I hope it will stay that way.

There was some misunderstanding about the reason the imidate update was a requirement. An instant update of the task is not significant. As mentioned for example in my post #55: " We can wait even a full minute before the task appears on the other computer". A fast form update will be required in the next step of the project with building the chat. I hope this clarifies the speed issue.

I want to thank everybody involved in this thread. Especially @The_Doc_Man for helping me understand access functioning on a deeper level, and also to @Isaac for handing out the solution.

Hi. Glad to hear you found a solution. I would like to learn something new as well. Can you share the code for "RefreshFormFunction?" Thanks.

 

[Acke]

I'm glad you got it working. Sounds like you didn't use the VBS but it was fun and eye opening to see the possibilities.
I'm surprised the in-VBA infinite loop doesn't just freeze everything up and make it impossible to work in the database. Guess I learned something here too! I am a little confused on how what you posted keeps going, since varNr will equal 2 after one round.

The VBS is a great trick. Thank you for pointing that out.

The goal was to get the loop going only while the form is open. Any event on form open start was freezing everything up. The only way was to use the timer. The loop in the timer code is being used only to trigger the other loop which is doing the refresh. In response to @theDBguy I will explain everything in detail.

The timer loop needs to have a short pause. Not sure why, but without a slight timer delay it freezes everything up.

 

[Acke]

Hi. Glad to hear you found a solution. I would like to learn something new as well. Can you share the code for "RefreshFormFunction?" Thanks.

Sure. I will explain the full setup.

There are 2 PCs connected over a network and 5 access files. Let's call access files beDB, feDB1, feDB2, feDB3 and feDB4. "be" as backend, "fe" as frontend. All "fe" files are copies of the same file but with different file names and are all table-linked to the beDB. feDB1 to feDB3 are running on the PC1 on 3 monitors. feDB4 runs on PC2.

The process starts when the user on either PCs, any of the forms, enters a new task or edits/deletes an existing task. When any of this happens, the condition is set for all forms, except the form user is working at, to updateable state. The state condition is being set in a special table. I have a "tblScrapBook" table with several numeric and text fields that are being used for various purposes in the application. The tblScrapBook ID in this case is 11. The fields that store the conditions are nField1 to nField4. Conditions are 0 - not updatable; 1 - updateable.

The code to set the updatable condition:

Function SetUpdatable()
Dim db as Database
Dim rs as Recordset

Set db = CurrentDB
set rs = db.OpenRecordset("SELECT * FROM tblScrapBook WEHERE ID = 11", dbOpenDynaset)
rs.edit

If Not ComputerName = "PC1" Then     'ComputerName function defines PC's name
'PC2, or any other computer in the future, sets the condition to updatable for all three acc files on the PC1'
rs!nField1 = 1 'sets condition for the form in the file feDB1'
rs!nField2 = 1 'sets condition for the form in the file feDB2'
rs!nField3 = 1  'sets condition for the form in the file feDB3'
End If

If ComputerName = "PC1" Then
rs!nField4 = 1 ' sets to updatable for the file feDB4 that runns on the PC2'
End If

If ComputerName = "PC1" AND CurrentProject.Name = feDB1 Then ' sets to updatable other two dbs on the PC1'
rs!nField2 = 1 'sets condition for the form in the file feDB2'
rs!nField3 = 1  'sets condition for the form in the file feDB3'
End If

If ComputerName = "PC1" AND CurrentProject.Name = feDB2 Then ' sets to updatable other two dbs on the PC1'
rs!nField2 = 1 'sets condition for the form in the file feDB1'
rs!nField3 = 1  'sets condition for the form in the file feDB3'
End If

If ComputerName = "PC1" AND CurrentProject.Name = feDB3 Then ' sets to updatable other two dbs on the PC1'
rs!nField2 = 1 'sets condition for the form in the file feDB2'
rs!nField3 = 1  'sets condition for the form in the file feDB3'
End If

rs.update
rs.close
db.close

Once the condition is set, any form that is open is being refreshed as it is having the following refresh loop running. The refresh loop code:

Function RefreshForm()
'I was adding the XYZ to the usual naming as the loop will be running almost all the time, to stop interfering with other codes. Not sure if this was necessary, but did it just in case. '
Dim dbxyz as Database
Dim rsxyz as Recordset

Set dbxyz = CurrentDB
Set rsxyz = dbxyz.OpenRecordset("SELECT * FROM tblScrapBook WHERE ID = 11, dbOpenDynaset)

Do Until CurrentProject.AllForms("frmTasks").IsLoaded = False

If ComputerName = "PC1" AND CurrentProject.Name = feDB1 AND rsxyz!nField1 = 1 Then
rsxyz.edit   'the edit/update is listed in the code for each IF to update data in the table only if the condition is met.
                  'This makes the application run smoother'
rsxyz!nField1 = 0 'sets condition back to not-update for the file feDB1'
rsxyz.update
Forms!frmTasks!frmTasksSub.Form.Requery ' refreshes data in the subform'
Forms!frmTasks!frmTasksSub.Form.Refresh ' refreshes the form visually. Task priorities are set as numbers and I am using
                                        conditional formating to present priorities with colors. Without the "Refresh", the numbers
                                        are visible in their fields for some time before the fields show colors again'
End If

If ComputerName = "PC1" AND CurrentProject.Name = feDB2 AND rsxyz!nField2 = 1 Then
rsxyz.edit
rsxyz!nField1 = 0 'sets condition back to not-update for the file feDB2'
rsxyz.update
Forms!frmTaks!frmTasksSub.Form.Requery
Forms!frmTaks!frmTasksSub.Form.Refresh
End If

If ComputerName = "PC1" AND CurrentProject.Name = feDB3 AND rsxyz!nField3 = 1 Then
rsxyz.edit
rsxyz!nField1 = 0 'sets condition back to not-update for the file feDB3'
rsxyz.update
Forms!frmTaks!frmTasksSub.Form.Requery
Forms!frmTaks!frmTasksSub.Form.Refresh
End If

If ComputerName = "PC2" AND CurrentProject.Name = feDB4 AND rsxyz!nField4 = 1 Then
rsxyz.edit
rsxyz!nField1 = 0 'sets condition back to not-update for the file feDB4 on the PC2
rsxyz.update
Forms!frmTaks!frmTasksSub.Form.Requery
Forms!frmTaks!frmTasksSub.Form.Refresh
End If

Pause (0.1) ' Not sure if time delay here is necessary. Function "Pause" written separately to delay for 0,1 sec.'

Loop

rsxyz.Close
dbrsxyz.Close
End Function

This Loop stops when the form is closed. The Loop is triggered each time any of the forms is being open using the Form's timer event:

Private Sub Form_Timer()
Dim varNr As Long

varNr = 1

Do Until VarNr = 2
varNr = varNr + 1
me.TimerInterval = 0
RefreshForm
Pause (0.1) ' Function "Pause" written separately to delay for 0,1 sec.'
Loop
End Sub

The Timer loop is used to trigger the Refresh loop and to stop the Form Timer running.

That's it. In case of any questions, I will be happy to respond.

 

[theDBguy]

Sure. I will explain the full setup.

There are 2 PCs connected over a network and 5 access files. Let's call access files beDB, feDB1, feDB2, feDB3 and feDB4. "be" as backend, "fe" as frontend. All "fe" files are copies of the same file but with different file names and are all table-linked to the beDB. feDB1 to feDB3 are running on the PC1 on 3 monitors. feDB4 runs on PC2.

The process starts when the user on either PCs, any of the forms, enters a new task or edits/deletes an existing task. When any of this happens, the condition is set for all forms, except the form user is working at, to updateable state. The state condition is being set in a special table. I have a "tblScrapBook" table with several numeric and text fields that are being used for various purposes in the application. The tblScrapBook ID in this case is 11. The fields that store the conditions are nField1 to nField4. Conditions are 0 - not updatable; 1 - updateable.

The code to set the updatable condition:

Function SetUpdatable()
Dim db as Database
Dim rs as Recordset

Set db = CurrentDB
set rs = db.OpenRecordset("SELECT * FROM tblScrapBook WEHERE ID = 11", dbOpenDynaset)
rs.edit

If Not ComputerName = "PC1" Then     'ComputerName function defines PC's name
'PC2, or any other computer in the future, sets the condition to updatable for all three acc files on the PC1'
rs!nField1 = 1 'sets condition for the form in the file feDB1'
rs!nField2 = 1 'sets condition for the form in the file feDB2'
rs!nField3 = 1  'sets condition for the form in the file feDB3'
End If

If ComputerName = "PC1" Then
rs!nField4 = 1 ' sets to updatable for the file feDB4 that runns on the PC2'
End If

If ComputerName = "PC1" AND CurrentProject.Name = feDB1 Then ' sets to updatable other two dbs on the PC1'
rs!nField2 = 1 'sets condition for the form in the file feDB2'
rs!nField3 = 1  'sets condition for the form in the file feDB3'
End If

If ComputerName = "PC1" AND CurrentProject.Name = feDB2 Then ' sets to updatable other two dbs on the PC1'
rs!nField2 = 1 'sets condition for the form in the file feDB1'
rs!nField3 = 1  'sets condition for the form in the file feDB3'
End If

If ComputerName = "PC1" AND CurrentProject.Name = feDB3 Then ' sets to updatable other two dbs on the PC1'
rs!nField2 = 1 'sets condition for the form in the file feDB2'
rs!nField3 = 1  'sets condition for the form in the file feDB3'
End If

rs.update
rs.close
db.close

Once the condition is set, any form that is open is being refreshed as it is having the following refresh loop running. The refresh loop code:

Function RefreshForm()
'I was adding the XYZ to the usual naming as the loop will be running almost all the time, to stop interfering with other codes. Not sure if this was necessary, but did it just in case. '
Dim dbxyz as Database
Dim rsxyz as Recordset

Set dbxyz = CurrentDB
Set rsxyz = dbxyz.OpenRecordset("SELECT * FROM tblScrapBook WHERE ID = 11, dbOpenDynaset)

Do Until CurrentProject.AllForms("frmTasks").IsLoaded = False

If ComputerName = "PC1" AND CurrentProject.Name = feDB1 AND rsxyz!nField1 = 1 Then
rsxyz.edit   'the edit/update is listed in the code for each IF to update data in the table only if the condition is met.
                  'This makes the application run smoother'
rsxyz!nField1 = 0 'sets condition back to not-update for the file feDB1'
rsxyz.update
Forms!frmTaks!frmTasksSub.Form.Requery ' refreshes data in the subform'
Forms!frmTaks!frmTasksSub.Form.Refresh ' refreshes the form visually. Task priorities are set as numbers and I am using 
                                        conditional formating to present priorities with colors. Without the "Refresh", the numbers 
                                        are visible in their fields for some time before the fields show colors again'
End If

If ComputerName = "PC1" AND CurrentProject.Name = feDB2 AND rsxyz!nField2 = 1 Then
rsxyz.edit
rsxyz!nField1 = 0 'sets condition back to not-update for the file feDB2'
rsxyz.update
Forms!frmTaks!frmTasksSub.Form.Requery
Forms!frmTaks!frmTasksSub.Form.Refresh
End If

If ComputerName = "PC1" AND CurrentProject.Name = feDB3 AND rsxyz!nField3 = 1 Then
rsxyz.edit
rsxyz!nField1 = 0 'sets condition back to not-update for the file feDB3'
rsxyz.update
Forms!frmTaks!frmTasksSub.Form.Requery
Forms!frmTaks!frmTasksSub.Form.Refresh
End If

If ComputerName = "PC2" AND CurrentProject.Name = feDB4 AND rsxyz!nField4 = 1 Then
rsxyz.edit
rsxyz!nField1 = 0 'sets condition back to not-update for the file feDB4 on the PC2
rsxyz.update
Forms!frmTaks!frmTasksSub.Form.Requery
Forms!frmTaks!frmTasksSub.Form.Refresh
End If

Pause (0.1) ' Not sure if time delay here is necessary. Function "Pause" written separately to delay for 0,1 sec.'

Loop

rsxyz.Close
dbrsxyz.Close
End Function

This Loop stops when the form is closed. The Loop is triggered each time any of the forms is being open using the Form's timer event:

Private Sub Form_Timer()
Dim varNr As Long

varNr = 1

Do Until VarNr = 2
varNr = varNr + 1
me.TimerInterval = 0
RefreshForm
Pause (0.1) ' Function "Pause" written separately to delay for 0,1 sec.'
Loop
End Sub

The Timer loop is used to trigger the Refresh loop and to stop the Form Timer running.

That's it. In case of any questions, I will be happy to respond.

Hi. Thanks for taking the time to explain. Please let us know how it goes when you get the chat part implemented. Good luck with your project.

 

[Acke]

Hi. Thanks for taking the time to explain. Please let us know how it goes when you get the chat part implemented. Good luck with your project.

Thanks @theDBguy. The skeleton of the chat is already implemented. I will open a new thread for the Chat and will post a link here.

 

[Acke]

The chat project thread is posted. Anybody interested to support the project is more than welcome.