Bob's FE update utility

[BarryMK]

I really want to use the Front end auto updating utility that BobLarson posted in Sample databases http://www.access-programmers.co.uk/forums/showthread.php?t=111132 but am having a problem that others have logged and wonder if anyone has a fix?

Having re-read the instructions many times and run up a test database to use, I'm pretty sure I've done everything correctly but I reach the point where the old front end is apparently deleted successfully but then the new file is not created.

I've attached a screendump of the batch file error.

I've extended the time (ping) in the module but no difference.

I've tried absolute address and UNC with equal lack of success.

 

[Guus2005]

For starters, rename instead of delete and second use echo on at the start of your bat file. Now you can see the actual command's which are executed. It gives you more information about the batch process!

 

[BarryMK]

Thanks for the input but I'm not familiar with bat files and wouldn't wish to play around too much, this was coded by BobLarson. I seem to have a local problem with this that others have had but their fixes haven't helped in my case.

 

[Guus2005]

Could you post the batch file?

 

[boblarson]

Barry:

It almost looks like the master file location is set incorrectly. The error message looks like it is trying to find the master on the desktop which isn't where the master should be, but where the file that was originally there should be and therefore it is trying to copy something that has been deleted. Can you post a screenshot of the value in the fe_master_location table?

 

[MochaTrpl]

FE update settings

Thanks Bob for the earlier Pm's.

I just came accross this new thread.

Question#1


I will store both the front-end and the back-end files on the network drive "Z"

I want to have on the client side a copy of the front-end file. If I understand the documentation and the revision status, if I update the Network revs, when the client starts, it should compare revs and close, delete and copy the version that is on the network drive.

The attached is a pdf screen capture of what I think the settings should be but an error says otherwise.

Question#2

How to encrypt and distribute so that the update will work.



Thanks in advance!

- Randy

 

[boblarson]

Aha! I think the problem potentially coming into play here is that I'm not sure that it will work with Access 2007. I hadn't tested it for that version and it may not work with accdb files.

 

[MochaTrpl]

I was lucky "once" to get it to work and have not duplicated it since but it was trial and error with the settings. The documentation can be confusing

Can you clarify the first line in the instruction.

1. On the main form in this utility, browse to the front-end master and to the back-end using the browse buttons and dialogs to select the file you want to specify as your front-end master and your back-end and select the location where your front-end master will be stored.

The above highlights are what are confusing since if I were to select the second items location, wouldn't this be the same as the first item? This is where I get a run-time error'7884':
"You can't export an object to itself"

Aha! I think the problem potentially coming into play here is that I'm not sure that it will work with Access 2007. I hadn't tested it for that version and it may not work with accdb files.

 

[MochaTrpl]

I feel stupid

OK, I feel like such an idiot...

I got it to work by process of elimination.

Make sure you DO NOT import the FE utility into your project before running it... It does not say to do that. You will have problems, headaches, suicidal thoughts!

As Bob had noted to me and I did not pick up on until I had some more caffeine and a Rockstar drink, that the 3rd text line on the menu is to point to the directory chosen in the 1st text line asking for the location of the FE master.

Please DO NOT do as I DID otherwise you will not be able to thank BOB as I am trying to do.

THANKS FOR YOUR HARD WORK BOB!

 

[BarryMK]

Barry:

It almost looks like the master file location is set incorrectly. The error message looks like it is trying to find the master on the desktop which isn't where the master should be, but where the file that was originally there should be and therefore it is trying to copy something that has been deleted. Can you post a screenshot of the value in the fe_master_location table?

Hi folks been away from the forum a few days. At the moment I'm buried under converting databases to 2003 thanks to our IT dept randomly upgrading people from 97 without telling me. I'm sure you're right Bob and it's my misunderstanding the addressing. When I get a chance to look at this again I'll have another crack. Thanks meanwhile to all.

 

[MochaTrpl]

Can someone look at the attached modified basFEUpdate and see if there is a way to have the downloaded database start by the wrkgrp login as in Access 2000. I am trying to continue the same login prompt using Access 2007. I currently launch Access 2007 using a desktop shortcut that points to the wrkgrp like this...

"C:\Program Files\QT_client\QTNextGen.accdb" /wrkgrp z:\QT_febe\workgroup\qtnew.mdw

