UPDATE Query from Excel to (My)SQL Database

Recently I found out about a problem going between versions of MySQL last week and I though you might enjoy the solution. You see FLOAT()'s functionality changed whereby FLOAT(4,2) once meant 0000.00 to now meaning 00.00. The problem with this is that database imported with a value of 2341.33 becomes 99.99! This could be very bad. Fortunately you can export from the old DB via Query Browser to Excel (XML) and employ VBA and ODBC to get things right, fast.

Notably this does not update all values, only values where there is data, if you were to use this routine to post to a database repeatedly I would suggest adjusting the 'If ActiveCell.Value <> "" Then' functionality to set Null values as needed.

Sub UpdateDB()
'
' 09/10/2006 by Cameron Stevens
'

Set myconn = CreateObject("adodb.connection")
Connection = "Driver={MySQL ODBC 3.51 Driver}; Server=MyServer; ~SPLIT LINE~
          uid=webuser; pwd=b33rg00gl3s; database=brewery; option=3; port=3306;"
myconn.Open (Connection)
Set result = CreateObject("adodb.recordset")
Sql = "USE brewery;"
Set result = myconn.Execute(Sql)

Range("A1").Select
Selection.Offset(1, 0).Select
strUpdate = "UPDATE tblBottlePrices SET "
While ActiveCell.Value <> ""
strWHERE = " WHERE order_id='" & ActiveCell.Value & "' "
Selection.Offset(0, 4).Select
strSET = " "
If ActiveCell.Value <> "" Then strSET = strSET & "retail_price='" & ActiveCell.Value & "' "
Selection.Offset(0, 1).Select
If ActiveCell.Value <> "" Then strSET = strSET & ", discount_price='" & ActiveCell.Value & "' "
'Selection.Offset(0, 1).Select

strUQuery = strUpdate & strSET & strWHERE

Set result = myconn.Execute(strUQuery)

'MsgBox strUQuery
Selection.End(xlToLeft).Select
Selection.Offset(1, 0).Select
Wend
myconn.Close
Set result = Nothing
Set myconn = Nothing

End Sub

Accessing or Deleting an Inaccessible File/Folder

While the rights may be adjusted using CACLS or the like, there are occationally files hat prove inpenitrable. It seems that the pleasantries of Long File Names can occationally curse us if that LFN gets corrupted. You can do what you want to delete the folder but it won't budge. But... there's a trick... go to a command prompt and perform a DIR /X on the folder containing this file or folder. The /X switch shows you the 8.3 or short names for these files, rename the problematic file using the short name and all is well (i.e. REN ALLDEA~2 XXX).

While this isn't the only way into a file that's inaccessible, it may be the last resort with a file that's got a corrupt filename.

3D Text Screen Saver

At one time the 3D text Screen Saver had some cool tricks that depended upon your choice of words. If you typed in 'Beer', 'Volcano', or 'rock' you had a selection of things come up in the screen saver that meant something to the developers. Windows 2000 threw this Easter Egg out, but left a treasure behind. If you're using Windows2000 you have the opportunity to inflict your personality on this screen saver. To get your own selection of words appearing you need to do the following:

1. Open Regedit and search for the string HKEY_CURRENT_USER\Control Panel\Screen Saver.3DText
2. Create a new String Value called Magic
3. Enter a value for it which will be the name of the file you want to use (e.g. c:\winnt\magic.txt)
4. Create the file (c:\winnt\magic.txt) using notepad. The format is as follows:
   -Test
   blah
   deedum
   hello
   n00b
   
   

Important - The first word must be preceded by a hyphen as this is used to designate a group of words. It is this word that you must enter into the 3D Text screensaver 'textbox'. When you run the screensaver you will get a random showing af all the words under the hyphened word in the text file. You can add more sections to the text file (again give each section a hyphened keyword to use in the screen saver dialogue box).

Safe Data: Backup and Recovery

It is critical to ensure data integrity in todays business world. Loss of data can set you back days or weeks and may put you in hot water with more than your boss or client. There are a few methods and means by which to safeguard your data and help you sleep at night. After deciding what sort of media you will use a backup schedule is the key to a good night's sleep.

Please have a look at this White Paper from Sun Microsystems and call me if you have any questions or concerns.

Finding a PC on the network

If you have several hundred PCs in a domain and need to quickly find just one there's a quick trick to finding them. This is especially true if your org uses a odd naming conventions for the ComputerName. At a command prompt type 'net view | find /i "userid"'. The userid is an example. If you want to explore this one further look up the details of the command FIND. You can also redirect the output and open it in NotePad for simplicity. and copy & paste functionality.

Internet Explorer opens Zip file as web page or undisplayed graphic.

It seems that a Zip Plug-In or MIME-type handler of zip files may have stopped functioning. The best way to reset this is to uninstall or install, then uninstall such a tool.

The symptoms are such that unless you right-click on a link and Save As, clicking on a zip file just opens another, somewhat blank, web page.

You may install the PKWare plug-in and, if don't really want to use it, remove it afterwards. If you do want it, please support the author by purchasing the full license.

Web Development Confinement to Screen Resolution Restrictions

When you build a web site your goal is to make it easy to read for your target audience. The layout is important and the content's readability from a font and presentation perspective and how the text reads. The target audience may have a specific preference as to browser, screen resolution, or age/reading level. Just as a newspaper must cater to it's target audience so must you in developing your web site.

One tool I have found useful is a wallpaper template. I normally run at a high resolution (1600x1200) and remembering how things look to the common user (1024x768 these days, though it was once 800x600) is sometimes tough. This template helps keep your perspective. Simply re-size your browser to fit within the size you're targetting. If you're running Windows XP or (gasp) Windows 98 you could configure a second display to the appropriate resolution, but that's a different challenge. Windows 2000 doesn't actually have this capability, but it's my OS of choice.

Note: you may want to leave a little space at the bottom of the area for the task bar.