Topic: MS Access on SME

[jbillings]

We recently installed SME for an agency that has about 10 -15 staff and were using a zip drive and sneaker net to share a contact and client tracking database which was created for them by a third party.  I do not have access to the form data so that is kind of a handicap.

A team of volunteers networked their facility and setup the SME server version 6.0 on a 2.6 GHZ IBM Thinkcenter PC.

Now that they have put the database on the SME server, they have noticed that the search function doesn't always work.  I am no ACCESS expert but this is what I have observed: when you double-click on a text field, like last name for instance, it seems like a macro is executed on this event, and opens a new form in a popup menu where you can enter search terms. When the database is running on a local machine the search function works fine. When installed on the network and accessing the database over the network, the irratating thing is, sometimes the search function works, and sometimes it does not. I've experienced this on both a Windows 98 and XP Pro computer.

I've set op-locks to be off in Samba, as I have read that can cause problems. I am pretty much stumped with this problem any advice on how to proceed with troubleshooting this problem would be appreciated.

[RonM]

How are they set up -
was the Access dbase imported into MySQL and ODBC configured on the client to allow Access to connect?
or, was the "sneakernet .mdb" file simply saved to an ibay and access opens it through Samba?

I'm guessing it was #2.

MSAccess, while remarkably powerful given its ease of use, does not scale well over a dozen users or so, even using the top method. Using the bottom method, you're going to see file locks occur under some circumstances - for instance, someone posts a change just before you click "search". It's one change at a time mode.

Remember, sneakernet gave you a dozen copies of the same .mdb file that they must have synchronized by hand somehow. There would not have been any "simultaneous access" issues

I think I would try to find somebody locally who is familiar with this stuff - dbases are a world of their own. Good Luck!

[Anonymous]

Yes it's number #2.  Interestingly, each database actually consists of two .mdb files.  One is in a directory called Application and the other is in a directory called Data.  So basically there is an application.mdb and a data.mdb file for each database. The actual tables are in the data.mdb   So even if a user made a copy of the application database to their desktop, the path to the actual data.mdb file is absolute and it appears to be a safe way to update the database, utilize the search function (since the file is stored locally) and the data would still be synced with the real data.mdb file.  I don't know if this is the best way to do it, but for now I think it will work. I wonder if other peole have experienced MS Access weirdness when it is stored on an SME server.

[Ness]

It is the norm to split an Access DB into two databases, even in standalone apps. The Application.MDB file is (by the sounds of it) the database that contains Forms, Queries, Reports and Macros. The Data.MDB will simply hold tables linked (referenced) by the Application.MDB database.

Ron is probably spot on with his assessment of user counts but there are values that can be tweaked in Access that let you set timeouts and stuff.

I can email (or post here) cut and paste info from my Access XP help file about this if it helps you?

Lemme know!

Chris Elliott

[Ness]

PS - I'm no SQL or ODBC specialist - I'll leave that to others, but shoving the data.MDB on the server in an i-bay works for me!

CE

[boringgit]

Personally I wouldn't know where to satar, but should it help, here is an FAQ a guy I know wrote explaining how to import data from an Access database into MySQL.

http://www.by-users.co.uk/forums/?board=faqs&action=display&num=1050430682

[Anonymous]

The solution to this problem seems fairly obvious to me now that I understand it a little better. The problem is the Front-End part of the "split" database needs to be installed on the client computer, and the Back-End which is actually where the tables and data are stays on the server.  

This guy Tony Toews has a good resource for information on Split Access Databases: http://www.granite.ab.ca/access/splitapp/overview.htm

[rexgaylord]

Or a copy of the front-end for each user on thier networked home drive with it relinked to the 1 back-end works for me.  I've linked them using UNC paths and mapped drives with sucess.

[jbillings]

I thought about doing this also, actually it was while I was experimenting with this idea that I accidentally moved the BE, breaking the path, and began to realize how it all worked.  I figure since most of the computers on the network have good sized hard drives, might as well install the copy of the FE locally, I mean it seems like it would reduce a lot of network traffic.

[briank]

Hi - I am an AXS programmer. The front end should be on each client computer and linked to the data - Tools - Addins-Linked Table Manager (not installed by default). AXS pulls lots of dll files accross the n/w generating much traffic if the frontend program is run over the n/w. There are also timeout values you can set for queries which at times I have had to tinker with. I have had no problems with large databases and 10 users with the data just sitting in an ebay.
Regards
Brian

[sqlerror]

We run prox. 35 different access databases on a sme in a winnt4 environment. The front-ends are kept on one networkshare, the backends (the tables) are kept on anotherone. All (65) users open their frontends via a shortcut in their startmenu. We did not encounter any timeouts or macro's modules misfunctioning. The main database holds over 1,5 mil records. Searching on primary indexes is fast, even on slow pentium 1 233 systems. The front ends are equipped with active-x controls to communicate with our dialers and to access a webbases sheduler. Both systemfiles for these active-x components and stored on the share that holds te front-ends. Performs well. Maybe develop your own forms on top of the backend-tables.

Cheers