By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
449,313 Members | 1,696 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 449,313 IT Pros & Developers. It's quick & easy.

Creating an Access DB & Tables

P: n/a
Does anyone know of a good example for creating a access database and then
tables within that database.

All the examples I have found so far use a SQL database.

Thanks,

Ken

Nov 21 '05 #1
Share this Question
Share on Google+
18 Replies


P: n/a
Ken

Here's a sample I cut and pasted from a prior program I worked on
Hope it helps. Note that when appending the items you can specify if the
column data is nullable, a size, a datatype etc etc.

Mike

Private Sub CreateDatabase(ByVal filename As String)

Dim CnnNew As ADODB.Connection

Dim CatNew As ADOX.Catalog

Try

' Open a Connection and create the Catalog (Database)

'

CnnNew = New ADODB.Connection

CatNew = New ADOX.Catalog

CatNew.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & filename & ";" & _

"Jet OLEDB:Engine Type=5")
'Open the connection

CnnNew.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & filename)

'Open the Catalog

CatNew.ActiveConnection = CnnNew

'

'Now build all the Database tables as desired. Add or delete column

'attributes or properties as desired.

'

'Build and add the first Table

'

Dim objDesignTable As New ADOX.Table

With objDesignTable

..Name = "xxxxxxxx"

..ParentCatalog = CatNew

With .Columns

..Append("Units", ADOX.DataTypeEnum.adVarWChar)

..Append("Code", ADOX.DataTypeEnum.adVarWChar)

Append("Change X", adDouble)

..Append("Density", adDouble)

..Append("NA1", adDouble)

..Item("NA1").Attributes = ColumnAttributesEnum.adColNullable

..Append("Occasional Load Factor", adDouble)

..Append("Load Case W1", adBoolean)

..Append("Load Case T1", adBoolean)

..Append("Load X", adDouble)

..Item("Load X").Attributes = ColumnAttributesEnum.adColNullable

..Append("NA2", adDouble)

..Item("NA2").Attributes = ColumnAttributesEnum.adColNullable

..Append("FLAG1", adBoolean)

..Append("FLAG2", adBoolean)

..Append("FLAG3", adBoolean)

End With

'Create and Append a new auto incrementing column to be used

'as the primary key for the table

..Columns.Append("KeyID", adInteger)

..Columns("KeyID").Properties("AutoIncrement").Val ue = True

End With

'Create and Append a new key. Note that we are merely passing

'the "KeyID" column as the source of the primary key. This

'new Key will be Appended to the Keys Collection.

Dim objDesignKey As New ADOX.Key

objDesignKey.Name = "PrimaryKey"

objDesignKey.Type = KeyTypeEnum.adKeyPrimary

objDesignKey.Columns.Append("KeyID")

objDesignTable.Keys.Append(objDesignKey)

'Append the newly created table to the Tables Collection

CatNew.Tables.Append(objDesignTable)

' clean up objects

objDesignKey = Nothing

objDesignTable = Nothing

'. Add or delete column

'attributes or properties as desired.

catch

'error code here

end try

end sub

"Ken Kazinski" <Ke*********@discussions.microsoft.com> wrote in message
news:42**********************************@microsof t.com...
Does anyone know of a good example for creating a access database and then
tables within that database.

All the examples I have found so far use a SQL database.

Thanks,

Ken

Nov 21 '05 #2

P: n/a
Thanks Mike.

What references are required?

Ken
"Michael" wrote:
Ken

Here's a sample I cut and pasted from a prior program I worked on
Hope it helps. Note that when appending the items you can specify if the
column data is nullable, a size, a datatype etc etc.

Mike

Private Sub CreateDatabase(ByVal filename As String)

Dim CnnNew As ADODB.Connection

Dim CatNew As ADOX.Catalog

Try

' Open a Connection and create the Catalog (Database)

'

CnnNew = New ADODB.Connection

CatNew = New ADOX.Catalog

CatNew.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & filename & ";" & _

