One file to many folders !!

E

ECEK

Registered User.
Local time
Today, 21:35
Joined
Dec 19, 2012
Messages
717
whilst Im sure this is possible it is beyond me at the moment but I feel that it could be useful to many developers.

Table1 contains a textfield with the name(s) of the file(s)
Table2 contains the folder names (in this case it is peoples names)
I want to update all of my folders with my latest version.
EG

So DATABASE1.accdb
DATABSE2.accdb
etc

Are moved from their development area: C:\DEVELOP

To several folders
C:\Folders\Doug Roberts
C:\Folders\Mike OHallorahan
C:\Folders\Jim Duncan
C:\Folders\Roger Simmons
etc

What would be great is to just put the file name and the names of the recipents (all other directory info is pre written)

is this possible?
 
This is known as a cross join.
Put both tables into the query designer. Do NOT join them. Pull in the file names and the peoples names to the query fields. Run the query. Does that begin to give you a starting point?
 
So using VBA I want to physically move what ever is in Table1 (DATABASE1.accdb
DATABSE2.accdb)
To all of the folders that are listed in Table2
C:\Folders\Doug Roberts
C:\Folders\Mike OHallorahan
C:\Folders\Jim Duncan
C:\Folders\Roger Simmons
 
Yup - I understood that - but first you need a query(recordset) to provide you a method of driving the process. So does the cross join query provide the right data do that?
 
Is the idea that each user will then go to their folder to get the latest version?
If so, there are better ways of doing this on a network
 
@Colin - I agree.
@Ecek - Simple code loop - untested try it, see if it heads you in the right direction. Personally as Ridders has said there are better more elegant ways to achieve Front End distribution.

If that's not what it for then this should sort of work.
Code: 
Private Sub CopyDatabasFiles()

	Dim db as Database
	Dim rs as Recordset 
	Dim sUserName as string
	Dim sFilename as String
	Dim sTarget as String
	Dim sSource as String

	Set db = CurrentDb
	Set rs  = db.OpenRecordSet("YourSavedQuery",dbOpenSnapShot)  ' Open your query as the recordset

	If Not rs.EOF Then exit sub	' Make sure it's got some records
	rs.MoveFirst	
	Do While Not rs.EOF

		sTarget = TrailingSlash(rs.Fields("YourUsersFilepath")) & (rs.Fields("YourDatabaseNames")
		sSource = "\\YourDatabaseStoragePath\" &  (rs.Fields("YourDatabaseNames")) 

		Debug.Print sTarget & " : " & sSource		'This will print out in the immediate window in the VBA editor.

		'FileCopy sSource sUsername   		'uncomment this out when its working

		rs.movenext
	Loop

	rs.Close
	Set rs = Nothing

Exit Sub

Public Function TrailingSlash(varIn As Variant) As String
    If Len(varIn) > 0& Then
        If Right(varIn, 1&) = "\" Then
            TrailingSlash = varIn
        Else
            TrailingSlash = varIn & "\"
        End If
    End If
End Function

Put the function in a module - call it something like BasicFunctions if you haven't got something similar already in place. It's dead handy when dealing with file paths.
 
Hi Ridders.
Yes Indeed. I have several (named) folders and want to run vba code that runs overnight. I develop the front end and it copies (and over writes) the existing front end in the folders.
Im looking at over a 100 users (Not of the same front end) so doing this manually is a bit of a chore (and a waste of time).

Could you enlighten me as to how best to distrbute or achieve what I want in a more efficient way.
Thanks for your time.

I'll hang fire a minute Minty I might be going down the wrong path.
 
Last edited:
This is what I do.

Place the latest version in a set location on the network.
All users have desktop shortcut that opens a small 'splash' screen - its actually a small separate db
This checks whether the network version is newer that the local version.
If the local version is up to date, it opens and the splash utility closes automatically.

If the network version is newer, all relevant files are copied overwriting the local copies.
I use Windows APIs for this so its lightning fast.
Again the utility closes automatically.

Before it closes, the utility also checks if there is a newer version of itself and if so updates itself.

It may sound complicated but it works so smoothly that users don't realise its any more than a simple splash screen.

I'm just going out but if you want, I can upload the utility later today when I return
If you like it, you can adapt it.
HTH
 
Last edited:
Ridders that would be fantastic. Love to experiment. Its sounds like a much cleaner way to do updates.
 
Attached is my take on this...
Hopefully you will be able to adapt this to your own needs

As explained previously, I use a small Access 'splash' utility to manage FE updates - SDAUpdater.accdb
I prefer this to using a batch file and command window which is also commonly done

This has 2 linked tables
a) tblLocalVariables - linked to the local program file and containing the version info & upgrade folder path on the network
b) UpgradetblUpdaterVersion - linked to a copy of the splash utility in the specified upgrade folder

As with all other similar approaches, the updater checks the local version number with that in the network upgrade folder.
If the local version is up to date, the updater utility automatically closes & opens the program file
If the network version is newer, files are first copied to the local folder & then as above. It uses Windows API call to copy files so its fast e.g. 130MB in a few seconds

The utility also checks if there is a newer version of itself on the network.
If so, it copies that file & renames as SDAUpdaterNEW.accdb

When the main program file opens, it looks for SDAUpdaterNEW.accdb & if it exists, renames it as SDAUpdater.accdb.

The zip file contains the updater utility and a PDF file which explains this in more detail with screenshots.

I recommend you read the PDF file first!

To view the code, open the SDAUpdater.accdb file using the Shift key so you can see the code & adapt the linked tables for your own purposes.

You may not need each step in the code for your purposes

Doing that bypasses the start up code which checks for valid links
You will need to modify the links for your own local / network setup
Unless you use the Shift key, you WILL get errors as you don't have the linked files

HTH
 

Attachments

You must log in or register to reply here.

Similar threads

zelarra821
Solved Help with a bat file
Replies
8
Views
672
zelarra821
zelarra821
J
VB Script: vbs to move selected files and folders in the current folder to a new custom folder by input box
Replies
11
Views
3,122
Isaac
Isaac
NickCoe(UK)
Getting the true location of VBA reference files in 365 ctr.
Replies
17
Views
2,011
NickCoe(UK)
NickCoe(UK)
T
Solved Open Explorer according FilePath filed
Replies
4
Views
697
theDBguy
theDBguy
A
Solved VBA compact-and-repair of a closed (corrupted) database from a new, blank Access file
2
Replies
24
Views
2,155
isladogs
isladogs

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom