[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

[valkyriekaren.livejournal.com]

*hugs* because there were whole swathes of that I didn't even understand at all.

You're clever, you'll work it out. And on Sunday there's a picnic and I will feed you chicken.

[d-floorlandmine.livejournal.com]

Ooh, thank you!

Only bugger is that I'm trying to work this out for tomorrow, as I'm not in all next week, in order to get my mind off work and onto revising, and it looks like they're going to have to sort the database issue over tomorrow and next week - as it keeps corrupting. Daft setup anyhow. SQL core data on server in london, front-end with translating lookups in london, bristol, and macclesfield, and multiple users simultaneously using each front end. Should the connection between london and the other sites even flicker momentarily, it goes pear-shaped! Oh well, not my design, and I wasn't even consulted on that one!

[mooism.livejournal.com]

Yeah, Windows shortcuts are just .lnk files, you can copy/delete them from the command line no problem. Presumably you can create them with VB.

[d-floorlandmine.livejournal.com]

Yeah - I've got a VB script which will do the job (yep, I'm just a script kiddy!) Just having a slight issue copying them to remote machines desktops successfully.

[eeymsmo.livejournal.com]

copy "c:\some-link.lnk" "\\remote-machine\c$\documents and settings\all users\desktop\some-link.lnk"

Should do the job nicely. or always has for me.(dropping it in all users will make sure everyone who uses the machine get's it. And it'll be easier to clean up when the next change comes around.)

[d-floorlandmine.livejournal.com]

Excellent. Cheers dude. That's a pint our head of research owes you.

[incy.livejournal.com]

