Replies

Just in case anyone reading this gets confused by terminology, “shutdown -t nn” means the shutdown will START in nn seconds. So anything other than 0 DELAYS shutdown. -f forces running programs to close which can cause loss of data, like when you shut down normally and a dialog pops up asking if you want to force close some sticky programs. Sticky programs might be waiting for a save instruction. Shutdown -t 600 is used when shutting down a server so users have ten minutes to save their work.

One client of mine was bought out by a bigger organisation. BigCo’s IT procedures stated that the only permitted way to shut down a desktop computer was to use the start menu shutdown option (in the days when there WAS a start menu…) Anything else was a sackable offence! So shutdown problems led to a call-out so a technician could hold the button down for 10 seconds…

To answer some points in more detail.

Access SQL / ANSI SQL / T-SQL
There are as many flavours of SQL as there are SQL database systems. ANSI is in theory the “standard” and most SQL dialects support most of ANSI. Access is very non-standard. Once you migrate to a server based SQL it is good practice to use ANSI syntax and functions wherever possible. That will assist when getting help from tip sites (like this), if someone else has to look at your code, or if you have to move to a different server (like MySQL). Some things are hard to do with ANSI and in those cases just bite the bullet and use the local dialect. Small things I’ve made myself do include using single not double quotes for string values and putting a space after the ANSI comment marker (–) as MySQL requires one. And I use COALESCE() instead of NZ() or ISNULL() as the latter have wildly different effects in some dialects.

Queries v Views
Access queries tend to pull large amounts of data from the database. If you make regular use of queries on large joined tables create a view in SQL server that handles the join, import that into Access as you would a table, and query the view. In my experience SQL server then optimises the join process and ODBC passes the query criteria down to the server for processing, returning only the results not the entire view or (if you don’t use a view) the entire tables to be joined. The view editor in MS SQL Server Management Studio is very similar to the query editor in Access (just arranged in columns not rows)

Deploying
Google for Task Scheduler. Create a batch file on each user PC with a command like

Code:

xcopy \serveraccessstufffrontend.mdb c:accessstuff /D/C/R/Y

(With appropriate files and folders) Set up task scheduler to run that batch file every night just after the nightly backup, and/or on startup. It will overwrite the user’s current Access front end with the latest version from your file server. Deploying updates only requires you to update the server copy.

Ian.

The articles others have referred you to should help, but a few other suggestions.

Use Windows Authentication. SQL Server authentication is insecure in a networked environment. Windows is more secure than your current system (non-users can’t steal the database), doesn’t require users to have two passwords (one for Windows and another for SQL) and doesn’t risk exposing passwords. Years ago I had to set up an Access front end to an existing SQL database with SQL authentication and someone knew the passwords within days. Once someone knew them, I had to assume EVERYONE knew them.

Access SQL and SQL Server SQL are different languages. Especially where functions are concerned. For a simple migration you should only move the tables, not the queries, so you can leave queries in Access syntax. If your front end relies on manufacturing SQL queries to pass to the server you may have to change them. There are many sources of knowledge for SQL Server but I always use SQL Server Management Studio to manage my SQL databases and its online help (SQL server books online) is very comprehensive. If you resort to tips sites and boards make sure you get tips about T-SQL (Transact-SQL) as other flavours (MySQL etc.) are different.

You’ll find plenty of sites online that highlight the language differences, perhaps the most awkward (for all sorts of reasons) is that the text delimiter is a single quote ‘ not a double quote “. (actually, you can change that behaviour in T-SQL, but in my view you should live with it as while all SQL flavours allow ‘ they don’t all allow “).

Once you’ve done the basic migration consider moving other parts of your application to the SQL database. Moving queries to the database (as T-SQL views) means the bulk processing is done on the server and only the results pass to the client. As SQL Server is a pretty fast system that can speed things up considerably.

As for deploying, others have pointed out that you’ll have to set up an ODBC DSN on each user’s workstation. I suggest you document that procedure thoroughly with screenshots, one day you’ll have to tell someone how to do it for themselves, usually on the phone from your favourite holiday spot. It is a rote procedure and with Windows authentication you don’t need to publish the passwords. As for front-end updates, you can use a scheduled task on each workstation to check for a new version on a server every night – just use xcopy with a date check and no overwrite check. Document that procedure too!

Finally, think about what happens when multiple users are reading and updating the same data at the same time. For example the scenario A reads record, B reads same record, A updates, B updates can lose A’s changes or make B’s changes invalid.

Ian.

I only included the ldb bit because that has been the cause of more than one support issue – it’s bad that MS defaults Windows to hiding the file extensions and then naming .ldb so it appears above .mdb in alpha order!

Ian.

How unresponsive? Do they give a context menu if you right-click? If not, I have no ideas. If they do, here are some avenues to explore.

I suggest it is the Access application that is unresponsive – i.e. on clicking the file icon, Windows tries to launch Access but Access doesn’t launch, or at least doesn’t get as far as a client window. Try running Task Manager before clicking and see if an MSACCESS.EXE task opens and closes. If it does, then there might be an issue with the file permissions on the server. The first user to open the database creates a .laccdb or.ldb file, which will get default folder permissions, and the subsequent users must be able to open and write to it. Also maybe something with the Windows 7 client firewall settings.

I assume from point 6 that everything works if you run Access and use file->open. What happens if you try to open the files from Windows Explorer while you have Access open? That circumvents the Access launch.

I also assume you’ve checked the obvious – that people are opening the .accdb/.mdb files not the .laccdb/.ldb files.

While checking what the lock file for a .accdb database was called I tried to open a .accdb database while I had a .mdb database open and Access locked up completely. Though both databases open fine individually. Don’t understand that one myself, but it illustrates the point that the file icon might appear unresponsive because Access itself is unresponsive. The .mdb eventually responded but the other had to be restarted after closing the .mdb.

Ian.

Connecting to a domain server from a workgroup PC is straightforward, including mapping drives. You just need appropriate credentials. I’ve been doing it for years and it is common for clients moving up from a small office setup to have a few PCs using “home” OSes that can’t join a domain. Also, Windows is increasingly friendly to the BYOD trend. If anything it is the easiest connection to make because you don’t have to have Windows Professional and you don’t need to register the PC on the domain or (usually) faff with DNS. The detail can be a little tricky, it helps the client to have network discovery turned on along with file and printer sharing. If there’s a hitch you might need to flush out old connections and credentials. Detail also depends on the server and client OSes.

For a typical case, obtain credentials for a user-level domain account. Lets say the server is called “server”, the domain is “domain”, the user is “user” and the password “password”. There’s a shared folder on the server, \servershare (we just made up the world’s least secure system). From the client PC just click the start button (or go to the start screen) and type “\servershare” (and return!). If the server is unreachable there’s a DNS issue – try replacing “server” with the IP address of the server. You should be asked for credentials. If necessary (username disabled), click “log in with a different account” or similar. Use “domainuser” as the username and enter a password. Click the “remember these credentials” box and go. That should do the trick.

Sometimes there’s a problem with mismatched credentials – maybe someone once tried to connect without a valid domain account and Windows remembers the account and the credentials. You can only ever connect one client PC with one set of credentials. To kill any latent connections, open a command window (type “cmd” on the start menu or page) and then the command “net use * /delete”. To kill any incorrect credentials open Control Panel, go to User Accounts, then Manage Credentials and remove any credentials for “Server” (or the real name).

I would suggest that the server be set up as DNS server and either DHCP server or the actual DHCP server offer the server as DNS server. Windows Server likes to recognise incoming connections and the do that it needs to see them in DNS. You might find yourself locked out sooner or later if that isn’t the case. (Check the server DNS forward lookup zone has your client’s IP address in it)

I’m being a bit naive, there are other issues that can prevent a connection like firewall rules and security policy on the server, and I’ve written this straight off the top of my head without checking some actual command names but in principle it is easy…

(Warning to sysadmins out there – this client PC is now a trojan horse. It has lots of rights and no responsibilities. If left on a train, your entire network is vulnerable to the “user” account. MINIMUM RIGHTS PLEASE!)

Ian.

Depends on what you are trying to achieve. As the Turkish President found out when trying to ban Twitter, there are ways around anything on the Internet. You don’t need DNS or HOSTS to surf the net, I have a few IP addresses memorised for use when DNS is down, and especially 8.8.8.8 (Google DNS) which allows me to use NSLOOKUP to find a website’s IP address when DNS is not working. (Note that some websites won’t respond to an IP address because they use a system called named virtual hosts.)

To use the method, get into your network adapter’s properties. Find the TCP/IP V4 protocol and look at its properties. Set DNS to “these addresses” and set them to something wild like 111.111.111.111. You now have no Internet name lookup, and trying a named website will result in a 20 second timeout. Now open a command window and enter a command like “nslookup http://www.bbc.co.uk 8.8.8.8″. Note the IP address returned (e.g. 212.58.246.91). Then use Notepad to edit C:windowssystem32driversetchosts with the line “212.58.246.91 http://www.bbc.co.uk”. You can now surf to the BBC website but no other (unless you know its IP address…)

Alternatively, and more effectively, you might be able to put a whitelist on your router/firewall – block access to all IPs then allow access to those VPN IPs and prioritise the second rule. You might be able to apply those rules to just your XP PC (remember to reserve it’s LAN IP address). Also remember to secure your router/firewall.

Ian.

Would that be 8 inch, 5 1/4 inch or 3 1/2 inch? Cue stories about users folding the disk to fit in the smaller slot. A few years ago firmware updates on a Windows Server required a floppy disk. And the disk controller had a connector for the one I salvaged from a very old PC abandoned in the loft.

