Macro to download the most current .adp to user's computer 1

[childrenfirst]

Hi,

I have an application in .adp, but don't want users on the network run the .adp file directly.

I heard that there is a way to create an .mdb file (no tables, forms, or vba codes, but a macro that download most current .adp file from the .adp file in the network to user's computer), and save a shortcut on user's computer. Everytime when the user launches the .mdb file, the macro will download the most current version of the .adp file in a network folder to user's computer.

Does anyone know how to implement this tactic and create the macro?

Any advice will be greatly appreciated!

 

[jrbarnett]

There's no need to do this as an Access database, use an operating system file copy as detailed in thread779-1297305.
Just replace the path and name of the Excel sheet with that of your ADP file.

John

 

[PWise]

Add This code to ypu ADP and run once

Sub AddProp()
Dim dbs As Object
Set dbs = CurrentProject
dbs.Properties.Add "Currentnum", 0
End Sub

on Startup of your launch Mdb create a autoexec macro to run this code

Option Compare Database
Option Explicit
Dim AppAccess As New Access.Application
Dim FS As Scripting.FileSystemObject

Sub CheckFileVersion()
Dim FS As Scripting.FileSystemObject
Dim a
Dim LocalVersion As Integer
Dim ServerVersion As Integer

Set AppAccess = CreateObject("Access.Application")
AppAccess.AutomationSecurity = 1
AppAccess.OpenAccessProject ("\\XXX\xxx\xXXX\XXXX\XXXX.ade") 'Server file
ServerVersion = AppAccess.CurrentProject.Properties("currentnum")
AppAccess.CloseCurrentDatabase
AppAccess.OpenAccessProject ("c:\xxx\xXXX\XXXX\XXXX.ade") ' Local file
LocalVersion = AppAccess.CurrentProject.Properties("currentnum")
If LocalVersion < ServerVersion Then
    AppAccess.CloseCurrentDatabase
    FS.CopyFile "\\XXX\xxx\xXXX\XXXX\XXXX.ade", "c:\xxx\xXXX\XXXX\XXXX.ade", True
Else
    
End If
AppAccess.OpenAccessProject ("c:\xxx\xXXX\XXXX\XXXX.ade")
AppAccess.Visible = True
AppAccess.DoCmd.OpenForm "FrmStart", , , , , acHidden
'open a form hidden that when that when you quit the launch app the ADP does not close
AppAccess.AutomationSecurity = 2
Quit
End Sub

Remenber to run this code when you are redey to update the ADP

 CurrentProject.Properties("Currentnum")= CurrentProject.Properties("Currentnum")+1

 

[childrenfirst]

Hi PWISE,

Thank you so much for sharing the code with me! I will give it a try today

 

[PWise]

Try this code instead
less Bugs

Option Compare Database
Option Explicit

Sub CheckFileVersion()
Dim AppAccess As Access.Application
Dim LocalVersion As Integer
Dim ServerVersion As Integer

Set AppAccess = CreateObject("Access.Application")
AppAccess.AutomationSecurity = 1
AppAccess.OpenAccessProject ("\\XXX\xxx\xXXX\XXXX\XXXX.ade") 'Server file
ServerVersion = AppAccess.CurrentProject.Properties("currentnum")
AppAccess.CloseCurrentDatabase
AppAccess.OpenAccessProject ("c:\xxx\xXXX\XXXX\XXXX.ade") ' Local file
LocalVersion = AppAccess.CurrentProject.Properties("currentnum")
If LocalVersion < ServerVersion Then
    AppAccess.CloseCurrentDatabase
    FileCopy "\\XXX\xxx\xXXX\XXXX\XXXX.ade", "c:\xxx\xXXX\XXXX\XXXX.ade"
    AppAccess.OpenAccessProject ("c:\xxx\xXXX\XXXX\XXXX.ade")
Else
    
End If
AppAccess.Visible = True
AppAccess.DoCmd.OpenForm "FrmStart", , , , , acHidden
'open a form hidden that when that when you quit the launch app the ADP does not close
AppAccess.AutomationSecurity = 2
Quit
End Sub

Let me know how it went

 

[childrenfirst]

Hi pwise,

Thank you very muchh for your help! I bumped into a few problems when trying to implement your solution:

1) I was unable to make ADE file out of the APT file. Will your code works for ADP file?

2) I do not know how to set up an AutoExecute macro in the .mdb file. Could you walk me through it step by step? Sorry

3) The local file is not in ADE format. I created an empty .mdb file and will set up the AutoExecute macor in the file. Does it mean that the path to the local file should be something like "C:...\....mdb" instead of "C:...\....ade" in your sample code?

Thank you very much!

 

[PWise]

1) I was unable to make ADE file out of the APT file. Will your code works for ADP file?

what is a ATP file?
this should work for a .mdb, .mde, .ade, .adp.

2) I do not know how to set up an AutoExecute macro in the .mdb file. Could you walk me through it step by step? Sorry

