Error Creating Table in ADOX

[Wayne Wengert]

I am getting an error that "object no longer valid" at the point indicated
in the code below - I am trying to build a table in an Access 2000 database
using ADOX. Any thoughts on what might cause this?

=============== ===== code =============== ==
Sub CreateADOUnitsT able(ByVal sTablename As String)

Dim oDB As ADOX.Catalog

Dim oUnits As ADOX.Table

oUnits = New ADOX.Table

oDB = New ADOX.Catalog

oDB.ActiveConne ction = myConn

With oUnits

..Name = sTablename

..Columns.Appen d("UnitName", ADOX.DataTypeEn um.adVarWChar, 20)

..Columns.Appen d("Class", ADOX.DataTypeEn um.adVarWChar, 5)

..Columns.Appen d("Comments", ADOX.DataTypeEn um.adVarWChar, 35)

End With

oDB.Tables.Appe nd(oUnits) '<=== Error occurs here

oUnits = Nothing

oDB = Nothing

End Sub

[Cor Ligthert]

Wayne,

When I do this using classic Ado I get forever all kind of ISO errors.

I make only the database with ADO and go than directly to ADONET.

Here a sample (more complete than the previous),

I hope that helps?

Cor

\\\needs that ADOX 2.7 lib for etc.
Public Class Main
Public Shared Sub Main()
Dim catNewDB As New ADOX.Catalog
Dim fi As New IO.FileInfo("c: \db1.mdb")
If fi.Exists Then
If MessageBox.Show ("Delete?", "Existing File db1.mdb", _
MessageBoxButto ns.YesNo) = DialogResult.Ye s Then
fi.Delete()
Else
Exit Sub
End If
End If
catNewDB.Create ("Provider=Micr osoft.Jet.OLEDB .4.0;" & "Data
Source=C:\db1.m db")
'To make tables we use Adonet
Dim conn As New OleDb.OleDbConn ection("Provide r=Microsoft.Jet .OLEDB.4.0;" &
_
" Data Source=C:\db1.m db;User Id=admin;Passwo rd=;")
Dim cmd As New OleDb.OleDbComm and("CREATE TABLE persons ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"BirthDate datetime," & _
"IdCountry int," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
conn.Open()
Try
cmd.ExecuteNonQ uery()
Catch ex As OleDb.OleDbExce ption
MessageBox.Show (ex.Message, "OleDbException ")
Exit Sub
Catch ex As Exception
MessageBox.Show (ex.Message, "GeneralExcepti on")
Exit Sub
End Try
cmd = New OleDb.OleDbComm and("CREATE TABLE countries ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
Try
cmd.ExecuteNonQ uery()
Catch ex As OleDb.OleDbExce ption
MessageBox.Show (ex.Message, "OleDbException ")
Exit Sub
Catch ex As Exception
MessageBox.Show (ex.Message, "GeneralExcepti on")
Exit Sub
End Try
conn.Close()
End Sub
End Class
////

[Wayne Wengert]

Cor;

Thanks for that information. I'll try to adapt that to my project.

Wayne

"Cor Ligthert" <no**********@p lanet.nl> wrote in message
news:uN******** ******@tk2msftn gp13.phx.gbl...

Wayne,

When I do this using classic Ado I get forever all kind of ISO errors.

I make only the database with ADO and go than directly to ADONET.

Here a sample (more complete than the previous),

I hope that helps?

Cor

\\\needs that ADOX 2.7 lib for etc.
Public Class Main
Public Shared Sub Main()
Dim catNewDB As New ADOX.Catalog
Dim fi As New IO.FileInfo("c: \db1.mdb")
If fi.Exists Then
If MessageBox.Show ("Delete?", "Existing File db1.mdb", _
MessageBoxButto ns.YesNo) = DialogResult.Ye s Then
fi.Delete()
Else
Exit Sub
End If
End If
catNewDB.Create ("Provider=Micr osoft.Jet.OLEDB .4.0;" & "Data
Source=C:\db1.m db")
'To make tables we use Adonet
Dim conn As New OleDb.OleDbConn ection("Provide r=Microsoft.Jet .OLEDB.4.0;" & _
" Data Source=C:\db1.m db;User Id=admin;Passwo rd=;")
Dim cmd As New OleDb.OleDbComm and("CREATE TABLE persons ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"BirthDate datetime," & _
"IdCountry int," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
conn.Open()
Try
cmd.ExecuteNonQ uery()
Catch ex As OleDb.OleDbExce ption
MessageBox.Show (ex.Message, "OleDbException ")
Exit Sub
Catch ex As Exception
MessageBox.Show (ex.Message, "GeneralExcepti on")
Exit Sub
End Try
cmd = New OleDb.OleDbComm and("CREATE TABLE countries ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
Try
cmd.ExecuteNonQ uery()
Catch ex As OleDb.OleDbExce ption
MessageBox.Show (ex.Message, "OleDbException ")
Exit Sub
Catch ex As Exception
MessageBox.Show (ex.Message, "GeneralExcepti on")
Exit Sub
End Try
conn.Close()
End Sub
End Class
////

[Wayne Wengert]

Cor;

You were correct, that works much more reliably. Thanks for the education.

Wayne

"Cor Ligthert" <no**********@p lanet.nl> wrote in message
news:uN******** ******@tk2msftn gp13.phx.gbl...

Wayne,

When I do this using classic Ado I get forever all kind of ISO errors.

I make only the database with ADO and go than directly to ADONET.

Here a sample (more complete than the previous),

I hope that helps?

Cor

\\\needs that ADOX 2.7 lib for etc.
Public Class Main
Public Shared Sub Main()
Dim catNewDB As New ADOX.Catalog
Dim fi As New IO.FileInfo("c: \db1.mdb")
If fi.Exists Then
If MessageBox.Show ("Delete?", "Existing File db1.mdb", _
MessageBoxButto ns.YesNo) = DialogResult.Ye s Then
fi.Delete()
Else
Exit Sub
End If
End If
catNewDB.Create ("Provider=Micr osoft.Jet.OLEDB .4.0;" & "Data
Source=C:\db1.m db")
'To make tables we use Adonet
Dim conn As New OleDb.OleDbConn ection("Provide r=Microsoft.Jet .OLEDB.4.0;" & _
" Data Source=C:\db1.m db;User Id=admin;Passwo rd=;")
Dim cmd As New OleDb.OleDbComm and("CREATE TABLE persons ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"BirthDate datetime," & _
"IdCountry int," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
conn.Open()
Try
cmd.ExecuteNonQ uery()
Catch ex As OleDb.OleDbExce ption
MessageBox.Show (ex.Message, "OleDbException ")
Exit Sub
Catch ex As Exception
MessageBox.Show (ex.Message, "GeneralExcepti on")
Exit Sub
End Try
cmd = New OleDb.OleDbComm and("CREATE TABLE countries ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
Try
cmd.ExecuteNonQ uery()
Catch ex As OleDb.OleDbExce ption
MessageBox.Show (ex.Message, "OleDbException ")
Exit Sub
Catch ex As Exception
MessageBox.Show (ex.Message, "GeneralExcepti on")
Exit Sub
End Try
conn.Close()
End Sub
End Class
////

[Paul Clement]

On Fri, 20 Aug 2004 05:58:26 -0600, "Wayne Wengert" <wa************ ***@wengert.com > wrote:

¤ I am getting an error that "object no longer valid" at the point indicated
¤ in the code below - I am trying to build a table in an Access 2000 database
¤ using ADOX. Any thoughts on what might cause this?
¤
¤ =============== ===== code =============== ==
¤ Sub CreateADOUnitsT able(ByVal sTablename As String)
¤
¤ Dim oDB As ADOX.Catalog
¤
¤ Dim oUnits As ADOX.Table
¤
¤ oUnits = New ADOX.Table
¤
¤ oDB = New ADOX.Catalog
¤
¤ oDB.ActiveConne ction = myConn
¤
¤ With oUnits
¤
¤ .Name = sTablename

You may need to add here the following line of code:

..ParentCatalog = oDB

¤
¤ .Columns.Append ("UnitName", ADOX.DataTypeEn um.adVarWChar, 20)
¤
¤ .Columns.Append ("Class", ADOX.DataTypeEn um.adVarWChar, 5)
¤
¤ .Columns.Append ("Comments", ADOX.DataTypeEn um.adVarWChar, 35)
¤
¤ End With
¤
¤ oDB.Tables.Appe nd(oUnits) '<=== Error occurs here
¤
¤
¤
¤ oUnits = Nothing
¤
¤ oDB = Nothing
¤
¤ End Sub
¤

Paul ~~~ pc******@amerit ech.net
Microsoft MVP (Visual Basic)

[Wayne Wengert]

Thanks Paul, I'll try that.

Wayne

"Paul Clement" <Us************ ***********@sws pectrum.com> wrote in message
news:cq******** *************** *********@4ax.c om...

On Fri, 20 Aug 2004 05:58:26 -0600, "Wayne Wengert" <wa************ ***@wengert.com > wrote:
¤ I am getting an error that "object no longer valid" at the point indicated ¤ in the code below - I am trying to build a table in an Access 2000 database ¤ using ADOX. Any thoughts on what might cause this?
¤
¤ =============== ===== code =============== ==
¤ Sub CreateADOUnitsT able(ByVal sTablename As String)
¤
¤ Dim oDB As ADOX.Catalog
¤
¤ Dim oUnits As ADOX.Table
¤
¤ oUnits = New ADOX.Table
¤
¤ oDB = New ADOX.Catalog
¤
¤ oDB.ActiveConne ction = myConn
¤
¤ With oUnits
¤
¤ .Name = sTablename

You may need to add here the following line of code:

.ParentCatalog = oDB

¤
¤ .Columns.Append ("UnitName", ADOX.DataTypeEn um.adVarWChar, 20)
¤
¤ .Columns.Append ("Class", ADOX.DataTypeEn um.adVarWChar, 5)
¤
¤ .Columns.Append ("Comments", ADOX.DataTypeEn um.adVarWChar, 35)
¤
¤ End With
¤
¤ oDB.Tables.Appe nd(oUnits) '<=== Error occurs here
¤
¤
¤
¤ oUnits = Nothing
¤
¤ oDB = Nothing
¤
¤ End Sub
¤

Paul ~~~ pc******@amerit ech.net
Microsoft MVP (Visual Basic)