"Jet OLEDB:Engine Type=5")
'Open the connection

CnnNew.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & filename)

'Open the Catalog

CatNew.ActiveConnection = CnnNew

'

'Now build all the Database tables as desired. Add or delete column

'attributes or properties as desired.

'

'Build and add the first Table

'

Dim objDesignTable As New ADOX.Table

With objDesignTable

..Name = "xxxxxxxx"

..ParentCatalog = CatNew

With .Columns

..Append("Units", ADOX.DataTypeEnum.adVarWChar)

..Append("Code", ADOX.DataTypeEnum.adVarWChar)

Append("Change X", adDouble)

..Append("Density", adDouble)

..Append("NA1", adDouble)

..Item("NA1").Attributes = ColumnAttributesEnum.adColNullable

..Append("Occasional Load Factor", adDouble)

..Append("Load Case W1", adBoolean)

..Append("Load Case T1", adBoolean)

..Append("Load X", adDouble)

..Item("Load X").Attributes = ColumnAttributesEnum.adColNullable

..Append("NA2", adDouble)

..Item("NA2").Attributes = ColumnAttributesEnum.adColNullable

..Append("FLAG1", adBoolean)

..Append("FLAG2", adBoolean)

..Append("FLAG3", adBoolean)

End With

'Create and Append a new auto incrementing column to be used

'as the primary key for the table

..Columns.Append("KeyID", adInteger)

..Columns("KeyID").Properties("AutoIncrement").Val ue = True

End With

'Create and Append a new key. Note that we are merely passing

'the "KeyID" column as the source of the primary key. This

'new Key will be Appended to the Keys Collection.

Dim objDesignKey As New ADOX.Key

objDesignKey.Name = "PrimaryKey"

objDesignKey.Type = KeyTypeEnum.adKeyPrimary

objDesignKey.Columns.Append("KeyID")

objDesignTable.Keys.Append(objDesignKey)

'Append the newly created table to the Tables Collection

CatNew.Tables.Append(objDesignTable)

' clean up objects

objDesignKey = Nothing

objDesignTable = Nothing

'. Add or delete column

'attributes or properties as desired.

catch

'error code here

end try

end sub

"Ken Kazinski" <Ke*********@discussions.microsoft.com> wrote in message
news:42**********************************@microsof t.com...
Does anyone know of a good example for creating a access database and then
tables within that database.

All the examples I have found so far use a SQL database.

Thanks,

Ken


Nov 21 '05 #3

P: n/a
Ken
Add Project COM references
Microsoft ADO ext (#.#) for DDL and Security
Microsoft ActiveX Data Objects #.# Library
Mike

"Ken Kazinski" <Ke*********@discussions.microsoft.com> wrote in message
news:4B**********************************@microsof t.com...
Thanks Mike.

What references are required?

Ken
"Michael" wrote:
Ken

Here's a sample I cut and pasted from a prior program I worked on
Hope it helps. Note that when appending the items you can specify if the
column data is nullable, a size, a datatype etc etc.

Mike

Private Sub CreateDatabase(ByVal filename As String)

Dim CnnNew As ADODB.Connection

Dim CatNew As ADOX.Catalog

Try

' Open a Connection and create the Catalog (Database)

'

CnnNew = New ADODB.Connection

CatNew = New ADOX.Catalog

CatNew.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & filename & ";" & _

"Jet OLEDB:Engine Type=5")
'Open the connection

CnnNew.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
filename)

'Open the Catalog

CatNew.ActiveConnection = CnnNew

'

'Now build all the Database tables as desired. Add or delete column

'attributes or properties as desired.

'

'Build and add the first Table

'

Dim objDesignTable As New ADOX.Table

With objDesignTable

..Name = "xxxxxxxx"

..ParentCatalog = CatNew

With .Columns

..Append("Units", ADOX.DataTypeEnum.adVarWChar)

..Append("Code", ADOX.DataTypeEnum.adVarWChar)

Append("Change X", adDouble)

..Append("Density", adDouble)

..Append("NA1", adDouble)

..Item("NA1").Attributes = ColumnAttributesEnum.adColNullable

..Append("Occasional Load Factor", adDouble)

..Append("Load Case W1", adBoolean)

..Append("Load Case T1", adBoolean)

..Append("Load X", adDouble)

..Item("Load X").Attributes = ColumnAttributesEnum.adColNullable

..Append("NA2", adDouble)

..Item("NA2").Attributes = ColumnAttributesEnum.adColNullable

..Append("FLAG1", adBoolean)

..Append("FLAG2", adBoolean)

..Append("FLAG3", adBoolean)

End With

'Create and Append a new auto incrementing column to be used

'as the primary key for the table

..Columns.Append("KeyID", adInteger)

..Columns("KeyID").Properties("AutoIncrement").Val ue = True

End With

'Create and Append a new key. Note that we are merely passing

'the "KeyID" column as the source of the primary key. This

'new Key will be Appended to the Keys Collection.

Dim objDesignKey As New ADOX.Key

objDesignKey.Name = "PrimaryKey"

objDesignKey.Type = KeyTypeEnum.adKeyPrimary

objDesignKey.Columns.Append("KeyID")

objDesignTable.Keys.Append(objDesignKey)

'Append the newly created table to the Tables Collection

CatNew.Tables.Append(objDesignTable)

' clean up objects

objDesignKey = Nothing

objDesignTable = Nothing

'. Add or delete column

'attributes or properties as desired.

catch

'error code here

end try

end sub

"Ken Kazinski" <Ke*********@discussions.microsoft.com> wrote in message
news:42**********************************@microsof t.com...
> Does anyone know of a good example for creating a access database and
> then
> tables within that database.
>
> All the examples I have found so far use a SQL database.
>
> Thanks,
>
> Ken
>


Nov 21 '05 #4

P: n/a
Appreicate you procedure. Just one more question: What Imports are required?

"Michael" wrote:
Ken
Add Project COM references
Microsoft ADO ext (#.#) for DDL and Security
Microsoft ActiveX Data Objects #.# Library
Mike

"Ken Kazinski" <Ke*********@discussions.microsoft.com> wrote in message
news:4B**********************************@microsof t.com...
Thanks Mike.

What references are required?

Ken
"Michael" wrote:
Ken

Here's a sample I cut and pasted from a prior program I worked on
Hope it helps. Note that when appending the items you can specify if the
column data is nullable, a size, a datatype etc etc.

Mike

Private Sub CreateDatabase(ByVal filename As String)

Dim CnnNew As ADODB.Connection

Dim CatNew As ADOX.Catalog

Try

' Open a Connection and create the Catalog (Database)

'

CnnNew = New ADODB.Connection

CatNew = New ADOX.Catalog

CatNew.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _

"Data Source=" & filename & ";" & _

"Jet OLEDB:Engine Type=5")
'Open the connection

CnnNew.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
filename)

'Open the Catalog

CatNew.ActiveConnection = CnnNew

'

'Now build all the Database tables as desired. Add or delete column

'attributes or properties as desired.

'

'Build and add the first Table

'

Dim objDesignTable As New ADOX.Table

With objDesignTable

..Name = "xxxxxxxx"

..ParentCatalog = CatNew

With .Columns

..Append("Units", ADOX.DataTypeEnum.adVarWChar)

..Append("Code", ADOX.DataTypeEnum.adVarWChar)

Append("Change X", adDouble)

..Append("Density", adDouble)

..Append("NA1", adDouble)

..Item("NA1").Attributes = ColumnAttributesEnum.adColNullable

..Append("Occasional Load Factor", adDouble)

..Append("Load Case W1", adBoolean)

..Append("Load Case T1", adBoolean)

..Append("Load X", adDouble)

..Item("Load X").Attributes = ColumnAttributesEnum.adColNullable

