Success!

At last a working VB 2010 data application linked to a SQL Server 2008 instance. In the end I used two methods.

Method 1: Extreme coding. I used sample data provided by aptly named PsychoCoder in http://www.dreamincode.net/forums/topic/32392-sql-basics-in-vbnet/. He obviously doesn't test his posted code, there were a number of errors in it but not too hard to sort out. I couldn't get his code to work with a connection string embedded in app.config but with a hard-coded string it worked as far as it went and showed me a filled DataGridView. That's about where it ended - I couldn't work out from there how to actually do anything useful like update back to the database (he provides code but doesn't suggest where to hang it in a datagrid). However, that clearly showed that with the appropriate connectiions the two systems could talk.

Method 2: Permissions kludge. In the VB data access wizard there is an option to connect to a SQL Server mdf file, but if you try to do that and point it at your SQL database it can't access the file. So I did a few tweaks. I used the advanced settings to select the appropriate SQL Server instance (the 32 bit one as that was where I'd managed to populate some data) and I set it to not use a user instance (that was suggested by a few forum posts). I then changed the permissions on the actual MDF file (it was in C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS32\MSSQL\DATA\FamilyHistory_Data.mdf, your mileage may vary) so the group Users had the same permissions as the SQL users group. Administrators already have those permissions but for some reason UAC doesn't pop up to ask if you want to use them so you have to access as Users. Bingo! the wizard completed and I can use the full might of Visual studio and Visual Basic to manipulate my data. It would be easier if the connection used SQL Server to access the data instead of piling in as if it was a private file, but at least it works. I suspect if you buy the non-Express versions there's an option to go through SQL Server.

Now to write the application. My Access database has developed over many years, so I don't know how much functionality I can get in before something else arrived to take up my time, but here goes.

First, I probably need to rewrite the database to use text strings for dates and a class to provide date checking and arithmetic based on those strings.

More VB and SQLWoes

I promised I'd keep readers up to date with my Visual Basic project issues, here's another one:

Problem #4: MS SQL Server has a base date of 1752!

Why is this a problem you ask? Because I've decided my VB project will be my Access family history database. I can trace my family back to the 17th century, long before 1752.
Why 1752? That is when the UK and US switched from the Julian to the Gregorian calendar, and also moved the legal year end ffrom 31st March to 31sr December. Dates before that can be problematical and SQL Server's progenitors evidently elected to simply not allow earlier dates.

This isn't quite a restrictive as MS Excel which won't handle dates before 1900, but Access happily goes back before 1752.

It wouldn't be hard to permit any date - what do historians and archaeologists do?

Visual Basic Woes

I've decided to have a real go at mastering Visual Basic 2010. After years of playing with VBA I'd like to be able to write real standalone apps without needing Access as a rather expensive front end. I'm going to post here any issues I've found with getting a "proper" app written.

Starting from scratch with a new laptop  (Windows 7 Home Premium) I downloaded (free) all this:

Visual Basic .net 2010 Express
SQL Server 2008 R2 Express x64
Office 2007 Home & Student (I had a spare license)
Adventureworks sample database
VB sample code

I needed to import data from an Access app on another laptop, so decided to use an Excel backup of the tables. Problem #1. The app is a genealogy database and has dates going back to the C17th. Excel won't accept a date before 1901. Had to write queries to export dates as text strings.

Then, Problem #2 (you won't believe it) there's no Excel 2007 connector for SQL Server 2008 x64.
Official MS advice was to install the x86 (32 bit) version and the downloadable Access connector, then reference the data across to the x64 database. So I did that. and then

Problem #3, I can't see any way to establish a data connection to a SQL server database from Visual Studiio. I can connect to a database file (.sdf) but not to a database in a server instance, which is what I am used to working with in SQL Server. So I'll investigate a bit more, and then probably copy the data over to a .sdf file.

F1 - Are the New Teams Welcome?

This is the subject of the Sidepodcast Thursday Thought so I'll post something on it here.

Surely no-one would not welcome new blood in F1. Without the three new teams we'd have less action to watch and some great drivers would be without a seat. No Karun Chandhok!

Arguably HRT as a team made little contribution, as much as anything because they didn't "join in". Get a third party to build you a car then not develop it during the season - not a great plan. And pulling in pay drivers to cover the bills isn't going to make you popular with the fans. I do wonder if they'll make the grid in Bahrain.

Virgin make a good showing. There's a good tech story (all CFD design) and worthwhile drivers. Its a shame they ended up last, because they were way better than HRT when they managed to finish. What WAS DiGrassi thinking of with an off on the way to the grid in Japan? Next year, with Marussia money and a year's experience of developing a car they may be challenging for the odd point.

Lotus really deserved to break out of the bottom 3. Mike Gascoyne is a good designer and they hired experienced drivers. I hope they make a real job of it next year, they seem to be at least as committed a team as the STRs of this world.

Overall, without the new teams we'd have had a much less interesting year and the future, with fewer opportunities for new drivers to get into the sport, would be looking shaky. Next year I'd like to see more interest in Q1 - this year it was all about who would get 18th place on the grid.

Back to web design

Years ago I used to design websites. Using HTML and FTP. It was pretty easy, just write some text, create some graphics, chop them up, add a few tags and there you go. At one point I picked up a copy of a popular web design tool but didn't find it did anything I couldn't do with a bit of my own HTML, and my HTML was easier to understand and debug. Anything complicated I'd check the source of a similar site and see how their designer did it.

Later, CSS came along and I learned how to do the basics, which simplified the HTML considerably.

The problem was, if I needed to change the content it could be pretty convoluted finding the right place to make the change, so if I'd done the site for a client I had to make the changes, which my generally impecunious clients didn't like paying for.

Now, it's easy to install a site builder system on the site, and once everything is designed and set up the client can use the builder to play with content to their heart's content. Now, however, the problem is bigger. I have to learn to use Joomla, and to use whatever template I choose for the overall site design. It's not easy, especially as I don't know where Joomla ends and the template begins. Or should I be using Wordpress? Or Moonfruit? (not Moonfruit, it's Flash dependent and Flash crashes are my biggest web surfing issue)

Looks like I've got a lot of learning to do!

Student fees

I always thought government had just a few significant roles. To defend the citizens from external attack, maintain public order, provide a few things that can't economically be provided privately like a national road network, to enable a healthy, well educated population.
 
Seem to have lost one. Arts and Humanities, the cornerstones of an educated population, are now to be pretty much entirely funded by the students themselves. Surely industry and commerce can fund work-related technical courses?

Glad my children are almost finished with University.

What everyone wants to know

How to make loads of money in Runescape - no scam!

The answer is CANNONBALLS. They sell in the grand exchange for 380gp and the raw materials (iron ore and coal) cost about 186gp per ball (one iron makes 4 balls). If you work in Port Phasmatys where the forge is one click from the bank you can make around 60gp per second, and you get smithing XP as well. If you can stand the boredom you can make around 1 million gp in an evening. You have to buy the materials, mining that much coal would take a week!

If you start without much money you'll need to teleport back to the Grand Exchange several times to cash in and get more material. An Ectophial is needed to teleport back to PP.

For a variation you can smelt the ores using Superheat Item and level up magic at the same time. (Economics are trashed unless you can make your own Nature Runes.) Superheat seems faster than a forge and you can stand in the bank and do it.

The surprising bit is that Port Phasmatys forge is pretty much unused, so not many players do this.

Designer available

I find myself short of work right now - well, there IS a recession! If anyone reading this needs some IT problems solved, especially small database systems designing and building, or advanced MS Office macros and things, post a reply to this! I'm very good at meeting your every need, even ones you didn't know about, for reasonable fees.

Read a Word table in VBA - or not!

To read data from a WORD table using a macro you might think you could use something like

Tables(1).Rows(1).Cells(1)

This is great because each collection (Rows and Cells) has a count property. Unfortunately you'd be wrong - sometimes. If you've merged adjacent cells vertically anywhere in the table you can no longer use the rows collection for that table. So you might try to loop over all rows and columns using

Tables(1).Cell(1,1)

but when you reach the second of the merged cells you get error 5941 "the requested element does not exist". The merged cell can only be addressed by its top left co-ordinate. Also, there's no way to count the rows and columns in the table (because you can't access the rows collection).

You can read successive cells by selecting the top left cell  then using

Selection.MoveRight wdCell, 1

but I can't find a way to limit the operation to the table - if you select a cell beyond the last cell the table grows by one row. There doesn't seem to be a simple count for the number of cells. Also, the merged cell may appear multiple times in this enumeration, once for each row of cells that was merged.

So, how to enumerate the cells of a Word table? The only way I can find is to use the cell(row,column) method with wildly excessive row and column limits, then handle the errors. Note that even this isn't neat, the error for running off the edge of the table is the same as that for hitting a merged cell.

Sub numbercells()
    On Error Resume Next
    For iRow = 1 To 100
        For iCol = 1 To 100
            ActiveDocument.Tables(1).Cell(iRow, iCol).Range.Text = _
                Format(iRow, "0") & ", " & Format(iCol, "0")
        Next iCol
    Next iRow
End Sub

If you know better, let me know.

Excel VBA macros fail with merged cells

If you use a VBA macro to read or set the contents of a merged cell in Excel it appears to fail. The code I used for a non-merged cell was

    appExcel.ActiveSheet.Range("A35").select
    sContent = appExcel.Selection.Text

Reading the value for a merged cell returns a null pointer for each of the cells that are merged, where you might expect the top left cell at least to return the value Excel shows if you click in the merged cell. The code below reads the value correctly:

    Set objRange = appExcel.ActiveSheet.Range("A35").MergeArea
    sContent = objRange.Cells(1, 1)

Thanks to Antonio Bayah for the initial tip that led me to this.

Your Vista computer starts from hibernation overnight

So. You work on your laptop all evening, then hibernate it. When you come down stairs in the morning it has turned itself on and is sitting waiting for you to log in. If you're lucky it is on mains power, if not it is about to run out of battery. I've also heard of people finding their laptop waking up in its case and getting rather hot.

The problem is Windows Automatic Update. If you accept the default setting of install updates automatically at 3am it wakes up at 3am to do just that. Microsoft claim it will only do this on mains power and it will hibernate again afterwards. Experience suggests it may do it on battery power and may not turn off.

I suspect the problem is restarting after update. For a while MS managed to make most updates install without a restart but now most require one (more about this here). I assume that when the laptop powers up after the restart it has forgotten it is supposed to go back to sleep. Anyway, change the automatic update settings to download updates automatically and notify you but don't install. The added benefit is that you can avoid updates you don't want, like new versions of Internet Explorer and nagware like Windows Genuine Advantage. I also don't generally install updates till Windows Secrets has passed them as not likely to break your software.

How to fix a laptop computer - step one

Picked this up from a Toshiba tech support call centre. The rep was really apologetic that he didn't know what was wrong, so went to basic procedures. The first one fixed the problem. and the same technique has since fixed many laptop problems. Its a simple cold boot, but cold booting a laptop isn't quite as easy as a desktop.

Start by shutting down. Then remove all sources of power, i.e. unplug the charger and remove the battery. Then hold the power button down for a while. I'd give it 20-30 seconds. Then replace the battery and charger and restart.

The last step with the power button discharges the power supply capacitors which otherwise keep a small amount of power on the motherboard.

That is now step one of my problem solving procedure if there is any suggestion of a hardware issue. The "cold" shutdown restores all the circuitry to default so it powers up the way it is supposed to instead of the way it was left.

Another useful basic check for laptops and desktops, especially if the system won't start, is to start up with no USB or other devices attached, just the monitor. If it works, add in the other things (keyboard, mouse, printer, card reader, external USB hub) one by one, restarting each time, until either it all works or you work out what is causing the problem.