Create Database

[TimHebel]

I want to use VB.net to create a and Access database named PFL2.mdb. I don't
want to anything web-based or server-based. Just a plain Access database.
Does ANYONE know how to do this? Every example in the world seems to rely
on the database existing already. I have spent the last 14 hours searching
for the correct syntax.

Please Help,

Tim

[Steve Cochran]

I don't think you can do this with dotnet, at least from the responses I =
got in the MS NGs about this. I'm thinking about making a dll in VB6 to =
do this with RDO and then seeing if I can call it from dotnet.

steve

"TimHebel" <timhebel@hotmail.com> wrote in message =
news:3ca674c7$1@10.1.10.29...
>=20
> I want to use VB.net to create a and Access database named PFL2.mdb. =
I don't
> want to anything web-based or server-based. Just a plain Access =
database.
> Does ANYONE know how to do this? Every example in the world seems to =
rely
> on the database existing already. I have spent the last 14 hours =
searching
> for the correct syntax. =20
>=20
> Please Help,
>=20
> Tim

[Scott Hutchinson]

I agree with you that there is no information to be found on this subject.
I've tried before myself. In my limited experience, you must create the
database file first in Access before you can manipulate it programmatically
using ADO (or now ADO .NET).

Here are some possible work-arounds, but I haven't tried any of them yet:

1. Create the database by automating Access through its COM object library
2. Create an empty database to use as a template, then copy it and rename it
before adding data to it using ADO .NET.
3. Use something other than Access, such as MSDE or an XML file.
4. Try DAO or RDO instead of ADO .NET.

Scott Hutchinson
scotthutchinson@usa.net

"TimHebel" <timhebel@hotmail.com> wrote in message
news:3ca674c7$1@10.1.10.29...
>
> I want to use VB.net to create a and Access database named PFL2.mdb. I
don't
> want to anything web-based or server-based. Just a plain Access database.
> Does ANYONE know how to do this? Every example in the world seems to
rely
> on the database existing already. I have spent the last 14 hours
searching
> for the correct syntax.
>
> Please Help,
>
> Tim

[Steve Cochran]

Thanks, Michael. I'll play with it some.

steve

"Michael Culley" <m_culley@hotmail.com> wrote in message =
news:3ca6d7c4@10.1.10.29...
> > 1. Create the database by automating Access through its COM object =
library
>=20
> This relies on access being installed.
>=20
> > 2. Create an empty database to use as a template, then copy it and =
rename
> it
> > before adding data to it using ADO .NET.
>=20
> Could be a solution.
>=20
> > 3. Use something other than Access, such as MSDE or an XML file.
>=20
> Bit drastic
>=20
> > 4. Try DAO or RDO instead of ADO .NET.
>=20
> Better to just use ADOX. From what I have read .net just uses COM ADO
> underneath for accessing all databases except SQL Server 7 or above, =
so just
> adding a reference to adox won't do any harm.
>=20
> Steve,
>=20
> > I'm thinking about making a dll in VB6 to do this with RDO and then =
seeing
> if I can call it from dotnet.
>=20
> Theres no point doing this, just add to a reference to RDO (or adox) =
within
> dotnet. I think you will need adox for creating queries also.
>=20
> --
> Michael Culley
> www.vbdotcom.com
>=20
>

[Michael Culley]

> 1. Create the database by automating Access through its COM object library

This relies on access being installed.

> 2. Create an empty database to use as a template, then copy it and rename
it
> before adding data to it using ADO .NET.

Could be a solution.

> 3. Use something other than Access, such as MSDE or an XML file.

Bit drastic

> 4. Try DAO or RDO instead of ADO .NET.

Better to just use ADOX. From what I have read .net just uses COM ADO
underneath for accessing all databases except SQL Server 7 or above, so just
adding a reference to adox won't do any harm.

Steve,

> I'm thinking about making a dll in VB6 to do this with RDO and then seeing
if I can call it from dotnet.

Theres no point doing this, just add to a reference to RDO (or adox) within
dotnet. I think you will need adox for creating queries also.

--
Michael Culley
www.vbdotcom.com

[Scott Hutchinson]

Well I'll be, Michael was right on. It's as easy as adding a reference to
ADOX and running this sub (from this article
http://msdn.microsoft.com/library/de...us/ado270/htm/
admscdatabasecreationexample.asp):

'The following code shows how to create a new Microsoft Jet database with
the Create method.

Sub CreateDatabase()

Dim cat As New ADOX.Catalog()

cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\new.mdb")

End Sub

Scott Hutchinson
scotthutchinson@usa.net