..Append("NA2", adDouble)

..Item("NA2").Attributes = ColumnAttributesEnum.adColNullable

..Append("FLAG1", adBoolean)

..Append("FLAG2", adBoolean)

..Append("FLAG3", adBoolean)

End With

'Create and Append a new auto incrementing column to be used

'as the primary key for the table

..Columns.Append("KeyID", adInteger)

..Columns("KeyID").Properties("AutoIncrement").Val ue = True

End With

'Create and Append a new key. Note that we are merely passing

'the "KeyID" column as the source of the primary key. This

'new Key will be Appended to the Keys Collection.

Dim objDesignKey As New ADOX.Key

objDesignKey.Name = "PrimaryKey"

objDesignKey.Type = KeyTypeEnum.adKeyPrimary

objDesignKey.Columns.Append("KeyID")

objDesignTable.Keys.Append(objDesignKey)

'Append the newly created table to the Tables Collection

CatNew.Tables.Append(objDesignTable)

' clean up objects

objDesignKey = Nothing

objDesignTable = Nothing

'. Add or delete column

'attributes or properties as desired.

catch

'error code here

end try

end sub

"Ken Kazinski" <Ke*********@discussions.microsoft.com> wrote in message
news:42**********************************@microsof t.com...
> Does anyone know of a good example for creating a access database and
> then
> tables within that database.
>
> All the examples I have found so far use a SQL database.
>
> Thanks,
>
> Ken
>


Nov 21 '05 #5

P: n/a
I'm not Michael, but, you need to use: Imports ADOX
james

"Dennis" <De****@discussions.microsoft.com> wrote in message news:61**********************************@microsof t.com...
Appreicate you procedure. Just one more question: What Imports are required?

