Keeping tables online so we can share data but keep using our forms reports offline?

[kingdoz]

Hi Guys,



Would anyone know if it is possible to have the tables only of an Access 2007 table saved online, and the front end offline on a few Windows machines that have Access installed, so that the tables are always up and up-to-date and can share the database content from a few Pc's anywhere in the world that have slightly different frontend forms? Is this possible with Access? Also, will it be very slow if it is indeed doable even though the heavy stuff like reports and queries and forms are local? I was suggested by to convert the database to SQL and build web forms for it, but Im not sure if I wish to do this as I can only use Access (im not a techie) and wish to improve the database and built on it in the future easily.

Thanks
Doz

[John_G]

Hi -

What you are describing is called a split database, and is the recommended setup for multi-user access, that is, tables (back end) on the server, and the forms etc. on individual PC's (front end). But having what you called the "heavy stuff" local is of no benefit, since the queries and reports have to get the data from the server, and in many cases they will have to get all of the data from the tables in order to satisfy the query. In otherwords, if a query will result in only 10 records out of 50,000, Access will still have to retrieve all 50,000, since the query processing is local.

If the database activity you are describing will be frequent, and the amount of data is substantial, then you should look at SQL Server if you can afford it, since in SQL the queries are processed on the server, and only the results are send back to the originator.

HTH

John

[RuralGuy]

Or maybe a Terminal Server like Citrix.

[kingdoz]

Hi,
Thanks for all your help guys. We actually have SQL server installed in our company server as we have a partner account with Microsoft. However, if we convert the back end to SQL if I ever need to update Access I am screwed as would not be able to do this myself (I can only use access as its so easy). Is there perhaps a way to convert the SQL database back to Access to make changes, then back to SQL to use as and when needed? Sorry if I sound like an idiot. I just have this task to do and a low budget so can't afford hundred of pounds to get an expert in or pay every time we need a change done.
thanks
doz

[RuralGuy]

Access can update an SQL BackEnd, or are you talking about something else?

[kingdoz]

Hi, was actually asking if once converted to a SQL backend, can I convert back to access to make changes (then back to SQL)

[RuralGuy]

What sort of changes do you expect would require converting back to Access?

[kingdoz]

If for instance I need to add a new table, or change something. I do this quite often as I'm always trying to improve the database with more functions for our office.

[RuralGuy]

Hi, was actually asking if once converted to a SQL backend, can I convert back to access to make changes (then back to SQL)

I have no experience so cannot offer a useful answer.