Programmatically creating an Access/Jet database

[johnb41]

I apologize if this is a dumb question; many aspects of databases w/
..net confuse the heck out of me!

I can easily work with MS Access/Jet databases, assuming the database
was first created right in Access. I can connect to it w/ the
connection string, and add, delete, update records. No problem...

But in my application, the user will need to create a new database from
scratch. So I need to create a form where the user types in the
fields, the datatype, etc. and then have code that builds a Jet-type
database which can be saved. I cannot assume that the user has MS
Access to create the database there.

Can anyone direct me to info on this? I searched for a while and came
up with nothing.

It would be great if I could create a dataset and datatable via .net,
and then somehow convert that dataset into a Jet-type database. That
would be so easy! I did something similar, but I converted the Dataset
into an XML file w/ schema.

Thanks for all your help!

John

 

[Cor Ligthert]

John,

You obviously did searched in these dotNet newsgroups, Paul, Peter, Ken and
I have so many times answered this question.

One of mine

http://groups-beta.google.com/group/microsoft.public.dotnet.languages.vb/msg/3300033570443de4?hl=en

I hope this helps,

Cor

 

[johnb41]

Cor,

I swear, I did do a thorough search! At least I thought I did. I must
have use the wrong keywords. I was very surprised then my search came
up empty.

Thanks for your link. It looks like just what I need.

If i decide on upgrading to MSDE or SQL Server Express, do you think
the process would be similar?

Thanks,
John

 

[johnb41]

Cor,

I swear, I did do a thorough search! At least I thought I did. I must
have use the wrong keywords. I was very surprised then my search came
up empty.

Thanks for your link. It looks like just what I need.

In the future, If i decide on upgrading to MSDE or SQL Server Express,
do you think the process would be similar?

Thanks,
John

 

[Cor Ligthert]

John,

Thanks for your link. It looks like just what I need.

In the future, If i decide on upgrading to MSDE or SQL Server Express,
do you think the process would be similar?

Not for the first part, however for the second part where ADONET is used.
MSDE or SQL Server Express can serve more application and therefore you will
not create the server everytime new.

Another difference that in that case you will by both instead of OleDB.OleDB
use SqlClient.SQL (not really needed in this, because it is for one time).

I hope this helps,

Cor

 

[Herfried K. Wagner [MVP]]

I can easily work with MS Access/Jet databases, assuming the database
was first created right in Access. I can connect to it w/ the
connection string, and add, delete, update records. No problem...

But in my application, the user will need to create a new database from
scratch. So I need to create a form where the user types in the
fields, the datatype, etc. and then have code that builds a Jet-type
database which can be saved. I cannot assume that the user has MS
Access to create the database there.

HOW TO: Create a Microsoft Access Database Using ADOX and Visual Basic .NET
<http://support.microsoft.com/?scid=kb;EN-US;317867>

HOW TO: Create an Access Database Using ADOX and Visual C# .NET
<URL:http://support.microsoft.com/?scid=kb;EN-US;317881>

+

<URL:http://search.microsoft.com/search/results.aspx?&View=en-us&qu=ADOX>

 

[johnb41]

I've done some research on the SQL "Create Table" command, and have
came up with this SQL statement which does not work:

Dim cmd As New OleDb.OleDbCommand("CREATE TABLE archive (Id INT NOT
NULL AUTO_INCREMENT, Lastname VARCHAR(50) )", conn)

I get an Syntax error for my SQL statement. The problem seems to be
for Id INT. If I leave out NOT NULL and AUTO_INCREMENT, the SQL works.
I need the Id column to be auto-incrementing. I got this syntax from
a couple different tutorial sites.

Do you have any idea what might be wrong?
Thanks!
John

 

[Cor Ligthert]

John

The first time that I saw SQL (and that is long ago), it was hate on first
sight because its inconsistency.

That never changed. So please don't ask this to me.

Cor

 

[Michael C#]

CREATE TABLE [archive]
([Id] INT NOT NULL IDENTITY(1, 1) PRIMARY KEY CLUSTERED,
[Lastname] VARCHAR(50))

You don't have to make [Id] a Primary Key, but it might help your
performance (I like to put a Primary Key/Clustered Index on all my tables).
To do it without making [Id] a Primary Key, use the following:

CREATE TABLE [archive]
([Id] INT NOT NULL IDENTITY(1, 1),
Lastname VARCHAR(50))

 

[johnb41]

Michael,

Your code works, but I need to make the ID field Auto-incrementing.
When I add AUTO_INCREMENT, I get a syntax error. Suggestions?

Thanks!
John

 

[Michael C#]

SQL Server doesn't have "AUTO_INCREMENT".

IDENTITY(1, 1) performs a similar function. It seeds your "IDENTITY" (i.e.,
AUTO_INCREMENT) column with a 1 and Increments it by 1 each time you insert
a new row. Try the following so you can prove it to yourself (since you're
using SQL Server, I assume you have Query Analyzer, which happens to be the
easiest way to test this.)

INSERT INTO [archive] ([Lastname]) VALUES ('Johnson')
INSERT INTO [archive] ([Lastname]) VALUES ('Williams')
INSERT INTO [archive] ([Lastname]) VALUES ('Jones')

Then, run this:

SELECT * FROM [archive]

 

[johnb41]

Thanks for the info Michael. Actually i'm just using MS Jet. Maybe
it's not supported their either? I wonder what database DOES support
it.

In any event, i'll test IDENTITY and see what happens!

Thanks so much for your help!

John

 

[Michael C#]

For Jet/Access, try this:

CREATE TABLE [archive]
([Id] COUNTER(1, 1) NOT NULL PRIMARY KEY ,
[Lastname] VARCHAR(50))

 

[johnb41]

Michael,

In Jet, the Counter(1,1) gives me a syntax error! But the
Identity(1,1) works. I tried it and it does auto-increment my Id
field. Looks like i'm sticking w/ IDENTITY(1,1).

Thanks VERY much with your help w/ this!

John

 

[Michael C#]

Interesting. I created a table in Access using the COUNTER(1,1) and it
worked, but IDENTITY wouldn't. Another one of those weird Access/Jet issues
I suppose.