"TimHebel" <timhebel@hotmail.com> wrote in message
news:3ca674c7$1@10.1.10.29...
>
> I want to use VB.net to create a and Access database named PFL2.mdb. I
don't
> want to anything web-based or server-based. Just a plain Access database.
> Does ANYONE know how to do this? Every example in the world seems to
rely
> on the database existing already. I have spent the last 14 hours
searching
> for the correct syntax.
>
> Please Help,
>
> Tim

[Scott Hutchinson]

I don't know for sure, but I would challenge your assertion that the
System.Data.OleDb namespace is just a wrapper for the ADO COM library. The
object model has some significant differences, and since it's virtually
identical to the SqlClient namespace, it seems to me that they would inherit
from the same base classes. But you could be right, because I seem to recall
that when I installed the .NET Framework, it ran a Windows Component Update
wizard that installed ADO 2.6. So, it's probably the case that any computer
with the .NET Framework installed can be assumed to have ADO 2.6 (and ADOX)
or newer as well. So the ADOX solution might be no problem to distribute.

Scott Hutchinson
scotthutchinson@usa.net

"Michael Culley" <m_culley@hotmail.com> wrote in message
news:3ca791bb$1@10.1.10.29...
> Scott,
>
> The thing I am curious about is installation of the completed app. If .net
> oledb providers just use ado underneath then I'm assuming that
> ado2.something gets installed with the framework, so maybe no additional
> files need to be distributed, or maybe just the adox dll needs to be
> included.
>
> --
> Michael Culley
> www.vbdotcom.com
>
>
> "Scott Hutchinson" <scotthutchinson@usa.net> wrote in message
> news:3ca73639$1@10.1.10.29...
> > Well I'll be, Michael was right on. It's as easy as adding a reference
to
> > ADOX and running this sub (from this article
> >
>
http://msdn.microsoft.com/library/de...us/ado270/htm/
> > admscdatabasecreationexample.asp):
> >
> > 'The following code shows how to create a new Microsoft Jet database
with
> > the Create method.
> >
> > Sub CreateDatabase()
> >
> > Dim cat As New ADOX.Catalog()
> >
> > cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\new.mdb")
> >
> > End Sub
> >
> > Scott Hutchinson
> > scotthutchinson@usa.net
> >
> > "TimHebel" <timhebel@hotmail.com> wrote in message
> > news:3ca674c7$1@10.1.10.29...
> > >
> > > I want to use VB.net to create a and Access database named PFL2.mdb.
I
> > don't
> > > want to anything web-based or server-based. Just a plain Access
> database.
> > > Does ANYONE know how to do this? Every example in the world seems to
> > rely
> > > on the database existing already. I have spent the last 14 hours
> > searching
> > > for the correct syntax.
> > >
> > > Please Help,
> > >
> > > Tim
> >
> >
>
>

[Michael Culley]

Scott,

The thing I am curious about is installation of the completed app. If .net
oledb providers just use ado underneath then I'm assuming that
ado2.something gets installed with the framework, so maybe no additional
files need to be distributed, or maybe just the adox dll needs to be
included.

--
Michael Culley
www.vbdotcom.com


"Scott Hutchinson" <scotthutchinson@usa.net> wrote in message
news:3ca73639$1@10.1.10.29...
> Well I'll be, Michael was right on. It's as easy as adding a reference to
> ADOX and running this sub (from this article
>
http://msdn.microsoft.com/library/de...us/ado270/htm/
> admscdatabasecreationexample.asp):
>
> 'The following code shows how to create a new Microsoft Jet database with
> the Create method.
>
> Sub CreateDatabase()
>
> Dim cat As New ADOX.Catalog()
>
> cat.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\new.mdb")
>
> End Sub
>
> Scott Hutchinson
> scotthutchinson@usa.net
>
> "TimHebel" <timhebel@hotmail.com> wrote in message
> news:3ca674c7$1@10.1.10.29...
> >
> > I want to use VB.net to create a and Access database named PFL2.mdb. I
> don't
> > want to anything web-based or server-based. Just a plain Access
database.
> > Does ANYONE know how to do this? Every example in the world seems to
> rely
> > on the database existing already. I have spent the last 14 hours
> searching
> > for the correct syntax.
> >
> > Please Help,
> >
> > Tim
>
>

[Michael Culley]

Micka,

> AFAIK an MDAC install is the only way you are allowed to add ADOX?

> Better to just use DAO -- guaranteed to be there if Jet is and easy to
> redist with the license that has been around for the last few versions.

I believe, as scott said, that MDAC is installed with the framework so this
should be no problem. I think DAO is less likely to be there.

Scott,

