how do i create a new ado database ?

jerryham · October 10th, 2008, 05:23 PM

my old vb-6 programs have lots of databases in them .. some with over 1,000,000 records in them.

in the vb-6 programs, i used (dao) objects to create and work with the databases .. using the jet engine so they could be accessed with access.

now that i am converting to vb-2008 (express), i want to use the (ado) objects.

? what is the simplest code to create a database ?

? what is the simplest code to create a table .. 1 field is enough.

? what is the simplest code to make the field a primary key ?

thanks

jerry gentry

jerry gentry .. i am not retired .. but after 47 years of computer programming .. i got so old that nobody will hire me anymore.

Old Pedant · October 10th, 2008, 06:04 PM

> now that i am converting to vb-2008 (express), i want to use the (ado) objects

No, you most certainly do *NOT* want to do this!!! ADO is also for VB6 and old-fashioned ASP and VBScript, but not for any of the .NET languages.

You want to use ADO.NET. Which is quite different.

Anyway, to answer your questions. Actually, you already know the answer. It's the same one as for most programming questions. "It depends..."

You don't say what kind of database you are talking about. Access?? SQL Server?? MySQL??

I'm *guessing* you are still talking Access. Using the JET OLEDB driver. No?

If so:

? what is the simplest code to create a database ?

I wouldn't use code. I'd use Access, itself. If you don't have a copy of Access, then you can use CONTROL PANEL==>>ADMINISTRATIVE TOOLS==>>ODBC. You can create a UserDSN or SystemDSN, specify ".mdb", and then when it comes time to configure the ODBC DSN, you ask to create a DB. Do so, and then forget all about the DSN.

Once you have a blank ".mdb" file, you can simply copy it (and rename it) any place as a starter DB with no tables in it.

Having said all the above: I would *bet* that there is a way to do this from VB Express, but I don't have a copy handy. I just tried from VWD Express 2005 and didn't see a way, though, so maybe I'm wrong on that.

? what is the simplest code to create a table .. 1 field is enough.
? what is the simplest code to make the field a primary key ?

Code:

CREATE TABLE tablename (
    id COUNTER PRIMARY KEY,
    name VARCHAR(255),
    comment VARCHAR(30000)
)

Again, that assumes Access (".mdb") database.

But you know, if you are going to use VB Express, why not use SQL Server Express?? I *believe* that then you *can* create a new DB right from within VB Express.

And if this is for SQL Server (any variety):

Code:

CREATE TABLE tablename (
    id INT IDENTITY PRIMARY KEY,
    name VARCHAR(255),
    comment VARCHAR(30000)
)

****************************

47 years. Wow. And I was feeling smugly superior at 38 years.

Old Pedant · October 10th, 2008, 06:20 PM

By the way, if it wasn't obvious, I meant that you would take the SQL query to create the table and then just execute the query via a SQLCommand (or OleDbCommand) object.

I'm aware that in old-fashioned ADO there was (okay, is) a way to do it using the ADOX (X=extended) objects, but it always seemed soooo much easier to just do it via SQL. Much less code to write, plus you can test it out in Access or Query Analyzer or the MySQL command tools.

jerryham · October 11th, 2008, 11:14 AM

hi old pendant.

my bad for not asking the right question.

i meant (ado.net) not ado .. i have never used ado, just dao.

i did mention the old vb-6 programs create and use (access) jet engine databases .. if possible i would like to keep it that way.

since the programs both use (and create) databases, i asked the first question.

? what is the simplest code to create an (ado.net) database ?

? what is the simplest code to create a table .. 1 field is enough.

? what is the simplest code to make the field a primary key ?

you can see and freely download the program i want to convert first from my website .. www (dot) wa0h (dot) com .. the (0) is the number zero.

regarding sql .. since i distribute my programs to single end users (ham radio operators) .. on cd .. i want to keep the distribution as simple and small as possible .. i don't know if this is an issue or not.

in the old vb-6 dao programs, i used dao objects to let the user create his own databases, but i used sql statements to let him query the databases.

thanks for your time and patients.

jerry gentry

Old Pedant · October 12th, 2008, 12:21 AM

Hmmm...you must have moved since you got your license? I didn't think the Missouri was in the 0 (zero) region. Well, what do I know. Haven't done ham radio since I was 18.

Yes, sticking with an Access (JET) database is probably best idea. In order to use SQL Server, you'd have to have your clients install and start the SQL Server "engine" on their machines.

I'm really afraid I don't know any way with ADO.NET to create a brand new ".mdb" database file. Even MSDN's library is silent on the subject, whereas they do describe how to create a SQL Server database.

So I really think my suggestion of creating a blank ".mdb" database file and distributing that on your CD has merit. You can then easily use file operations to copy (and rename, in the process) that file to wherever it needs to go on your client's hard disk. And then you can use all the SQL commands I noted to create tables, add fields, alter tables, alter fields, etc., etc.

AHHH! Just found an article that confirms what I thought:
http://www.codeguru.com/vb/gen/vb_da...icle.php/c5149

So, interestingly, that *DOES* show you how to do this in .NET code! By using the old COM ADOX objects, as I noted in my prior post.

Here's the root page on MSDN for ADOX:
http://msdn.microsoft.com/en-us/library/ms681520(VS.85).aspx

OH WOW!!!! YES!!!! You *CAN* use ADOX to create a new ".mdb" file! I just used this ASP (VBScript) code, and it worked:

Code:


<%
Set cat = Server.CreateObject("ADOX.Catalog")
cat.Create "Provider='Microsoft.Jet.OLEDB.4.0';Data Source='" & Server.MapPath("./foobar.mdb") & "'"
%>

So with this and the CodeGuru article, you should be able to figure out to use VB.NET (or C#) to create an Access database!!!

***************************

NOW...

This still leaves you the choice of then creating tables via the ADOX methods or using SQL. And I'm not sure that having to bind to a COM object and do the above is really any easier than simply copying a blank ".mdb" file to where it is needed.

So... I guess I'll leave it to you to decide which is easier for you, both regarding creating the blank DB and using SQL vs. ADOX.

I'm so used to using SQL to create and maintain databases and tables (I do it with MySQL on a Linux box, nearly daily) that I'm quite comfortable with the SQL methodology. The ADOX system seems clumsy to me, in comparison. But whatever lights your triodes. I don't know DAO, so I have no idea how closely the ADOX methods mimic the DAO methods. (But when I look again at that article, it seems to assume the prior existence of the ".mdb" database file.)

Here's an MSDN page that shows how simple it is to issue the SQL commands programmatically:
http://msdn.microsoft.com/en-us/library/ye97aks6.aspx

That shows using the SQLCommand class, but that's only for SQL Server. You would use the OleDbCommand class for JET OLEDB connections, which I would use (and as you suggested). There are some methods that differ, but none that you care about for these operations.

*****************

I'll make one final argument for providing a "starter" MDB file. You can created some "starter tables" ahead of time. The kinds of tables you know you will need in every installation. And now when you copy that starter MDB file to a final working location, you don't have to create the starter tables.

jerryham · October 12th, 2008, 10:39 AM

hi old pendant.

i agree .. i will just include skeleton access databases.

since the databases that the users create contains 75 fields, i was
hoping to make the new version of my program better by letting the
user specify which fields to include, and even let him create his
own fields .. this will be a hard feature to impliment if i have to
include empty skeleton databases.

i also agree on the use of sql.

my old vb-6 program uses a lot of sql to allow the user to query
the databases .. whatever sql that is needed is included in the
distribution cd.

many years ago, i also had a linux server running php and mysql.
i let my clients access databases that contained over 22 million
records.

dao is much older and completely different than ado.

if you (like a lot of programmers i know) decide the old way (vb6)
is better .. here is the (dao) code to create an access database

Public DbX as Database
Set DbX = CreateDatabase ("C:\DirX\DbX.mdb", dbLanguageGeneral)

i was just hoping there was an equivalent command in vb.net

regarding ham radio ..
since 1970, when i was licensed, the 0 call area has included
ND, SD, MN, NE, IA, CO, KS, and MO.

what has changed is that now you can keep your call when you move
to a different call area .. and cw is no longer required for a license.

again, thanks for your help.

Old Pedant · October 13th, 2008, 07:33 PM

since the databases that the users create contains 75 fields, i was
hoping to make the new version of my program better by letting the
user specify which fields to include, and even let him create his
own fields .. this will be a hard feature to impliment if i have to
include empty skeleton databases

Sorry, I don't see that.

Creating *TABLE* is easy. And you really don't NEED more than *ONE* database, you know. One ".mdb" database can hold *thousands* of tables.

Okay, okay...there's a 2 gigabyte max per database, true. If that's a problem, you would have to use multiple DBs.

But again, I don't see that as a problem. If you have a blank prototype .mdb file sitting on the CD, you simply copy it to (example) "Custom_1.mdb" and then let the user start creating many custom tables in it. Run out of room? Okay, so copy the prototype to "Custom_2.mdb" and go on. Etc.

You know, you *can* (1) create tables, (2) add fields to tables, (3) drop fields from tables, (4) drop tables, (5) CLONE tables, and much much more, all from with SQL queries.

The current Access help is crappy compared to the help that used to exist in Access97; so much so that I keep a copy of Access97 around just for the HELP files. But even so...

Bring up access.
Click on HELP
Click on TABLE OF CONTENTS
Click on MICROSOFT JET SQL REFERENCE
Click on DATA DEFINITION LANGUAGE
read, especially, the sections on CREATE TABLE and ALTER TABLE, including the examples.

****************

Yeah, you could use CreateDatabase with VB6 to create an ".mdb" database, but how could you use VB6 to create a SQL Server DB? Or a MySQL DB? Or... ADO and ADO.NET are much more flexible; they can work with virtually any database system. But then you lose the "immediacy" of VB6. It's a tradeoff.

****************

I just mis-remembered where Zero was. Thought it was just the northern tier of states, that it didn't get down as far a MO. Well, whaddya want from a 46 year old experience.

gbianchi · October 13th, 2008, 08:05 PM

Just a quick note.. if your .mdb file will grow bigger than 500 MB.. please please use another engine.. Access usually brakes that types of databases...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

Old Pedant · October 13th, 2008, 09:21 PM

Depends on what the MDB is used for. Yes, Access has a lot of trouble with large databases when doing inserts and updates.

But I've had MDB files over a gigabyte that have worked just fine if they were mainly used for reading, only. Especially in a web app, where there is really only one user (that is, the IIS user account).

But, in principle, I agree.