Thursday, March 11, 2010

it's nice when things work...

...without having to read the documentation, that is.

I've never done anything significant with SQL Server, but there's a chance that an upcoming project will be an Access front-end to an SQL Server database, so I figured I ought to get the basic development stuff set up now.

I downloaded and installed SQL Server Express and its management tool. It was pretty easy to create a new database, add a table, define some fields, and insert some data. Pretty slick.

The next challenge was to build an Access database that can see (and modify) the SQL Server table, and the obvious way to do that is via a linked table, using ODBC. The one critical step to do this is to correctly define the ODBC data source and confirm that the connection is valid.

However, my efforts to define the ODBC data source failed again and again, as my frustration and dread grew exponentially. I tried every combination of the parms that I thought it wanted, but I got a 'connection failed' message every time.

It was totally frustrating, and that was the state I was in last night, when I headed for bed.

Naturally, at some point during the night, a thought 'occurred to me' (as they say), and, in short order, this morning, I created and successfully tested the ODBC data source definition (hint: the default server-name that Windows inserted was incorrect), I created a proof-of-concept Access database with the SQL Server table successfully linked in, the SQL Server table was copied into an Access table, and I created a form that can display (and modify) EITHER the Access table OR the SQL Server table, based on a parm that I can easily toggle in an Application Constants table.

So, I now know I can build an application with forms pointing to tables on either platform, copy data from one to the other, etc. It may not sound like much, but it opens up a lot of possibilities for development.

Now all I need to do is hope they come thru with the contract!

Your reward for getting thru all the above boring stuff is this.

No comments: