Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
BOOK: Visual Basic 2008 Programmer's Reference ISBN: 978-0-470-18262-8
This is the forum to discuss the Wrox book Visual Basic 2008 Programmer's Reference by Rod Stephens; ISBN: 9780470182628
Welcome to the p2p.wrox.com Forums.

You are currently viewing the BOOK: Visual Basic 2008 Programmer's Reference ISBN: 978-0-470-18262-8 section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old March 9th, 2010, 03:18 PM
Registered User
Join Date: Jan 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts
Default Creating DB Tables Programmatically

I'm working on a project that records data continuously streaming in through a serial port, and is parsed into a custom data format, and then, after being used in various ways, is suppose to be put into a database table for storage. I've worked out how to capture, parse and use the data, but I'm just not clear on storing it.

Since each database table will only hold a single days data, its name will be composed of the current date string at runtime (e.g., "20100309abc"). Since the program may be started and stopped many times per day, one of the things that have to happen when the main form loads is that the database needs to be searched to determine whether the current days table has been created yet or not. If it has already been created then that table gets added to, otherwise a new table needs to be created and set up with all the appropriate fields before being written to.

I've seen lots of tutorials that show 36 ways to do this at design time, but none that show how to do it at run time. I broke down and bought Rod Stephen's "Visual Basic 2008 - Programmer's Reference" to help on me with the database questions I have, but I'm still confused as to whether I should be using TableAdapters, DataSets, connection strings, or what. Is there anyone out there that thinks they could give me some guidance. (I will provide more information upon request)

I appreciate any assistance I can get,

Old March 9th, 2010, 06:52 PM
Friend of Wrox
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts

Well, the first comment/question to you needs to be: WHY???

It's almost always a really bad idea to have more than one table in your database with the same design. If the only thing distinguishing your tables is the date that the data is entered into them, then you should use just one table and you should just add a single field (a DATETIME field, of course) to hold the date that the data pertains to.

If somebody has imposed this restriction upon you, you should question their database design skills. Even if there is a need for the data to be (say) exported on a daily basis, you would surely still be better off keeping it all in a single table and using queries to do the exporting as needed.

Having said all that...

If you still have to do this for some horrible reason, it's not hard. You can certainly simply execute a SQL statement to create the table:
    CREATE TABLE name (
        fieldname fieldtype,
Just put the entire CREATE into a string and use ExecuteNonQuery and presto, you have a table.

A more practical way might be to have one prototype table that you "clone". Unfortunately, SQL Server doesn't have a "create table xxx like yyy" as some DBs do. But you can simply do
    SELECT *
    INTO newtablename
    FROM existingtablename
Provide a single dummy record in the existing table and voila! You have NEAR-clone in the new table. ("Near" because IDENTITY columns will become just INT, and you won't copy any foreign key info, and and and... But all those are easily fixed by follow-up ALTER TABLE statements.)

You *can* ask SQL Server if a table already exists, but if you are really bent on doing this ugly thing, I think I would create a separate table, just to keep track of which tables already exist.

But please please please consider NOT creating separate tables. It's just not a good solution, honest.
Old March 10th, 2010, 12:14 PM
Registered User
Join Date: Jan 2010
Posts: 2
Thanks: 0
Thanked 0 Times in 0 Posts

Thanks for getting back to me so quickly on this matter. I understand that the design I've chosen is not optimum, but given the trade-offs I think I'm looking at, it was the only solution that I could come up with.

The problem is that my project will be collecting 1800 records per hour and will be running off and on for up to 6 hour per day. The records are essentially GPS location fixes with 3 other data bits plus the date and time. As the points are coming in I will be, among other tasks, plotting their geographic positions on a map in the UI that is color-coded to represent one of the other data bits, and I will be having to redraw the map with every new record that comes in. It is my understanding that working with tables that have over 5,000 records or so greatly encumbers a database's ability to process records. I thought that creating a fresh table at the start of each day's run, and therefore working with smaller tables, might be a way to keep the record-reading fast enough to keep up with the influx of data.

What are your thoughts on the reading efficiency vs. size issue?


Similar Threads
Thread Thread Starter Forum Replies Last Post
which privilege for creating a db and tables? hertendreef SQL Server 2005 5 July 11th, 2007 04:04 PM
creating code programmatically mumphis Excel VBA 0 September 2nd, 2005 09:29 AM
Report - programmatically creating penta Access 12 May 19th, 2005 04:53 PM
Creating a table programmatically Bob Burns ASP.NET 1.0 and 1.1 Basics 2 July 15th, 2004 11:44 AM
Creating Tables Programmatically stu9820 ASP.NET 1.0 and 1.1 Basics 10 October 22nd, 2003 04:25 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.