it has vbeen awhile since I used accss, but when i did it, i put an executable only fromt end on the pc (i think they are mdx files) linking it to a mdb on the back end which contained all the data (it makes change maangement easier and gives youa bit of security0, but just linked the tables in the back end system to the front end, hence no need for shortcuts at all. Or am i missing something here.

[d-floorlandmine.livejournal.com]

Not missing anything - just that, to coin a phrase, "our users have varying levels of ability"¹, and wouldn't all be able to open the database without a shortcut in the middle of an otherwise almost empty desktop. Hence, unfortunately, the need for shortcuts.

At the moment, it's a bit of nightmare:
User on PC -> Access database on local server -> SQL server in London

The plan is apparently to move to:
User with Access db on local PC -> SQL server in London

Me, I'd probably prefer:
User with Access front end on local PC -> SQL on local server (with data capture queries which echo the captured information to the core research database in London - or build the research database so that it queries the local data when the queries are run). That would at least proof the data collection against VPN glitches, but I'm not technically involved in the development of this one - just the fire-fighting ...

¹ One of the guys on our support helpdesk liked that one so much he decided he was going to make a note of it as being one of the most diplomatic way of referring to lusers he'd heard.

[eeymsmo.livejournal.com]

.mde for the access front end. (mdx is some sort of borland jobby I think, but it's been many years....)

It's easier to shortcut to the front end as well. That way when you want to upgrade you just upgrade the copy on the server and next morning everyone's got the new front end. Not the most elegant solution, but it works.

[d-floorlandmine.livejournal.com]

Yeah, .mde is the encoded front end. Currently everyone is using a shortcut to the server, but it keeps corrupting. Then again, shouldn't really surprise, as was developed in-house by someone who, when she started, had less Access knowledge than me. Ho hum.

Thanks for the help, anyhow!

[damerell.livejournal.com]

Neither did I - I know nothing about Windows through a process of strategic incompetence.

[thepaintedone.livejournal.com]

Ahh, the joys of Access. I've been deving in Access since version 2 and still do so on a daily basis despite repeated attempts to leave it behind forever.

First up, I think the advice above about shortcuts will do you fine, it's just an .lnk file which you can create, copy, delete like any other.

With regards to the changes, what you were doing before is very bad and wrong and as you've mentioned will corrupt the hell out of the MDB file on a regular basis.

With regards to getting the client out to the users. I would advise creating a folder on every PC (e.g. C:\Database ) and setting up a login script to copy the client from the file server to there, with a -y to overwrite without prompting, whenever they log in. A batch file on a file share so they can also manualy update can be usefull too. You can then create a simple desktop shortcut to the client. I tend to put a label on the main menu, with the date and time I released the client, so I can get a user to easily read it off to me so I can check what version they are running. If you do an automated copy though, watch out for the folder permissions. If you are hot desking you can get a situation where a new user doesn't have permission to overwrite the file that was put there by the previous user, as login scripts run with the users credentials.

As a very strong recomendation, deploy the client as an MDE. This has several advantages.

1. The users can't open it in design mode, so they can't screw around with things.
2. It's a lot smaller so it's quicker to copy down to the client PC.
3. The VB is pre-compiled so it runs noticably faster than an MDB.

Another tip. If you buy one copy of the developer edition of Access (well office), you can deploy the runtime edition, license free, to as many workstations as you like. This means you can buy a cheaper copy of Office and save you a stack of cash, very usefull for a chairty I'd imagine. For my 50 users we saved a couple of grand doing that. It also makes it impossible for the users to screw around with your databases! Work's very well in conjunction with MDE clients.

Incidentaly, switching to a SQL backend for access, while doable, is not nearly as easy a task as you might think. I've done it once before, and am doing it again now. Unless you have designed your system very carefully to expect this (v.unlikely) you have to essentialy re-build the entire thing. In fact what you mostly have to do is replace all the built in access functions with your own code modules. E.g. instead of having a bound form, you write a VB routine to populate the unbound form for you. You can't just put the data into a SQL server, then move the linked tables to that instead of an Access back end. The access client has no concept of proper SQL procedures, and if you allow it direct access to your SQL backend (i.e a linked table) it will bollux it up completely and performance could well be worse than with an access backend. e.g. Access will still copy the entire table down to the local client, and process it there, rather than letting the SQL engine do the work for it. It'll put cursors and locks all over the table while doing it as well! Linked tables to a SQL server is v.bad.

Without wishing to tread on your dev's toes. I've been doing exactly this sort of thing, to pretty horrendous depth, for many years now. So if you need any help or pointers on Access, drop me a line.

[thepaintedone.livejournal.com]

Managed to miss this before my other reply.

Using Access to stage a SQL connection is about as bad an idea as I can think of. Access is an horrendous SQL client unless you set it up very carefully.

I'd say that your best setup, as you seem to agree, is Local client (written in whatever) > Local SQL server <> Merge Replication to central distributiuon server.

As long as you code the client carefully, this will let you do inserts and edits at any site, and all sites will have all the data. All the sites can also run independantly if the connection goes down.

I put together a very similar setup with offices in 6 different countries. Access clients talking to local SQL servers, each server dialled in overnight to the central server to exchange all the updates. Worked very well.

[d-floorlandmine.livejournal.com]

Good planning sir. Most of what I know is stuff that I've picked up through doing it wrong. Or appealing to my elders and betters out there in LJ-land. I know enough to get myself asked to do things, and not enough to do them.

[d-floorlandmine.livejournal.com]

Thanks for all that - I'll pass it on in digest form! And as for our "developer" - 'tis merely the Head of Research. (Who is, incidentally, leaving at the end of July, as her husband's UK contract is coming to an end, so she's heading back to Oz.)

Shortcut advice worked a treat (although I had to reiterate part of it - don't forget those quotes when passing the location strings of the files - and beware the hidden . extensions courtesy of looking at it through Windows. I was using dir in a DOS box, so spotted that one!)

It's already running with an SQL backend. As for coding, well, she doesn't really know any VB, so I believe that all the automation at the Access end has been put together with Macros - and left as Macros. I've used .mde's to idjit-proof my databases before - but I don't think that she knows about them.

I'll look into the possibility including the copy function into the login script for the volunteers - fortunately they share profiles, as they have very limited permissions anyway.

Thanks for the offer - you may well find that it returns to bite savage you wildly.

[damerell.livejournal.com]

Ah, the more you get asked, the more you learn - and generally speaking any decent UNIX admin can muddle through most Windows problems - which is why I have found it so important to avoid learning anything at all.

[d-floorlandmine.livejournal.com]

Aha - but I'm not really any sort of Admin at all - "I'm just a simple lad with ideas above his station" (Skyclad) [grin]. Fundamentally speaking, this job started out as being a data-entry monkey.

[thepaintedone.livejournal.com]

Macro's are horid. There is nothing a Macro can do that VB can't do infinitely better and easier!

I started out as a Macro programmer, but as soon as I got my head round a teeny bit of VB I threw them away and never looked back. It looks scary at first, but is actualy way easier once you realise what it does as you don't have to jump through as many hoops as you do with Macros.

I'd be very intressed to see how you are back-ending it to SQL server without using VB though. Most likely it's using linked tables, which could well be slaughtering your server performance. Could be on pass through queries though, which isn't as bad, but not as usable either.

More than happy to advise on Access and/or SQL server, I've been doing it so long it's almost a hobby now. to be fair though my SQL is pretty rusty as until about a month ago it'd been two years since I last worked on one. I also do occational freelance Access dev if you want some more involved work doing.

[d-floorlandmine.livejournal.com]

All hail VB! Yep, I'm a worshipper of VB as compared to Macros. Since my first Access database, if it's got to do something, I want to do it with nicely commented VB. I used to have to (in Previous Job^TM) periodically work out where an uncommented database which relied heavily upon uncommented macros (and vanishing temporary tables) was producing anomalous reports such as the delivery of 32.5 BMWs to a customer¹.

I think that she's using linked tables. From what you've said, that would explain the daily corruptions. And as for the freelance Access dev, you might just have sold your soul, my son ... [grin]

I passed on your suggestions, but She Who Will Not Be Mentioned is slightly narked that no-one told her there could be a problem with SQL, and she was in fact told that it would be more stable using SQL as back-end. I did mention that Microsoft would be unlikely to want to mention that two of their applications didn't play nicely together.

¹ My initial suggestion that it might have been a convertible was appreciated by my manager, but not passed on to the customer.

[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.