Copy DB file to new location, and open without warnings

[Presto]

Access 2003
I'm trying to eliminate unnecessary network traffic, and stop a warning
screen from popping up.
(Multiple users scenario)

I need to copy a database file from a network location to a local folder,
then run the local copy.
I accomplish this with a desktop shortcut to a "dummy.mdb" with the
following code on the startup form. After the code runs, the dummy.mdb
closes :
----------------------------------------------
Private Sub Form_Load()
' This copies the db from the network location and saves it locally, then
runs the local copy.
' After all that, this file will close - it takes about 2-4 seconds.

DoCmd.SetWarnings False

FileCopy "\\networkpath\mydatabase.mdb", "C:\data\mydatabase.mdb"
Application.FollowHyperlink "C:\data\mydatabase.mdb"

DoCmd.SetWarnings True
DoCmd.Quit

End Sub
-----------------------------------------------

This code works perfectly, *BUT* for the annoying message from Bill G...:
" Hyperlinks can be harmful to your computer and data. To protect you
computer, click only those hyperlinks from trusted sources. Do you want to
continue?" Yes/No. After clicking Yes, everything works like I want it
to.

I tried setting the macro security to low, and as you can see above, I tried
turning off the warnings, but that doesn't seem to make a difference. I
can't have 100 people calling Tech Services crying about this silly error.

Also, I would also like to know how to deal with network issues. If the
network path is not available, then just run the local copy if it already
exists.(Day old data is much better than no data to work with)

Any life saving suggestions out there??

Thanks in advance,
Presto.

 

[Arvin Meyer MVP]

The problem has nothing to do with Access, it is a Windows problem which
your IT staff may be able to address through Group Policies.

 

[Presto]

Thank you for your reply. I was hoping that wasn't the case. I guess I'll
have to change my approach to this. If I have to get the IT department to
look at Group Policies, it means at least a four to six month delay on this
project. Currently the copy/paste/run is done with macro that calls a batch
file. I wanted to eliminate that extra step, but I guess I can't .

How about if after the copy command, I reference a macro, then set the macro
to run the db file ?
Hummm..... off to try that.

 

[Presto]

OK I fixed it!!

I have the VBA code on the OnLoad:
-------------------------------------
Private Sub Form_Load()
DoCmd.SetWarnings False

FileCopy "\\mynetworkpath\filename.mdb", "C:\data\filename.mdb"
DoCmd.SetWarnings True
DoCmd.Quit
End Sub
---------------------------------------
Then, when the form closes : OnClose code refers to a macro and runs it
In the macro, I put :
---------------------------
RunApp ( msaccess.exe C:\data\filename.mdb )
Quit (Exit)
--------------------------
This works perfectly!!! It copies the file I need to the local drive, and
then runs the local copy. No warning messages at all.

Now the only thing I would need assistance with is making the VBA code check
to see if the network exists, if yes then do the copy , if no then Show a
warning "Network is unavailable. You are using a local copy." OK button
only, then quit (which will force running the local copy)

 

[Stefan Hoffmann]

hi,

I have the VBA code on the OnLoad:
-------------------------------------
Private Sub Form_Load()
DoCmd.SetWarnings False

FileCopy "\\mynetworkpath\filename.mdb", "C:\data\filename.mdb"
DoCmd.SetWarnings True
DoCmd.Quit
End Sub

RunApp ( msaccess.exe C:\data\filename.mdb )
Quit (Exit)

Instead of using Access to copy the file I would either use a batch

@echo off
copy "\\mynetworkpath\filename.mdb" "C:\data\filename.mdb"
start path\msaccess.exe C:\data\filename.mdb

or an aquivalent VB Script.


mfG
--> stefan <--

 

[Presto]

Stefan,

Thank you for your reply.
It is set up to use a batch file now. I wanted to eliminate the batch file.
And I did that successfully by adding the code listed below. I need to
streamline this process as much as possible to
1. Speed up the processes / make the databases more efficient - (I have
already elimated several useless time consuming steps the administrator had
to do.. )
2. Reduce Network Traffic and "Multiple User" issues by running a local copy
3. Make the database maintainence easier. One Excel data file (instead of 8)
and Two DB files(instead of 4) is much easier to handle.

Now the only step I need to complete is the If / Then / Else part to check
for the network as outlined in my prior post this morning. Then I'm done.

 

[Stefan Hoffmann]

hi,

Thank you for your reply.
It is set up to use a batch file now. I wanted to eliminate the batch file.
http://www.granite.ab.ca/access/autofe.htm

Also, I would also like to know how to deal with network issues. If the
network path is not available, then just run the local copy if it already
exists.(Day old data is much better than no data to work with)

I'm not quite sure why you are doing this. The normal setup would be:

A backend database on a shared server folder to store the data only and
a frontend database on a local folder. The frontend only needs to be
refreshed when changes occured to it. Thus the AutoFE.

Otherwise I don't get your point (also from your first post).


mfG
--> stefan <--

 

[Presto]

I know how to split a db, and in most cases, it would be appropriate.

Using a frontend/backend is *not* appropriate in this case, because the 100+
end users **must always** have access to the data(even if it's 1 day
old-which is OK). If there are network issues, (which happens sometimes)
then the data is not available. A local copy is *critical*.

 

[Presto]

OK. I finally solved my own problem. It works like a charm! Check it out:

------------------------------------------------------------------------
Private Sub Form_Load()

If Dir("\\networkpath\mydatabasefile.mdb") <> "" Then
' Then go ahead and copy the file to the local folder
DoCmd.SetWarnings False
FileCopy "\\networkpath\mydatabasefile.mdb",
"C:\data\mydatabasefile.mdb"
DoCmd.SetWarnings True
DoCmd.Quit

Else
' If the network path does not exist then just use the local copy
but warn the users that it is yesterdays copy.
MsgBox "The Network is not available." & vbNewLine & "You will be
using yesterdays copy today." & vbNewLine & "An email will be sent when the
network issues are resolved. ", vbOKOnly
' By exiting the form, the macro code attached to the OnClose
event runs to start the local copy.
DoCmd.Quit

End If
End Sub

 

[Arvin Meyer MVP]

So before your DoCmd.Quit line, why don't you start the new instance:

Call fHandleFile("C:\data\mydatabasefile.mdb", WIN_NORMAL)

where fHandleFile is:

http://www.mvps.org/access/api/api0018.htm