Splitting the DB (1 Viewer)

I have read through a couple of questions about splitting into FE and BE database, I have an odd situation and thought I would reach out to see what the best solution would be. I have created a database that I will have 3-8 users updating and entering info at sponsered events. The issue that I am running into is this, the share drive that we are required to use in the office is internet based and we don't always have an internet connection when doing these events.

I am trying to figure out the best way to update info from the front end to the back end if there is no internet.

When splitting the DB will the front end forms hold the information until it can connect? or is there some other way to upload info when I am able to connect again?

:banghead:

Running an Access db as backend over the internet is not impossible but asking for trouble: an Access file gets disassembled and reassembled and this process is fraught with challenges over a WAN. For WAN see http://www.access-programmers.co.uk/forums/showthread.php?t=241257 Use mysql or ms sql or any other server db (which Access is not) as backend and you may be able to do it.

For no-connection situation the answer is that your Db must be able to stand on its own legs locally, and you then update central data when connection is available. This is assuming that updates only in the local-to-central direction are required

but note that merging data from otherwise discrete databases is also not trivial, and may involve other issues

eg, what if two different distributed databases both add the same customer with different ID references. How can you then merge the two?

if you don't have an internet connection at an event, then any sort of accessing the db at the office or held on the web will not be possible.

If all your users are at the same event, then you could set up a localised network between each users machine - suspect the simplest way would be to use a router (which are not expensive), hardwired to each machine for preference but otherwise wireless, so you can share the backend held on one machine.

If the backend is a mdb then you can use replication make the office db the master copy and create a slave copy for the event so you can synchronise the data with the main db when you return to the office. Just because your front end is a an accdb, does not mean the back end can't be a mdb.

Otherwise if your users are only collecting information and not changing pre event data (like existing customer data), you don't need replication just an import process to update the gathered information.

Thank you for all your replies, just a few more questions.

1. The event will be both adding new customers and updating old customer information, the customer information is further split into 6 different tables connected by the customer ID number. If I just put the DB locally on all machines and merge data later in the office, can I create a coded button to do this merge automatically?

2. Would I need to create a Dummy Copy of my DB to hold temporary infomation and then run an update or append query to merge later?

BTW I know that a web based SQL is my best option I am currently working on getting that piece completed, however the way the office is set up I have to request someone else to make it for me as they won't allow me to download the program on my machine, and it will probably be 6 months before I even get a beta version, and we have 5 events scheduled in between, trying to find the best way to collect data until I can get the application completed.

All of your help is truly appreciated

vnfoster,

As others have said any proposed solution using multiple "offline" FE or local databases can be a nightmare with respect to modifications/additions to "established" records.
This is especially true if multiple people could gather adjustments to the same records.

Your idea of collecting data and merging it with the "official" version later may work. You might consider those activities at the events being more like transactions rather than updates to records. Each local database could identify who inputted/collected this transaction data and at what date/time. This, along with identifying fields with respect to the info gathered, would definitely be helpful in any subsequent merge activity.

You know the environment better than we do, and you have a better handle on how much duplication/revision to existing data is normal and/or tolerable with any method you might consider.

If this is a major part of your business, then you might make a business case to put more priority on a web based solution.

Good luck.

BTW I know that a web based SQL is my best option

Click to expand...

we don't always have an internet connection when doing these events

Click to expand...

Does not compute - not much point in having a web based anything without a connection

I have a client who provide a booking service for events - primarily handling delegate details and requirements such as pickup from station/airport, dietry needs, accommodation, presentation events booked etc.

This data is collected prior to the event and at the event a registration service is provided for delegates checking in, at which time details are checked, vouchers issued etc. At any stage during the event delegates may want to change their details, or details are amended/added.

They also suffer occasionally with poor/slow/intermittent/non existent internet connection and use the solution outlined in my post using a replicated mdb.

When an application requires to be deployed in a multi-user environment, it is very convenient to split the database into two groups. The first contains the client application: database with forms, queries, reports, macros, and modules. The second contains only one or more database tables. The client application is distributed among all the workstations and connects to one or more databases stored on the server.

It is important to clarify that the term server is not very suitable for "Jet Engine and Access Database Engine" databases (mdb, accdb, etc) and it only refers to a shared folder on the network because no data processing task is executed on the server, but on the client.

By implementing the client – server design:



* All users share the same information.

* Maintenance tasks may be carried out in the application objects (forms, reports, etc,) without placing the database offline.



Perhaps the easiest method to implement this design consists of attaching all tables of the database server in the client application. For security and performance reasons, this method is not recommended for real server databases like a SQL.