Creating MS Access database

[C# Beginner]

Hi there,

I'm currently trying to create a MS access file at runtime, but I stumble
into some problems.
1. When I use Datatype adUnsignedInt I get an error (invalid type).
2. Which datatype must I use to create an autonumbering field?
3. Which datatype must I use to create an OLE-object field?

I'm currently using Microsoft ActiveX Data Objects Library 2.8 and using
engine type 5.

Can anybody help me out here?

thnx

 

[Otis Mukinfus]

Hi there,

I'm currently trying to create a MS access file at runtime, but I stumble
into some problems.
1. When I use Datatype adUnsignedInt I get an error (invalid type).
2. Which datatype must I use to create an autonumbering field?
3. Which datatype must I use to create an OLE-object field?

I'm currently using Microsoft ActiveX Data Objects Library 2.8 and using
engine type 5.

Can anybody help me out here?

thnx

Is the database you are trying to create always the same(schema-wise)? If it is
why not just distribute an empty DB with all the tables, etc. already created
and copy it when you want to create a new one?

Additionally, DAO is still a viable option for doing this. To use it just add
the dll to your project.

Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[C# Beginner]

Hi Otis,

Thanks for the rapid respons.

I'm currently rewriting a program which I developed in VB6 using DAO, in
which users were able to create
their own databases at demand (no schema). I like to keep it that way.

My problem is that DataTypeEnum contains a lot of types of which some can't
be used (so it seems). So I need to understand if and how each type works.
OLE-object type and Autonumber are examples of types which aren't available
in DatatTypeEnum, so I must find an alternative type for them.

There are a lot of examples how the create a access database at runtime, but
I still can't find the solution for my problem. Most examples aren't writing
c# anyway.

thnx

DD

 

[Mark Rae]

There are a lot of examples how the create a access database at runtime,
but I still can't find the solution for my problem. Most examples aren't
writing c# anyway.

Did you actually read Otis' reply?

How many different versions of this Access (by which, of course, you mean
Jet) database do you need to create...?

 

[C# Beginner]

Hi Mark,

Sorry, it seems that my english is a little bit poor, but I thought I
mentioned that the creation of databases depends on the users wishes at
runtime (have you actually read my mail?).

Though I thank Otis for his rapid respons and suggestion, his solution will
not do for my project.

The usage of DAO can be a solution, but I rather try it with .NET elements
(ADO/ADOX), since DAO is (officialy) not supported in .NET.

Back to my project. Think of it like VisData which was included in Visual
Studio 6.

DD

 

[Mark Rae]

but I thought I mentioned that the creation of databases depends on the
users wishes at runtime

You didn't - you said "I'm currently trying to create a MS access file at
runtime" and then mentioned some of the DDL issues you were facing - no
mention that the database needs to be different for each user...

(have you actually read my mail?).
Yes.

The usage of DAO can be a solution, but I rather try it with .NET elements
(ADO/ADOX), since DAO is (officialy) not supported in .NET.

ADOX is probably your best choice, even though it is not officially
supported in .NET either...
http://support.microsoft.com/kb/317881
http://www.google.co.uk/search?sourceid=navclient&ie=UTF-8&rls=GGLG,GGLG:2006-28,GGLG:en&q=.NET+ADOX

 

[C# Beginner]

Hi Mark,



Sorry I wasn't specific in my first mail, but I thought my response to Otis
was clear enough.

Maybe I was or maybe I wasn't.... no need to argue here. Next time I try to
choose my words more carefully and be more specific. (Though it's no easy
when trying to communicate in a language that is my native language, but I'm
still learning).



Enough said.




For anybody who's interested....

http://www.codeguru.com/cpp/data/mfc_database/ado/article.php/c4343/



Now I only have to figure out how to translate
'col.Properties["AutoIncrement"] = true'. It does work in VB, but it seems
that the Properties collection is readonly in C#.



thnx for the interesting links Mark



DD

 

[Otis Mukinfus]

Hi Mark,

Sorry, it seems that my english is a little bit poor, but I thought I
mentioned that the creation of databases depends on the users wishes at
runtime (have you actually read my mail?).

Though I thank Otis for his rapid respons and suggestion, his solution will
not do for my project.

The usage of DAO can be a solution, but I rather try it with .NET elements
(ADO/ADOX), since DAO is (officialy) not supported in .NET.

[snip]

C# Beginner,

Your original post said absolutely nothing about users creating their own
tables, etc. Have you bothered to go back and read your original post? Don't
be trying to get a splinter out of mine and Mark's eyes without first taking off
the blindfold you are wearing.

ADOX is not an element of the .NET framework. That is why you will not see it
listed if you type "ADOX" into the Visual Studio Help Index, but you WILL find
"DAO" if you type it in. Which do YOU think MS would rather you use????.

If you want an entirely .NET solution, then I suggest you ditch the Jet database
technology and use SQL server 2005 Express (free) or the new SQL Server Compact
Edition (not very well supported yet, but also free).

There is no reason you cannot have an empty Jet database for distribution and
then USE DDL to create the tables, etc for the user. Write the DDL script and
execute it with the ExecuteNonQuery method of the OLEDB provider. Copying the
Jet DB file is about three or four lines of code and I'm sure you know how to
write the DDL.


Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[Mark Rae]

ADOX is not an element of the .NET framework. That is why you will not
see it
listed if you type "ADOX" into the Visual Studio Help Index, but you WILL
find
"DAO" if you type it in.
Indeed.

If you want an entirely .NET solution, then I suggest you ditch the Jet
database
technology and use SQL server 2005 Express (free) or the new SQL Server
Compact
Edition (not very well supported yet, but also free).

In fact, Access 2007 does not even use the old Jet database format by
default: http://office.microsoft.com/en-us/access/HA100678311033.aspx
though, obviously, it *can* still work with the previous version...

There is no reason you cannot have an empty Jet database for distribution
and
then USE DDL to create the tables, etc for the user. Write the DDL script
and
execute it with the ExecuteNonQuery method of the OLEDB provider. Copying
the
Jet DB file is about three or four lines of code and I'm sure you know how
to
write the DDL.

Actually, that is probably the best solution - no need for ADO, ADOX or DAO
at all...

 

[C# Beginner]

Sorry for the confusion. It is correct I said nothing about users creating
their own databases in my first question. I mentioned it only later in my
first response to your first suggestion. I should have made that clearer in
my response to Mark. Sorry Mark.

Though I almost have the answer to my problem, you suggestion of using an
empty database and use later on the DDL to add more tables as wished by
other users, never crossed my mind. I was fixed on using only one method
(ADOX or DAO) and never thought of combining two methods (ADOX and DDL).

thnx for the tip.

DD

Hi Mark,

Sorry, it seems that my english is a little bit poor, but I thought I
mentioned that the creation of databases depends on the users wishes at
runtime (have you actually read my mail?).

Though I thank Otis for his rapid respons and suggestion, his solution
will
not do for my project.

The usage of DAO can be a solution, but I rather try it with .NET elements
(ADO/ADOX), since DAO is (officialy) not supported in .NET.

[snip]

C# Beginner,

Your original post said absolutely nothing about users creating their own
tables, etc. Have you bothered to go back and read your original post?
Don't
be trying to get a splinter out of mine and Mark's eyes without first
taking off
the blindfold you are wearing.

ADOX is not an element of the .NET framework. That is why you will not
see it
listed if you type "ADOX" into the Visual Studio Help Index, but you WILL
find
"DAO" if you type it in. Which do YOU think MS would rather you use????.

If you want an entirely .NET solution, then I suggest you ditch the Jet
database
technology and use SQL server 2005 Express (free) or the new SQL Server
Compact
Edition (not very well supported yet, but also free).

There is no reason you cannot have an empty Jet database for distribution
and
then USE DDL to create the tables, etc for the user. Write the DDL script
and
execute it with the ExecuteNonQuery method of the OLEDB provider. Copying
the
Jet DB file is about three or four lines of code and I'm sure you know how
to
write the DDL.


Good luck with your project,

Otis Mukinfus
http://www.arltex.com
http://www.tomchilders.com

 

[Mark Rae]

Though I almost have the answer to my problem, you suggestion of using an
empty database and use later on the DDL to add more tables as wished by
other users, never crossed my mind. I was fixed on using only one method
(ADOX or DAO) and never thought of combining two methods (ADOX and DDL).

No need for ADOX - that's the whole point...

1) Using Access (or whatever) create a totally blank database.

2) Add the totally blank database to your project as an embedded resource.

3) When you need to create a new database, extract the database from your
project and save it somewhere for the user

4) Add the various object to it as required by passing DDL SQL to the
ExecuteQuery method