[Tech Support] Sorry about this ...

[dancefloorlandmine]

We're currently having joy with a database that's playing up, and the person to blame for credit with creating this has decided to change the way it's set up. Previously, each volunteer had a desktop shortcut to the server copy of the database on their PC (we're ruining Windoze 2K, btw). However, the new cunning plan is to put a front end .mdb on each PC, and replace the current shortcut with the appropriate for the new database.

Copying the .mdb to the individual pcs is no problem (nice shared C: drives, stroke, stroke), but she's in a bit of a quandary with regard to how to be able to manage the shortcut change, without requiring someone to wander around each PC and do it by hand (there are several PCs in Macclesfield and Bristol). If I was going to take the time, I'd use VNC to do it remotely, but I'm not in the office next week (due to the joys of revision).

I've scavved a couple of vb scripts that can create and delete shortcuts on the local PC, but not quite sure how to get them to run remotely on the other PCs. Alternatively, would a simple command line "erase \\machine01\c:\blahblah\desktop\blah.lnk" do the job in a reliable fashion. And would a similar copy instruction do the trick for adding the link (once a sort of template shortcut had been created)?

You see, I truly know nothing! And I'm tired.

Comments

[thepaintedone.livejournal.com]

Oh dear. SQL is much more stable, the trouble is that the two systems work in fundamentaly different ways, if you don't properly tell the Access system that there's a SQL server out there, it will work the way it knows, i.e the Access way, and bad things may ensure.

To put it simply, Access is dumb, utterly dumb, by which I mean there is no intelligence at all on the server end, it's just a file share. If you want to process something in a query, Access pulls all the relevent data down to the local PC, chews it around, then shows you the results. i.e. if you link two tables and ask it to select all the records with 'bob' in the title, it will load both tables into local memory , link them, do the selection , generate the results and dump the excess. It also has essentialy sod all locking, it just puts down a marker (in the LDB) to warn any other copies of Access that come along, then does what it likes.

SQL on the other hand has this wonderfull engine running on the server, which caches all the tables, indexes them (Access has index's but they arent very good), optimises queries to make them run faster and generaly does a very good job of servering data quickly and efficiently to lots of users at the same time.

Sadly, if you use a linked table, Access is completely oblivious to this, so it will still download the entire table to local resources, chew it around and spit out the results. Hey preto you've just removed most of the advanatges of having a SQL server, as you've just turned it into a file server. Even worse, because Access doesn't have proper locking, it leaves cursors and locks all over the place in the SQL server. In an Access backend it wouldn't matter as other access clients would largely ignore them (hence it corrupts so often), but SQL treats them as inviolate, so it won't let anything else touch that data while the Access client is chewing it around. If you couple this with bound forms which leave locks in as long as the user has them open, and Access constantly copying down whole tables to process, you can see why performance can actualy go down, as you aren't using any of the advantages of SQL, and bolluxing up the locking. In fact you can bung up your entire server this way.

To do a simple test, create a pass through query in access (look in the help for how to). This tells Access not to try and process the job itself, but to send it to the SQL server to be processed. As long as your SQL server is set up correctly, it will run much faster, and not leave any nasty locks about.

To be honest though, if you have a big system with lots of tables, you're talking about a lot of work to fix it properly as it takes a very different way of working to do right. Also, at a guess, your SQL server probably isn't very optimised either in terms of indexes and stuff. Things that Access does for you, but which need configuring in SQL.

Might be worth getting together to discuss over a pint sometime.