rename the sub CheckFileVersion to function CheckFileVersion
In the Launch .mdb create a new macro the action should be runcode
the function shuld be CheckFileVersion()
save the macro as autoexec.

each time you open this mdb this macro will run

3) The local file is not in ADE format. I created an empty .mdb file and will set up the AutoExecute macor in the file. Does it mean that the path to the local file should be something like "C:...\....mdb" instead of "C:...\....ade" in your sample code?

by the local file i mean the last file version that this user downloaded

 

[childrenfirst]

Hi pwise,

Thank you so much for being very patient and helpful! I had a typo in the previous posting. I meant to write ADP but I typed APT. Sorry... The application is in ADP format.

I saved the module CheckFileVersion in the empty .mdb database, and set up a macro (named autoexecute) which has RunCode in Action and CheckFileVersion() in the function field.

When I launched the .mdb file, a file is created in the user folder with in .ldb format (i.e. ApplicationName.ldb), and when I clicked on the newly created file, I got an error message saying "unrecognized database format '\\userfolder\ApplicationName.ldb'"

Could you advise what I should do next? Thank you again for all your help!

 

[PWise]

There is no need to click any thin except the .mdb file this will copy and launch the newest .adp

 

[childrenfirst]

Good morning, pwise,

Then I must have done something wrong because the .mdb file did launch the newest .adp...

Here is the code after I customized it:

Function CheckFileVersion()
Dim AppAccess As Access.Application
Dim LocalVersion As Integer
Dim NetworkVersion As Integer

Set AppAccess = CreateObject("Access.Application")
AppAccess.AutomationSecurity = 1
AppAccess.OpenAccessProject ("\\Networkfolder\ApplicationName.adp") 'Network file
NetworkVersion = AppAccess.CurrentProject.Properties("currentnum")
AppAccess.CloseCurrentDatabase
AppAccess.OpenAccessProject ("\\UserFolder\ApplicationName.mdb") ' Local file
LocalVersion = AppAccess.CurrentProject.Properties("currentnum")
If LocalVersion < NetworkVersion Then
AppAccess.CloseCurrentDatabase
FileCopy "\\Networkfolder\ApplicationName.adp", "\\UserFolder\ApplicationName.mdb"
AppAccess.OpenAccessProject ("\\UserFolder\ApplicationName.mdb")
Else

End If
AppAccess.Visible = True
AppAccess.DoCmd.OpenForm "frmLogin", , , , , acHidden
'open a form hidden that when that when you quit the launch app the ADP does not close
AppAccess.AutomationSecurity = 2
Quit
End Function

 

[PWise]

So is it working now

 

[childrenfirst]

No... It's not working... I wonder if I saved the macro in the wrong way or customized the code incorrectly. Here is what I have done:

Created an empty .mdb file in UserFolder called ApplicationName.mdb. Saved a function in module called CheckFileVersion(). Save a macro named autoexecute with RunCode in Action and CheckFileVersion() in Function.

The .adp file in the NetworkFolder is called ApplicationName.adp, and I saved the AddProp() in a module and ran it once after I added the module to the file.

Thank you!

 

[PWise]

Created an empty .mdb file in UserFolder called ApplicationName.mdb. Saved a function in module called CheckFileVersion(). Save a macro named autoexecute with RunCode in Action and CheckFileVersion() in Function.

macro name Should be autoexec

on the local computure you need two files (or shortscuts to these two files)
1) lanuch.mdb contains the function CheckFileVersion() and macro autoexec
2) ApplicationName.adp contains all yor forms reports ect. and the AddProp() sub

what happans when you click lanuch.mdb it checks the Version on the ApplicationName.adp on the server and then it it checks the Version on the ApplicationName.adp on the local compture AppAccess.CurrentProject.Properties("currentnum")

if the server version is newer then the local version it copies the server version to the local compture and opens it

if not it opens the local version

 

[childrenfirst]

Hi pwise,

I changed the macro's name to autoexec, and also renamed the module from CheckFileVersion to CheckFileVersion(). I also saved an .adp file to the local user folder. So now, when I launch launch.mdb file, it opens up the .adp file.

One thing that I still have question about is whether the macro indeed checks file versions. To test it, I made a minor change in the login form in the .adp file in the server folder (or network folder), saved it and closed it. I then launched the .mdb file which opens up the .adp file in the User Folder. For some reason, the minor change is not showned in the .adp file in the User Folder. Is it because the version comparison is based on the date the .adp file in the network folder was last modified? What is the criterion used for version comparison?

Thank you so much for all your help! You are super!!

 

[PWise]

Note this in my first post.

Remenber to run this code when you are redey to update the

CODE

CurrentProject.Properties("Currentnum")= CurrentProject.Properties("Currentnum")+1

The function checks the Property Currentnum you must remember to increase Currentnum Property that the compture should know that is a new version