As a warning, I'm self-taught so I have a bad habit of brute-forcing things out of ignorance. I've done some searching online and haven't found a solution, so I figured it couldn't hurt to ask you kind folks if there was a better alternative. PROBLEM
I have multiple front ends that are updated on an almost daily basis as needs change (or I find problems). I need an easy way to push out new local copies of the front ends to multiple computers. Asking the users to replace the database themselves is highly problematic as there are very few computer savvy people on the production floor. MY IDEA
I was going to create a PushTo access database (2003) that would store a list of the front ends, and a list of the locations/directories where the programs are stored. The program would then cycle through the locations for the program selected by the user, deleting the old version and copying the newer one over from the master folder. Thus I could trigger an update whenever I had a new version ready to go.
The alternate thought was to put code inside the front ends that would check on startup to see if it was current or not. But I'm not sure if there is a way to trigger a self-delete/replacement while the program is active. QUESTION
Is there any easier way to do this? Am I going about this completely backwards?
Thanks!
10 11438 Minion 108
Recognized Expert New Member
It sounds like you have a couple good ideas there, but let me ask a couple things.
1. Are all the FE's identical?
2. Do all the users have access to a networked location that you control?
The reason I ask is this... If the number of users is limited they should be able to share a front end. Baring this it is generally easier to alter a stored location that you control than trying to access a user's machine. If all the users were hooked to one location you could store the front ends on the server and they would link via shortcuts on their desktops. Then you would need only replace the FE's on the server and the user would be blissfully unaware.
There are several ways to do what you are asking, but it'll take some further understanding to find the best method so bear with me.
Hope this helps.
- Minion -
1. Are all the FE's identical?
2. Do all the users have access to a networked location that you control?
This is where it gets a little complicated, and the point where I wish I had a whiteboard to doodle on. ^_^;; *sheepish look* (baa!)
1. The back ends are all stored in one folder that is accessible to everyone. (\\Fileserver\D atabases)
2. The front ends (master copies) are in another folder that is also accessible to everyone, but which is read-only to everyone but me. (\\Fileserver\D atabases\Active Copies of UI) This stops folks from using the master copy, as I need to get in an work on them on a more or less regular basis. *sighs* This also lets them replace the local copy if it gets corrupted (something I'm still teaching the Supervisors how to do. I really need to automate it at some point...)
3. There are 10 back ends and 22 front ends, not counting the front ends that haven't hit beta yet. Each of these front ends does something unique, from running custom reports for the various departments and supervisors, to recording a different data set at each manufacturing station. Such fun. However there are several of these databases that run on more than one computer (such as the Supervisor's Production Database) and those are the ones I need to force updates on.
3a. There are fifty some-odd users, thirty some-odd computers. The computers are not always turned on (sigh) and thus I probably need to code in something that remembers what it has updated and what it hasn't. Hrmm.. *makes notes*
4. Why local copies instead of shortcuts? They kept corrupting when multiple people used the same front end. Plus the UIs need to be running 24/5 (for production) but only need to link to the back ends when actively in use (5% of the time). Since the bulk of the users are never active at the same time, it's easer to break down the back ends accessing to each user. Thus everyone has a local copy that can pick up and drop connections to the back ends as needed. (Only active tables are linked, static ones have local copies that are programmaticall y sync'd)
5. Users are sneaky and keep ferreting away copies of the UIs to locations I don't know about. (I run a Search on the network every so often to try and kill them off.) So I probably need to include a validation code in the UIs themselves, just to keep folks from using the old versions.
Too much info? Not enough?
Just a thought ...
I had a single application which users logged into. I did a lookup on the backend user table to see what group the user belonged to. The forms and menus were all driven by the user group. Some forms and capabilitlies were shared across groups and a group I called "reporters" were only able to view and run reports.
I had case statements which may open a form read-only for one group and full access for another. The case statements would enable/disable functions as well.
I had case statements which may open a form read-only for one group and full access for another. The case statements would enable/disable functions as well.
This is something I have implemented for several of the databases, it filters by user and access level as to what they see and what they can edit. The problem with combining them into one 'Evil Overlord' database would be it would be a pain in the neck to document clearly enough that if I got hit by a bus the company wouldn't suffer.
I've got an Inventory Database, a Production/WIP Database, a Sales/Forecasting database, a Scheduling Database, etc. All of them talk to each other, but each of them has a host of unrelated tables, forms, and queries. There is a three-ring binder for each, with full documentation. Plus it's much easier to troubleshoot fifty-odd pages of code than it is 500. ^_~ *grin*
The best way that I've dealt with that very issue is to create a batch file that they use to run the install/Update batch file. This way they can get the most current published version of the application that you place in a folder that is used for published versions of the different applications. -
Call "R:\CorpAdmin\Records And Imaging\CSI\Tally Sheets\New User Interface\Install4.BAT"
-
"C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE" "c:\BatchTally\BatchTallyV4.mde"/wrkgrp "r:\CorpAdmin\Records and Imaging\CSI\Tally Sheets\New user Interface\StandardADPA.MDW"
-
and the Install4.bat has the following in it. -
c:
-
cd\
-
mkDir BatchTally
-
CD\BatchTally
-
Copy "r:\CorpAdmin\Records and Imaging\CSI\Tally Sheets\New user Interface\BatchTallyV4.MDE"
-
Copy "r:\CorpAdmin\Records and Imaging\CSI\Tally Sheets\New user Interface\*.bat" "c:\BatchTally"
-
Copy "r:\CorpAdmin\Records and Imaging\CSI\Tally Sheets\New user Interface\*.Lnk" "c:\Documents and Settings\%UserName%\Desktop"
-
Hope that helps,
Joe P.
NeoPa 32,579
Recognized Expert Moderator MVP
I remember another Expert telling me of his solution for this, so I PMed him a link. Let's see if he can throw in something interesting ;)
Denburt 1,356
Recognized Expert Top Contributor
I know I am late on my posts a lot lately.
First I don't push them out since I don't have the necessary IT privileges etc.So what I do is this.
I created a routine which could definitely use some work and it is a little brutal or crude in some aspects but it serves it's purpose and it could/should help. Works great for us on quite a number of databases. A lot of this design is similar to some of your ideas and I completely understand the self taught approach so please feel free to ask as many questions you would like. I don't want it to just "work" I would rather you understand the code why it is there and what it is doing.
I start out with two tables one on the back end I call it "version", you update this one after designs are completed. The local one on front end I called it "versionFrm " to be compared with the one on the back end. Once they confirm they want the newer version I create a VBScript on the fly run it and the code immediately exits out of the db I am in, almost completely stupid proof. The VBScript copies the database creates a desktop shortcut etc.
Main form has a field with the current version number on it pulled from the local table Main form uses the on timer event to compare the tables periodically. I generally set the timer interval to 5min, set it longer if you don't change that database as often. For safety reasons I rename the older file each time and delete ones not needed, that way they will almost always have 2-3 backup front ends on the hard drive I am currently in need of automating the rollback so they don't need to call me for instructions on a manual rollback to the previous versions on the hard drive.
Does this sound like something you could use? If so I will post the code it is a fair amount so I will do a little house cleaning in case you would like it. I will be back in a day or two (tops I hope lol).
Oh btw I just had to add a check to see if the front end is on the network drive when the Main form is opened and if so I prompt them to copy it over this saves me so much time and headache. The supervisors can show the newbs hey look just click and go.
NeoPa 32,579
Recognized Expert Moderator MVP
Thanks for posting this Denburt. As it's such an interesting concept anyway, can you go ahead and post what you have. That way it's here for posterity :)
Denburt 1,356
Recognized Expert Top Contributor
I should probably post this in the articles section yet I would like to refine it a bit before I do that. I think I have everything or at least most of it if there is any questions or comments please feel free to let me know. - Private Sub Form_Timer()
-
If CheckVersion(Me!Version) = False Then
-
msg = MsgBox("This is an old version there is a newer version available. " & vbCrLf & "Click yes to copy a newer version to your harddrive (the old version will be renamed Futures1.mde and saved for a backup)!" & vbCrLf & "Click no to continue using this version!", vbYesNo)
-
If msg = vbYes Then
-
UpdateDB
-
End If
-
End If
-
End If
-
End Sub
-
Public Function CheckVersion(Ver As String)
-
Dim dbs As Database
-
Dim rs As Recordset
-
On Error GoTo CheckVersion_Error
-
Set dbs = CurrentDb
-
Set rs = dbs.OpenRecordset("Version")
-
Do While Not rs.EOF
-
If Ver = rs!VersionNo Then
-
CheckVersion = True
-
Else
-
CheckVersion = False
-
End If
-
rs.MoveNext
-
Loop
-
rs.Close
-
Set rs = Nothing
-
Set dbs = Nothing
-
On Error GoTo 0
-
Exit Function
-
CheckVersion_Error:
-
ErrEmail "modUtilities", Err.Description, Err.Number, "CheckVersion"
-
End Function
-
The UpdateDB function is a bit crude and I did have some trouble getting it to work since it is essentially creating a VBScript file similar to the batch file an earlier poster mentioned. I create this using code so I have even less issues to worry about. Once created you call it, then quit the Access app. Similar to what I think you were describing when you said you could call a function from another DB. Something I have thought about as well but for now this is how I am handling it and it works quite well. - Function UpdateDB()
-
Dim strVBS As String, strFName As String, strPath As String
-
strFName = "\copyMDB.vbs"
-
strPath = Application.CurrentProject.Path
-
-
'The VBScript code is simply pasted in below this comment but I removed it and placed the file in a more legible context below. This could probably be stored in a table or something but as you stated I have been thinking about moving it to another central code DB
-
-
strVBS = "Option Explicit" & vbcrlf & "etc. etc. see the code below"
-
-
'For the finale
-
SaveTextData strPath & "\" & strFName, strVBS, "ASCII"
-
'Function to create a shortcut
-
CreateShrtCut
-
DoEvents
-
'Function to call VBScript
-
RunIt strPath, strFName
-
DoEvents
-
DBEngine.Idle
-
Application.Quit
-
End Function
-
-
Function SaveTextData(FileName, Text, CharSet)
-
Const adTypeText = 2
-
Const adSaveCreateOverWrite = 2
-
-
'Create Stream object
-
Dim BinaryStream
-
Set BinaryStream = CreateObject("ADODB.Stream")
-
-
'Specify stream type - we want To save text/string data.
-
BinaryStream.Type = adTypeText
-
-
'Specify charset For the source text (unicode) data.
-
If Len(CharSet) > 0 Then
-
BinaryStream.CharSet = CharSet
-
End If
-
-
'Open the stream And write binary data To the object
-
BinaryStream.Open
-
BinaryStream.WriteText Text
-
-
'Save binary data To disk
-
BinaryStream.SaveToFile FileName, adSaveCreateOverWrite
-
Set BinaryStream = Nothing
-
End Function
-
Public Sub CreateShrtCut()
-
On Error Resume Next
-
Dim wsShell As New WshShell
-
Dim wsSCut As WshShortcut
-
Dim strCommandLine As String 'Command Line for shortcut to run
-
Dim DskTop
-
Dim strYN As String
-
strYN = MsgBox("Would you like a shortcut for this database on your desktop?", vbInformation + vbYesNo)
-
If strYN = 7 Then Exit Sub
-
DskTop = wsShell.SpecialFolders("Desktop")
-
Set wsSCut = wsShell.CreateShortcut(DskTop & "\ShortcutFutures.lnk")
-
strCommandLine = "C:\SomeFolder\Futures.mde"
-
-
With wsSCut
-
.TargetPath = strCommandLine
-
.Save
-
End With
-
-
Set wsSCut = Nothing
-
Set wsShell = Nothing
-
End Sub
-
-
VBScript code to be entered into the VBA code from above. -
Option Explicit
-
Dim strFile, sfol, dfol
-
Dim Cnt
-
Dim fName, ext, strErr, isOpen
-
Dim ws, fso, myApp
-
Set fso = CreateObject("Scripting.FileSystemObject")
-
'File Name
-
fName = "futures"
-
'main folder location
-
sfol = "network path"
-
'Destination folder
-
dfol = "C:\someFolder\"
-
ext = ".mde"
-
Cnt = 3
-
Set WS = CreateObject("Wscript.Shell")
-
If not Folexists(dfol) then CreateFolder(dfol)
-
Do Until Cnt = 0
-
If FExists(dfol & fName & Cnt & ext) And Cnt = 3 Then
-
DeleteIt dfol & fName & Cnt & ext
-
ElseIf FExists(dfol & fName & Cnt & ext) And Cnt = 2 Then
-
RenameIt dfol & fName & Cnt & ext, dfol & fName & (Cnt + 1) & ext
-
ElseIf FExists(dfol & fName & ext) And Cnt = 1 Then
-
If FExists(dfol & fName & Cnt & ext) Then
-
RenameIt dfol & fName & Cnt & ext, dfol & fName & (Cnt + 1) & ext
-
End If
-
RenameIt dfol & fName & ext, dfol & fName & (Cnt) & ext
-
End If
-
Cnt = Cnt - 1
-
Loop
-
CopyIt sfol & fName & ext, dfol & fName & ext
-
WS.PopUp "Thanks for updating to the latest version!"
-
Ws.Run """C:\Program Files\Microsoft Office 2003\OFFICE11\Msaccess.exe """ & dfol & fName & ext,1
-
Set myApp = Nothing
-
Set WS = Nothing
-
Set fso = Nothing
-
Function RenameIt(strFile, dfol)
-
Dim cnt
-
on error resume next
-
strErr = fso.MoveFile(strFile, dfol)
-
if err = 70 then
-
do until err= 0 or cnt = 200000
-
err.clear
-
strErr = fso.MoveFile(strFile, dfol)
-
cnt = cnt+1
-
if cnt = 200000 then
-
ws.popup "There is an error that we are unable to account for at this time." & vbcrlf & vbcrlf & "Please close any open MS Access databases then go to C:\someFolder and open or click on the file named copyMDB.vbs" & vbcrlf & vbcrlf & "If you have any further problems questions or comments please contact your administrator."
-
exit do
-
end if
-
Loop
-
end if
-
End Function
-
Function DeleteIt(strFile)
-
strErr = fso.DeleteFile(strFile)
-
End Function
-
Function CopyIt(strFile, dfol)
-
Dim fso
-
Set fso = CreateObject("Scripting.FileSystemObject")
-
strErr = fso.CopyFile(strFile, dfol, True)
-
Set fso = Nothing
-
End Function
-
Function FExists(strFNme)
-
If Not fso.FileExists(strFNme) Then
-
FExists = False
-
Else
-
FExists = True
-
End If
-
End Function
-
Function FolExists(strFol)
-
If fso.FolderExists(strFol) Then
-
FolExists = True
-
Else
-
FolExists = False
-
End If
-
End Function
-
Sub CreateFolder(strFol)
-
Dim fso
-
Set fso = CreateObject("Scripting.FileSystemObject")
-
If Not fso.FolderExists(strFol) Then
-
strErr = fso.CreateFolder(strFol)
-
End If
-
Set fso = Nothing
-
End Sub
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Manuel Lopez |
last post by:
I have a puzzling form timer problem that I didn't experience prior to
Access 2003 (though I'm not sure access 2003 is to blame).
Here's the situation: a computer has two access 2003 databases on it, a
frontend and a backend.
Case 1: If vba code on the frontend updates many rows (360,000) on the
backend, a form's timer event (from the frontend) will stop firing until the
user gives the form focus. (Note that the update itself always...
|
by: Mike |
last post by:
Hi
Hi
We are currently upgrading from access 97 to office 2003 on site. We
would like to upgrade our access databases to Access 2003 but we wish
to remove Microsoft access software from our end users so that we can
control the creation of databases. Is there a way that our end users
can still use these upgraded access databases without having access
2003 installed on their machines. The mdes will reside on a server so
they can be...
|
by: Terry Bell |
last post by:
We've had a very large A97 app running fine for the last seven years.
I've just converted to SQL Server backend, which is being tested, but
meanwhile the JET based version, running under terminal server, has
suddenly started running very slowly. The network engineer has thrown
up his hands and said "It's Access 97". I've checked out lots of
things including the Oplocks setting and other stuff from this NG, and
I think I've done everything...
|
by: DFS |
last post by:
Architecture: Access 2003 client, Oracle 9i repository, no Access security
in place, ODBC linked tables.
100 or so users, in 3 or 4 groups (Oracle roles actually): Admins, Updaters
and ReadOnly. Each group sees a different set of menu options when they
open the client and login to Oracle.
For the sake of speed I use pass-through queries here and there for updates
and deletes. I update their SQL property in code and execute them.
|
by: jqpdev |
last post by:
Hello all...
I'm coming from a Borland Delphi background. Delphi has a specific
component called a Data Module. In the designer the Data Module behaves
like a windows form. A developer can drop non-visual (controls) on the data
module surface and wire them up and create procedures, functions, event
procedures. In the source file (code behind file) the Data Module is a
class, and the dropped components are public properties. The...
| |
by: Cheryl Langdon |
last post by:
Hello everyone,
This is my first attempt at getting help in this manner. Please
forgive me if this is an inappropriate request.
I suddenly find myself in urgent need of instruction on how to
communicate with a MySQL database table on a web server, from inside
of my company's Access-VBA application. I know VBA pretty well but
have never before needed to do this HTTP/XML/MySQL type functions.
|
by: RayPower |
last post by:
I'm having a system using Access 2000 as both front-end (queries,
forms, reports & temp tables for reports) & back-end (data) with
back-end running on the server. The application runs on the network
with around 15 users.
The back-end database is suffering from performance issues. There were
several corruptions since few months & one major corruption & corrupted
the MSysObjects table. Also, the system will run batch process that
generate...
|
by: Moor |
last post by:
I have a .MDB back-end database that is created with Access 2.0.
And multiple front-end mdb's, some are created by Access 2.0, and the
rest are created by Access 2003.
The fron-ends had table attachments to the back-end database.
The problem is I'm unable to access any table by both versions of
Access in the same time!
Here are the steps to replicate the problem
1- Open one Access 2.0 front-end mdb, then open the attached table T1
|
by: Brian Nelson |
last post by:
Sorry for the long post, I've tried so hard to solve this one but I'm
just stuck!
We've had a multiuser .mdb file on a network share for years. I know
it's not ideal, but it's worked well from Access 2000 to 2003. Ever
since we upgraded to 2007, we've noticed some serious bloating.
When we were on 2003, after a fresh compact, the database would be at
about 20 MB. After normal use it would grow to about 25 MB and then
significantly...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |