Starting a Startup: Burglar Alarm

Documenting the development of the prototype of RopeWeaver

Someone’s burglar alarm went off at 11.00 p.m. last night, and was still going this morning when I got up. Fortunately I have a large supply of earplugs.

Think I’ve had an unproductive day, though it’s difficult to tell seeing as I’m only 2 days in, not really got a frame of reference yet. Whole day spent coding functionality to import comma-delimited text files of data into a collection. Had planned to get so much more done today, but I think that was a fault of my hopes rather than my abilities. Well maybe a little to do with my abilities.

Lunch is vegetable soup with 2 slices of brown bread. Gaddafi clinging to power in Libya. Assange to be extradited to Sweden pending appeal. During lunch break I rip some choice CDs, attempting to construct my perfect programming playlist. More jazz needed.

Must get some database action going tomorrow, plus need core data objects defined in application and database. Don’t feel confident.

Starting a Startup: Project Kilo Lima Day 1

Documenting the development of the prototype of RopeWeaver

Development of the Project Kilo Lima operations management system begins today, and I’m not in the least bit terrified [lying].

First job: need to determine robust database design to store bills of material, it underpins everything. Start at 8.00 a.m., by 9.45 a.m. I have a structure that I’m happy with. It was stressful until then; convinced myself I was going to fail, that I was already behind schedule, that I should have some UI working by now. The start is always hard; I spend the rest of the day flipping back and forth between database table design, object model coding, login screen, and getting the CSS to make lists appear with square bullet points.

Lunch is beans and sausages on 2 slices of brown toast.

At 3.00 p.m. I listen to a live webinar by Dr Ted Hutchin from the TOC-Lean Institute while I work. My cork board is delivered. Strung it using one of the millions of 3mm audio cables I own, and hung it on the wall above my desk. The first pinned notes are the Gantt chart of the work to be done over the next 12 months, and company revenue estimates until 2013.

Exploring the Structure of a Sage 50 Accounts Database

So you’ve found your way into the database behind Sage 50 Accounts, it’s time to have a poke around

As promised in my previous post, Connecting to a Sage 50 Accounts Database from VBA, I described how to go about setting up a connection using VBA and ODBC to extract data from a Sage 50 Accounts database.

Unless you are a registered Sage developer the inner workings of their databases are kept shrouded in mystery (unless you’re using Sage 200 Accounts that is, where you can just quite simply open up the database in SQL Server Management Studio and mess about with it to your heart’s content). Sage 50 Accounts uses a proprietary database format, passed down through the ages largely unchanged, not widely suited to today’s multi-user network environments.

Even without a Sage Developer subscription it is possible to discover and explore the secrets within the Sage 50 Accounts database; it just takes a little bit of ingenuity, and a copy of Microsoft Access. I warn you now that I will not be going into a detailed description of the purpose of each table, the most commonly used ones are self-explanatory anyway (e.g. SALES_ORDER, STOCK, PURCHASE_ORDER).

Create an ODBC connection to your Sage 50 Accounts database

We won’t go through this again, as I explained it in my previous post.

Open the ODBC connection in Microsoft Access

Microsoft Access Import Objects window

  1. Open Microsoft Access, create a blank database and save it somewhere.
  2. Due to the changes made to the Microsoft Office suite over the previous years (I still prefer 2003) the option you are looking for may be in a different place, but essentially you should locate and select “Import External Data” (that’ll be “Get External Data -> Import…” from the File menu in 2003).
  3. Select “ODBC” as the type of connection you wish to import from.
  4. An ODBC data source selection window will appear. Select the connection to your Sage 50 Accounts database.

Import the Sage 50 Accounts database tables

  1. After entering your credentials you’ll be greeted with a new window listing all the tables available for import from Sage 50 Accounts into Microsoft Access. Highlight the tables you wish to examine and click OK.
  2. It may take several minutes to import the data, depending on the size and number of tables you have selected to import.
  3. Once completed you will see new tables within your Microsoft Access database, copies of the data from your Sage 50 Accounts.

You can open and view the records inside these tables, and explore the design of these tables exactly as you would in any other Microsoft Access database. With some deductive reasoning you should be able to determine what fields you need to provide to your SQL queries to extract data directly from the Sage 50 Accounts database, or you could create a sporadic dump of the data into Microsoft Access (as we have done here) and write queries to select from this non-live data.