It would seem that the re-launch, after checking for latest revisions, the FE utility could do the same?

Option Compare Database
' global variable for path to original database location
Public g_strFilePath As String
' global variable for path to database to copy from
Public g_strCopyLocation As String


Public Sub UpdateFrontEnd()
Dim strCmdBatch As String
Dim notNotebook As Object
Dim FSys As Object
Dim TestFile As String
Dim strKillFile As String
Dim strReplFile As String
Dim strRestart As String

' sets the file name and location for the file to delete
strKillFile = g_strFilePath
' sets the file name and location for the file to copy
strReplFile = g_strCopyLocation & "\" & CurrentProject.name
' sets the file name of the batch file to create
TestFile = CurrentProject.Path & "\UpdateDbFE.cmd"
' sets the restart file name
' Original string command => strRestart = """" & strKillFile & """"
'sets the restart file name(with wrkgrp command)
strRestart = """" & strKillFile & " /wrkgrp z:\QT_febe\workgroup\qtnew.mdw" & """"
' creates the batch file
Open TestFile For Output As #1
Print #1, "Echo Off"
Print #1, "ECHO Deleting old file"
Print #1, ""
Print #1, "ping 1.1.1.1 -n 1 -w 2000"
Print #1, ""
Print #1, "Del """ & strKillFile & """"
Print #1, ""
Print #1, "ECHO Copying new file"
Print #1, "Copy /Y """ & strReplFile & """ """ & strKillFile & """"
Print #1, ""
Print #1, "CLICK ANY KEY TO RESTART THE ACCESS PROGRAM"
Print #1, "START /I " & """MSAccess.exe"" " & strRestart
Close #1
'Exit Sub
' runs the batch file
Shell TestFile

'closes the current version and runs the batch file
DoCmd.Quit



End Sub



Thanks in advance!

 

[Villarreal68]

Access 2007 and Front-End Update Utility...

Aha! I think the problem potentially coming into play here is that I'm not sure that it will work with Access 2007. I hadn't tested it for that version and it may not work with accdb files.

Hello Bob,

I tried using your handy utility on an Access 2007 db, but I wa not able to get anywhere from the get go. it checks that the db has an extension of .mdb. I'm not very familiar with vb but I tried tinkering with the code so It would accept the .accdb extension but with no success. Any suggestions?

Thanks!

 

[boblarson]

Yeah, I found that out when I needed to use it with a client here. I will be uploading a revised version soon that will handle Access 2007, as long as the trusted locations are set up.

 

[Villarreal68]

Yeah, I found that out when I needed to use it with a client here. I will be uploading a revised version soon that will handle Access 2007, as long as the trusted locations are set up.

The trusted locations are already in place and waitting!

Thanks for all your efforts and time spent in this very unique and helpful place.

René

 

[boblarson]

The trusted locations are already in place and waitting!

Thanks for all your efforts and time spent in this very unique and helpful place.

René

The tool has been modified and is available on my site. It now works with Access 2007.

 

[Villarreal68]

Thanks!

 

[Banana]

*BUMP*

A quick question.

I have a database that uses ODBC connection and therefore has no Access backend. I wonder if it's safe enough to make it self-referencing like this:

Set FE master to a copy of FE on a networked folder.
Set BE to the same FE master

Or will this cause more headache than it solves?

 

[KenHigg]

What do you mean 'Set BE to the same FE master'?

 

[boblarson]

*BUMP*

A quick question.

I have a database that uses ODBC connection and therefore has no Access backend. I wonder if it's safe enough to make it self-referencing like this:

Set FE master to a copy of FE on a networked folder.
Set BE to the same FE master

Or will this cause more headache than it solves?

The main thing is that the backend (regardless of type) needs to have the table with the version in it. So, in this case, yes you would refer to the frontend which then the table would be put into it and then you would move that table to your backend and then link to it (manually).

 

[KenHigg]

I haven't looked at Bob utility but having used similar approach I suspect one option would be to go ahead and have the MS Access backend set up as Bob's model suggest and only have the table that contains the version record/value in it. And of course all of you production data would still be in the SQL Server. Chances are that there will be other system or utility type information that might make sense to store in the MS Access backend instead of the SQL backend.

???