"Michael" wrote:
Ken
Add Project COM references
Microsoft ADO ext (#.#) for DDL and Security
Microsoft ActiveX Data Objects #.# Library
Mike

"Ken Kazinski" <Ke*********@discussions.microsoft.com> wrote in message
news:4B**********************************@microsof t.com...
> Thanks Mike.
>
> What references are required?
>
> Ken
>
>
> "Michael" wrote:
>
>> Ken
>>
>> Here's a sample I cut and pasted from a prior program I worked on
>> Hope it helps. Note that when appending the items you can specify if the
>> column data is nullable, a size, a datatype etc etc.
>>
>> Mike
>>
>> Private Sub CreateDatabase(ByVal filename As String)
>>
>> Dim CnnNew As ADODB.Connection
>>
>> Dim CatNew As ADOX.Catalog
>>
>> Try
>>
>> ' Open a Connection and create the Catalog (Database)
>>
>> '
>>
>> CnnNew = New ADODB.Connection
>>
>> CatNew = New ADOX.Catalog
>>
>> CatNew.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & _
>>
>> "Data Source=" & filename & ";" & _
>>
>> "Jet OLEDB:Engine Type=5")
>>
>>
>> 'Open the connection
>>
>> CnnNew.Open("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" &
>> filename)
>>
>> 'Open the Catalog
>>
>> CatNew.ActiveConnection = CnnNew
>>
>> '
>>
>> 'Now build all the Database tables as desired. Add or delete column
>>
>> 'attributes or properties as desired.
>>
>> '
>>
>> 'Build and add the first Table
>>
>> '
>>
>> Dim objDesignTable As New ADOX.Table
>>
>> With objDesignTable
>>
>> ..Name = "xxxxxxxx"
>>
>> ..ParentCatalog = CatNew
>>
>> With .Columns
>>
>> ..Append("Units", ADOX.DataTypeEnum.adVarWChar)
>>
>> ..Append("Code", ADOX.DataTypeEnum.adVarWChar)
>>
>> Append("Change X", adDouble)
>>
>> ..Append("Density", adDouble)
>>
>> ..Append("NA1", adDouble)
>>
>> ..Item("NA1").Attributes = ColumnAttributesEnum.adColNullable
>>
>> ..Append("Occasional Load Factor", adDouble)
>>
>> ..Append("Load Case W1", adBoolean)
>>
>> ..Append("Load Case T1", adBoolean)
>>
>> ..Append("Load X", adDouble)
>>
>> ..Item("Load X").Attributes = ColumnAttributesEnum.adColNullable
>>
>> ..Append("NA2", adDouble)
>>
>> ..Item("NA2").Attributes = ColumnAttributesEnum.adColNullable
>>
>> ..Append("FLAG1", adBoolean)
>>
>> ..Append("FLAG2", adBoolean)
>>
>> ..Append("FLAG3", adBoolean)
>>
>> End With
>>
>> 'Create and Append a new auto incrementing column to be used
>>
>> 'as the primary key for the table
>>
>> ..Columns.Append("KeyID", adInteger)
>>
>> ..Columns("KeyID").Properties("AutoIncrement").Val ue = True
>>
>> End With
>>
>> 'Create and Append a new key. Note that we are merely passing
>>
>> 'the "KeyID" column as the source of the primary key. This
>>
>> 'new Key will be Appended to the Keys Collection.
>>
>> Dim objDesignKey As New ADOX.Key
>>
>> objDesignKey.Name = "PrimaryKey"
>>
>> objDesignKey.Type = KeyTypeEnum.adKeyPrimary
>>
>> objDesignKey.Columns.Append("KeyID")
>>
>> objDesignTable.Keys.Append(objDesignKey)
>>
>> 'Append the newly created table to the Tables Collection
>>
>> CatNew.Tables.Append(objDesignTable)
>>
>> ' clean up objects
>>
>> objDesignKey = Nothing
>>
>> objDesignTable = Nothing
>>
>> '. Add or delete column
>>
>> 'attributes or properties as desired.
>>
>> catch
>>
>> 'error code here
>>
>> end try
>>
>> end sub
>>
>> "Ken Kazinski" <Ke*********@discussions.microsoft.com> wrote in message
>> news:42**********************************@microsof t.com...
>> > Does anyone know of a good example for creating a access database and
>> > then
>> > tables within that database.
>> >
>> > All the examples I have found so far use a SQL database.
>> >
>> > Thanks,
>> >
>> > Ken
>> >
>>
>>
>>


Nov 21 '05 #6

P: n/a
Michael,

In fact is only the Microsoft ADOx.x for DLL and Security needed when you do
the building from the tables with Adonet instead with ADODB.

When you want a sample of that, reply than I have sent a times that too this
newsgroup.

Cor
Nov 21 '05 #7

P: n/a
On Sun, 23 Jan 2005 18:44:12 +0100, "Cor Ligthert" <no************@planet.nl> wrote:

Michael,

In fact is only the Microsoft ADOx.x for DLL and Security needed when you do
the building from the tables with Adonet instead with ADODB.


AFAIK you need both.

If I remember correctly, the ADOX interop assembly only accepts an ADO Connection object for the
Catalog ActiveConnection property.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 21 '05 #8

P: n/a
Paul,
AFAIK you need both.

If I remember correctly, the ADOX interop assembly only accepts an ADO
Connection object for the
Catalog ActiveConnection property.


You don't, see this sample I have showed often in the dotnet newsgroups.
http://groups-beta.google.com/group/...00033570443de4

I have probably a later one as well, this is a little bit overcomplete one.

However I hope that this makes it clear.

Cor

Nov 21 '05 #9

P: n/a
On Mon, 24 Jan 2005 17:30:56 +0100, "Cor Ligthert" <no************@planet.nl> wrote:

Paul,

> AFAIK you need both.
>
> If I remember correctly, the ADOX interop assembly only accepts an ADO
> Connection object for the
> Catalog ActiveConnection property.
>

You don't, see this sample I have showed often in the dotnet newsgroups.
http://groups-beta.google.com/group/...00033570443de4