Another thread on this forum, re mysterious changes to link paths, would extend the tip to “do not open and save directly from a network drive or web folder”!

Ian.

“as a simple internet search will reveal” – actually quite a complicated internet search but I did find it. And I assume you’ve read the causes and tried the solutions. The page Microsoft support direct you to at least tries to explain the process, but doesn’t really offer a solution.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;328440

Microsoft are not about to fix this anytime soon. One possible solution would be to use three workbooks. “source.xlsx” has the source data, and is in a remote location (i.e. needs a drive letter or unc path), “farlinks.xlsx” just has links to source.xlsx and is in the same folder as your main file. Links in your main file go to farlinks and only specify that file name, no path. Ideally your main file and farlinks are on your local drive. Because your main file and farlinks are always available on the same path Excel should not break the links in the main file when it is saved. Because farlinks never needs to be saved those links should not break either.

Ian.

=IF($A5*(1+HighPercent/100)=LowSalary,LowPercent,(LowSalary-$A5)/$A5*100))

Formula in Col B alongside the old salaries in Col A, HighPercent is 15, LowPercent is 10, LowSalary is 80

Ian.

Partial answer. The problem is maybe that your values are text but you want a numeric comparison “11.37” is alphabetically less than “7.60” but numerically greater. I tried the following:

Enter the data as numbers and apply a number format ‘0.00 “Times”‘ (double quotes are part of the format) so the display is similar to your example. Then apply your conditional format which should work as expected. (11.37 is not less than 7.60 and is not highlighted)

Partial answer because you might not be able to separate the numbers from the text, and I don’t know why some are “times” and some are “days” and if it matters

Ian

As with all things Microsoft, the later the version the deeper the useful controls!

File->Options->Trust Center->Trust Center Settings->Macro Settings

You might like to instead add the location of the database to Trusted Locations, which allows you to leave the macro setting at a safe level for all other locations.

A couple of points on longevity. It is my firm belief that writing with good ink on a good substrate, and making many copies, is a reliable way to preserve information. It has been proven to work for up to 1500 years. Pressing marks into clay tablets may work for 5,000 years in a hot, dry climate. There are some residual problems with language, but scholars have proven themselves very good at solving such problems.

Once you move to digital recording, the longevity of the media is the least worry. How many people have a device that will read 1/2 inch computer tape? Punched tape? Cards? 12 inch optical disks? 5 1/4 floppies? If you want to keep access to your digital data keep it on a technology that your next computer will support and copy to a new technology when that appears. Keep several copies. And then there is formatting. Stick to the lowest tech form. Archive databases as CSV text files. Somewhere in the future be prepared to convert all your photos from jpeg to kpeg or whatever comes next, avoid manufacturer formats which could disappear tomorrow.

So think about whether your backups are for day-to-day convenience or true archiving. And plan accordingly. I have my grandfather’s photos on paper, but not many of my father’s on colour slide film, and very few of my own from the beginning of the digital age which I failed to copy from zip disks while I still had a computer that would read them. But I’m not crying over my lost photos the way I would over last month’s lost business data.

And as for backing up open files, most decent backup software will handle open files, but to back up a live database you should use a specialist tool, usually provided with the database, that backs up a consistent snapshot plus transaction logs. Even then, the database design must anticipate backup and wrap changes inside transactions.

Ian.

Indexes.

Don’t go wild, as maintaining indexes on big tables can be slow, but well chosen indexes can make all the difference.

Obviously the primary key field(s) in your tables are automatically indexed by Access.

The foreign key fields (link or join fields) will be indexed by Access if you created relationships for them. If you didn’t create relationships then you should define a non-unique (duplicates OK) index on the fields you are linking (I’m assuming these mostly or entirely link to the primary key of another table, in which case you obviously don’t need to re-create that index). E.g if orders.customer links to customers.id put an index on orders.customer if Access hasn’t done it for you.

Finally, if you are regularly selecting records from a big table using a value in a field that is not otherwise indexed, or sorting on a field, you should consider adding a non-unique index on that field, but only if it make a worthwhile difference to a commonly used query.

Indexes hurt performance every time you add records to the table, but benefit you whenever you look up records using that index – it’s a straight trade-off.

Ian

The trouble with constantly backing up all data rather than using an incremental scheme is that you are regularly backing up maybe 100 or 1000 times as much data, because the vast majority of your files don’t change between backups. A good full then incremental backup system gives you a single save set with multiple versions of changed files, much easier to restore than trawling through many full backups.

I like the earlier post that suggested fast copy to an external drive then online backup of the external drive. I’ve investigated something similar but have yet to do a full trial. I suspect the snag will be persistence of deleted files – sometimes you really want deleted files to go away and with this scheme they might never do so.

Ian.