PDA

View Full Version : Collaboration - Table Driven DB



Bob Phillips
10-24-2008, 09:38 AM
Anyone interested in a collaborative project, just for the fun of it.


Background

I am often creating databases, mainly SQL Server but some Access, and it is a real pain. Even with decent UIs, it is a slog. So historicaly I have created a procedure, or set of procedures, using SQL to create the database, add tables, add columns, etc. This has worked okay, and eases rebuilding and so on, but I still find myself repeating the same old code, even re-using means more work than it should.

Yesterday, I attended an MS presentation on VS2008 Service Pack 1, and its effects (don't I lead the exciting life). One of the presenters said something that really struck a chord with me, namely that whilst we are more and more imbued with objects, think objects, create objects, we still interact with databases via text strings, the old SELECT * FROM table WHERE column = value. He was clearly taling about consuming the database, and we are still not there with object databases yet, but I got to thinking about my situation.

Potential Solution

Even though I like to think of myself as object aware, I still use procedural code to create my databases, and I still create too much code. So I thought, let's use ADO to create these database(s) and their attributes. And let's automate it fully

For years, I have been creating commandbars using a table driven approach, so why not also do this for my database(s) and their attributes?

So that is my proposal, build an application where the databse and all of its attributes are added to a table, press a button and the database is built for us.

I have an outline, and built some starter code, so we have a startup discussion point ready.

Further On

Stage one would be to get the design principles agreed and implemented, and applying them to a single database type. I am suggesting Access to start with, it is the most familiar.

Stage two would be to convert the code to an addin.

Stage three would be to add SQL-Server.

Later stages might be to switch to a COM addin, and add more database types.

Rules of The Game

Whilst we are collaborating, I would hold the master code, but it would be completely open, so anyone can view it.

Later, we might even go to open-source, where others can amend it, but let's see how it goes down.

Once we have a working version, I will host it on my website as free download, others are free to do so also.

Target

I am looking for good developers, I have some names in mind, so if none of these come forward, it will die at birth.

I don't see there is a pressing need for this, so not looking for huge commitments from anyone, rather different perspectives.

Anyone who makes a significant contribution will get a credit in the code.

Apologies if this sounds snobbish or elitist, but I am not asking for collaboration because I can't do it on my own, but rather because I am looking for other ideas and input from experienced members of VBAX. As such, if you are not an experienced VBA user, please don't offer your services. I am not seeng this as a teaching/mentoring project, there is another initiative in this area, although please feel free to drop in on the discussion, download what we have to date, and post questions that one of the active participants may take time to answer.

Tommy
10-24-2008, 10:39 AM
FWIW I'm Game.

mdmackillop
10-25-2008, 09:13 AM
Me too.

CreganTur
10-27-2008, 05:37 AM
I'm interested as well.

I've got a small library of ADO procedures for creating databases, tables, columns, indexes, etc. that I'll make available to the dev crew at large when we start (if anyone needs them).

Bob Phillips
10-29-2008, 03:10 PM
Well, the responses have been dismal, but we'll plough on regardless.

I would like to start by setting up a LinkedIn discussion group to have a private discussion about how we run it, and then we go public. Can all who have responded, and anyone that might also like to join in, send me an PM with an email address that I can use to invite you in the LinkedIn?

SydneyGeek
11-09-2008, 08:59 PM
Well, the responses have been dismal, but we'll plough on regardless.

I would like to start by setting up a LinkedIn discussion group to have a private discussion about how we run it, and then we go public. Can all who have responded, and anyone that might also like to join in, send me an PM with an email address that I can use to invite you in the LinkedIn?

I'm interested in having some input. Haven't spent much time on this board but I make a living with both Excel and Access. Would have posted a link to an ADO tutorial on my site but I need a couple more posts to get permission...

Denis

Tommy
11-15-2008, 06:21 AM
This is the form I wanted ya'll to look at.

Bob Phillips
11-15-2008, 11:00 AM
And this is my HLD

SydneyGeek
11-15-2008, 10:42 PM
Hi Tommy, Looks like a good idea. I'd suggest another field -- Table Name.
That way the user can define more than one table at a time. It could be set up so that a new field inherits the table name of the previous field by default.

Denis

SydneyGeek
11-16-2008, 01:54 AM
Here's the 2-table layout that I alluded to in one of the discussion threads.
Tommy, the form you built could be used to populate a table like this -- maybe that's the way to go? The second table is where the relationships are defined.
The other 2 sheets have useful lookups and some setup info that can be used to drive the table creation, depending on the database engine that we use.

Denis

Bob Phillips
11-16-2008, 02:57 PM
.