I have probably a later one as well, this is a little bit overcomplete one.

However I hope that this makes it clear.


I see, you used the Create method to specify the connection string. Nice shortcut.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 21 '05 #10

P: n/a
Hi Cor,

When I use the adox.create I get a error - "Could not find installable
ISAM.". Is there something else that needs to be referenced.

I have ADODB, ADO Ext. 2.8 in my references.

Thanks,

Ken
"Cor Ligthert" wrote:
Paul,
AFAIK you need both.

If I remember correctly, the ADOX interop assembly only accepts an ADO
Connection object for the
Catalog ActiveConnection property.


You don't, see this sample I have showed often in the dotnet newsgroups.
http://groups-beta.google.com/group/...00033570443de4

I have probably a later one as well, this is a little bit overcomplete one.

However I hope that this makes it clear.

Cor

Nov 21 '05 #11

P: n/a
Ken

I tried "Adox ext 2.8 for dll and security" with my sample and had no
problem at all.

Cor
Nov 21 '05 #12

P: n/a
On Tue, 25 Jan 2005 08:51:03 -0800, Ken Kazinski <Ke*********@discussions.microsoft.com> wrote:

Hi Cor,

When I use the adox.create I get a error - "Could not find installable
ISAM.". Is there something else that needs to be referenced.

I have ADODB, ADO Ext. 2.8 in my references.


Possibly a bad connection string? You may want to post it so we can take a look.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 21 '05 #13

P: n/a
Hi Guys,

It was the connection string DataSource is 2 words.

Thanks very much.

Ken
"Paul Clement" wrote:
On Tue, 25 Jan 2005 08:51:03 -0800, Ken Kazinski <Ke*********@discussions.microsoft.com> wrote:

¤ Hi Cor,
¤
¤ When I use the adox.create I get a error - "Could not find installable
¤ ISAM.". Is there something else that needs to be referenced.
¤
¤ I have ADODB, ADO Ext. 2.8 in my references.
¤

Possibly a bad connection string? You may want to post it so we can take a look.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)

Nov 21 '05 #14

P: n/a
Hi Guys,

One more question - how do you release the connection?

Sub CreateDB (DB_PathName as string)

Dim NewDB As New ADOX.Catalog

NewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DB_PathName)

NewDB = Nothing

This code leaves the *.ldb file in the directory and it can not be deleted
until the program exits.
Ken

Nov 21 '05 #15

P: n/a
Ken,

This answer I leave for Paul, he is much more busy with access than me.

I have this problem not by the way when the program closes the ldb is gone.

I am glad you found the error by the way.

Cor
Nov 21 '05 #16

P: n/a
Hi Cor,

I was looking for the newDB.close method or dispose method.

Ken
"Cor Ligthert" wrote:
Ken,

This answer I leave for Paul, he is much more busy with access than me.

I have this problem not by the way when the program closes the ldb is gone.

I am glad you found the error by the way.

Cor

Nov 21 '05 #17

P: n/a
On Wed, 26 Jan 2005 05:49:05 -0800, Ken Kazinski <Ke*********@discussions.microsoft.com> wrote:

Hi Guys,

One more question - how do you release the connection?

Sub CreateDB (DB_PathName as string)

Dim NewDB As New ADOX.Catalog

NewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DB_PathName)

NewDB = Nothing

This code leaves the *.ldb file in the directory and it can not be deleted
until the program exits.

Try the following:

NewDB.ActiveConnection.Close()
NewDB.ActiveConnection = Nothing

ADOX can be a bit of a problem when it comes to closing connections via the COM interop layer. This
is why I typically establish the connection using the ADO Connection object instead of ADOX.
Paul ~~~ pc******@ameritech.net
Microsoft MVP (Visual Basic)
Nov 21 '05 #18

P: n/a
Hi Paul,

That did it! It closed the data base and removed the *.ldb file.

Ken

Nov 21 '05 #19

This discussion thread is closed

Replies have been disabled for this discussion.