Saving A database to the desktop

[ram]

Hi to All,

I would like help with the following question:

Currently I have a database located on a share drive. 500 different user
located throughout the country access this database. Their complaint is the
database takes to long to open and after it does open it takes to long for
the forms to load and retrive data.

When I have the users drag the database to their desktop and then open the
database it opens quickly and things work fine.

Is there a way to create a Macro that would automaticlly save the database
to their desktop after they double click on the database to open it?

I would like the following to happen:
1. User open the share drive
2. User double clics on the database to open it
3. A copy of the database is copied to the user desktop
4. The user open the copied databse from the desktop


Thanks in advance fro any help or suggestion

Ram

 

[Steve Schapel]

Ram,

You mean the whole application including the data will be on the local
machine? So that means any data entry/editing that is done locally
needs to be re-synchronised with everyone else's?

 

[ram]

Yes each time a user wants to access the database they will need to save
over the current database

Thanks for your time

 

[Steve Schapel]

Ram,

So that means that only one of the 500 potential users can use it at any
given time?

 

[ram]

I thought they multiply users would be able to drag it to their respective
desktop. Would you suggest a different soultion?

The user experience slowness when ever they try to down load something from
the share drive.

Thanks again.

 

[Steve Schapel]

Ram,

Certinly you could have multiple users using the application
independently on their local machines. The problem arises when any data
changes made by one user will overwrite the existing data on the
server... see what I mean? User A and User B both get a copy of the
database to use locally. User A enters or edits some data, and then
re-submits his copy of the database to the server. In the meantime,
User B is editing data on his local copy. Then when he re-submits his
copy of the database to the server, it overwrites User A's work.

Or have I missed your meaning?

 

[ram]

The 500 user are basically viewing information that the home office enters
into the database. The field users will not update the database only the home
office.

I have attached a macro to a command button that will update the field
database at 11:00 and 3:00.

Thanks

 

[Steve Schapel]

Ram,

Ah, ok, well that makes more sense.

There are a number of approaches you could take here, depending on the
volume of data involved, the nature of your WAN, etc.

I would expect that this is beyond the scope of macros, and you would be
moving to a VBA procedure for this.

I trust your database is split, with the tables in a separate file. In
which case you would have the frontend application installed on the
field users' computers. When you do your 11am and 3pm update, you would
be making a ciopy of the backend data file on the server. And then you
could have a routine on the remote machines to use a FileCopy method to
replace their local copy of the backend with the updated one from the
server. I think that's the first basic concept I would explore.

 

[ram]

I tried to split the database but ran into problems , so that why I went with
the setup I'm currently using. It seems to work but I know it could be a
little easier forthe field users if I could automate the drag and drop
process.

I'm not real good with VB but will be willingto try.

Thanks

 

[Steve Schapel]

Ram,

Not sure what the problems you ran into. But you *must* split the
database in this sort of a scenario.

I would also recommend you place your database files correctly within
your folder structure on the field users' computers, and avoid the idea
of running it from a desktop.

And as I said, FileCopy method would be appropriate, where the concept
of drag'n'drop is not appropriate. If you type FileCopy into a module,
highlight it, and presss F1, you will see it is a very simple command,
but it is not available in macros.

You will need to run it from an event (command button click for example)
on an unbound form on the field user's application, so that the backend
data file is not being accessed at the time.

 

[ram]

I went back and split the database and will now use the FileCopy method in a
vba moduel. I am going to confirm the path for the source and destination.

Thank so much for your help I will let you know how I make out tomarrow.

Again thank you for your kindness, I don't know what I would do without this
website. it has truly helped me out.

My family needs me and the work server went down.

Thank