> I don't know for sure, but I would challenge your assertion that the
> System.Data.OleDb namespace is just a wrapper for the ADO COM library.

I've had a look again at where I read this and it seems that .net oledb
providers use com interop underneath to wrap OLEDB, not ADO itself. I'm not
sure of the exact difference myself but using the SQL providers in .net is
alot faster because it is all dotnet. I am reading this from "Professional
ADO.Net Programming" from wrox press.

I think that if this was untrue I would have been flamed to death by now

--
Michael Culley
www.vbdotcom.com

[Scott Hutchinson]

MichKa is definitely right about a couple of things. The Jet OLE DB
provider, ODBC driver, and Jet engine HAVE all been removed from MDAC 2.6
and newer--I didn't realize that. However, the Jet components (including
DAO) ship with Windows 2000, XP, and ME. MDAC 2.5 is pre-installed in
Windows Me and Windows 2000. And MDAC 2.7 RTM is pre-installed in Windows
XP. So unless you're running .NET on Windows 98 or NT, Jet, ADOX, and DAO
will definitely be there, and both DAO and ADOX should be available to
create an Access database. (By the way, ADOX was no in MDAC 2.1, which
originally shipped with SQL Server 7 in Nov '99.)

See the excerpt below from
http://msdn.microsoft.com/library/de...us/dnmdac/html
/data_mdacinstall.asp, as well as the KB article at
http://support.microsoft.com/default...08&id=Q271908.
Also, see below some example DAO and ADOX code below for creating Access
databases and tables.
MDAC Components Overview
As mentioned earlier, MDAC is not a single product, but a collection of
components that comprise several data access technologies. The MDAC
components have historically included the core pieces OLE DB, ODBC, ADO, and
RDS. In addition, the Jet database engine is included in versions earlier
than MDAC 2.6. MDAC also includes several ODBC drivers and OLE DB providers
for common data sources, including SQL Server, Microsoft FoxPro®, Oracle,
and Jet (earlier than MDAC 2.6). For a detailed history of MDAC components
through MDAC version 2.0, see Redistributing Microsoft Data Access
Components.

In MDAC versions that are later than MDAC 2.6, Jet is not included with the
MDAC components set; therefore, the Jet engine core components, the Jet ODBC
driver, and the Jet OLE DB provider are no longer included as a part of
MDAC.

The latest Jet components are available as a part of MDAC 2.5 SP2. If you
want to use MDAC 2.6 or later (including the latest Jet components), first
install MDAC 2.5 SP2, and then upgrade to the latest Jet components. You can
also obtain the latest Jet components for installation on existing MDAC 2.6
and later installations as a stand-alone distributable file. For information
about obtaining this distributable file, see the Microsoft Knowledge Base
article Q239114, ACC2000: Updated Version of Microsoft Jet 4.0 Available in
Download Center.

On Windows XP, the Jet components that are included with MDAC 2.5 SP2 are
pre-installed with the OS. For more information about Jet's removal from
MDAC, see the Microsoft Knowledge Base article Q271908, INFO: MDAC Version
2.6 and Later Do Not Contain Jet or Desktop ODBC Drivers.

For a complete list of the files (and their versions) that are included with
each MDAC release, see MDAC Release Manifests.

4.. DAO
1.. ADO

Creating a Database
Dim wsp As Workspace, dbs As Database
Set wsp = DBEngine(0)
Set dbs = wsp.CreateDatabase("Newdb.mdb")
Dim cat As New ADOX.Catalog
cat.Create "Provider = Microsoft.Jet.OLEDB.4.0;" _
& "Data Source = Newdb.mdb"

Creating a Table
Dim dbs As Database
Dim tdf As TableDef, fld As Field

Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("NewTable")
Set fld = tdf.CreateField("FName", dbText, 50)
tdf.Fields.Append fld
dbs.TableDefs.Append tdf
Dim cat As New ADOX.Catalog
Dim tbl As New ADOX.Table

Set cat.ActiveConnection = CurrentProject.Connection
tbl.Name = "NewTable"
tbl.Columns.Append "FName", adVarWChar, 50
Cat.Tables.Append tbl


Scott Hutchinson
scotthutchinson@usa.net

"Michael (michka) Kaplan" <former_mvp@nospam.trigeminal.spamless.com> wrote
in message news:3ca7acda$1@10.1.10.29...
> "Michael Culley" <m_culley@hotmail.com> wrote...
>
> > Micka,
>
> Typo?
>
> > > AFAIK an MDAC install is the only way you are allowed to add ADOX?
> >
> > > Better to just use DAO -- guaranteed to be there if Jet is and easy to
> > > redist with the license that has been around for the last few
versions.
> >
> > I believe, as scott said, that MDAC is installed with the framework so
> this
> > should be no problem. I think DAO is less likely to be there.
>
> Actually, I believe Scott is wrong. If you install the framework redist,
> MDAC is recommended but not required. And neither 2.6 nor 2.7 includes
Jet.
>
> If JET is there, then DAO will be too. The same cannot be said of MDAC >=
> 2.6.
>
>
> --
> MichKa
>
> Michael Kaplan
> Trigeminal Software, Inc. -- http://www.trigeminal.com/
>
> International VB? -- http://www.i18nWithVB.com/
> C++? MSLU -- http://msdn.microsoft.com/msdnmag/issues/01/10/
>
>
>

[Michael Culley]

> > Micka,
>
> Typo?

Oops, sorry My spelling/typing is atrocious (I had to use word to work
out how to spell atrocious)

> If JET is there, then DAO will be too. The same cannot be said of MDAC >=
> 2.6.

I'll have to take your word on this as the number of installation
permutations and combinations has got me a little confused

--
Michael Culley
www.vbdotcom.com


"Michael (michka) Kaplan" <former_mvp@nospam.trigeminal.spamless.com> wrote
in message news:3ca7acda$1@10.1.10.29...
> "Michael Culley" <m_culley@hotmail.com> wrote...
>
> > Micka,
>
> Typo?
>
> > > AFAIK an MDAC install is the only way you are allowed to add ADOX?
> >
> > > Better to just use DAO -- guaranteed to be there if Jet is and easy to
> > > redist with the license that has been around for the last few
versions.
> >
> > I believe, as scott said, that MDAC is installed with the framework so
> this
> > should be no problem. I think DAO is less likely to be there.
>
> Actually, I believe Scott is wrong. If you install the framework redist,
> MDAC is recommended but not required. And neither 2.6 nor 2.7 includes
Jet.
>
> If JET is there, then DAO will be too. The same cannot be said of MDAC >=
> 2.6.
>
>
> --
> MichKa
>
> Michael Kaplan
> Trigeminal Software, Inc. -- http://www.trigeminal.com/
>
> International VB? -- http://www.i18nWithVB.com/
> C++? MSLU -- http://msdn.microsoft.com/msdnmag/issues/01/10/
>
>
>

[Scott Hutchinson]

Interestingly, according to this help article, .NET requires MDAC 2.6 ONLY
for the SQL Server .NET Data Provider (i.e., the Data.SqlClient namespace),
NOT the OLE DB namespace.

http://msdn.microsoft.com/library/de...us/cpguide/htm
l/cpconnetframeworksystemrequirements.asp

Scott Hutchinson
scotthutchinson@usa.net

"TimHebel" <timhebel@hotmail.com> wrote in message
news:3ca674c7$1@10.1.10.29...
>
> I want to use VB.net to create a and Access database named PFL2.mdb. I
don't
> want to anything web-based or server-based. Just a plain Access database.
> Does ANYONE know how to do this? Every example in the world seems to
rely
> on the database existing already. I have spent the last 14 hours
searching
> for the correct syntax.
>
> Please Help,
>
> Tim

[Scott Hutchinson]

But this article (see excerpt below from
http://msdn.microsoft.com/library/de...us/vsintro7/ht
ml/vxtskAddingLaunchConditionForMicrosoftDataAccessComponents.asp) and
others state that System.Data.dll in general is dependent on MDAC.

Any Visual Basic or Visual C# application that includes data access has a
dependency on Microsoft Data Access Components (MDAC) version 2.6 or higher.
MDAC must be installed on a target computer prior to installing your
application or the application will fail.

For any application that has a dependency on MDAC, you will need to add a
launch condition to the application's deployment project that checks for the
correct version of MDAC and, if not found, halts the installation and warns
the user.

Note An alternative to adding a launch condition is to include an MDAC
merge module in your deployment project that will automatically install
MDAC, if needed. A merge module for MDAC is not included in Visual Studio
..NET; however, one may be available at a later date on the Microsoft Web
site.

Scott Hutchinson
scotthutchinson@usa.net

"TimHebel" <timhebel@hotmail.com> wrote in message
news:3ca674c7$1@10.1.10.29...
>
> I want to use VB.net to create a and Access database named PFL2.mdb. I
don't
> want to anything web-based or server-based. Just a plain Access database.
> Does ANYONE know how to do this? Every example in the world seems to
rely
> on the database existing already. I have spent the last 14 hours
searching
> for the correct syntax.
>
> Please Help,
>
> Tim