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 CreateADOUnitsTable(ByVal sTablename As String)

Dim oDB As ADOX.Catalog

Dim oUnits As ADOX.Table

oUnits = New ADOX.Table

oDB = New ADOX.Catalog

oDB.ActiveConnection = myConn

With oUnits

..Name = sTablename

..Columns.Append("UnitName", ADOX.DataTypeEnum.adVarWChar, 20)

..Columns.Append("Class", ADOX.DataTypeEnum.adVarWChar, 5)

..Columns.Append("Comments", ADOX.DataTypeEnum.adVarWChar, 35)

End With

oDB.Tables.Append(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", _
MessageBoxButtons.YesNo) = DialogResult.Yes Then
fi.Delete()
Else
Exit Sub
End If
End If
catNewDB.Create("Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\db1.mdb")
'To make tables we use Adonet
Dim conn As New OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" &
_
" Data Source=C:\db1.mdb;User Id=admin;Password=;")
Dim cmd As New OleDb.OleDbCommand("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.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
Exit Sub
End Try
cmd = New OleDb.OleDbCommand("CREATE TABLE countries ( " & _
"AutoId int identity ," & _
"Id int NOT NULL," & _
"Name NVarchar(50)," & _
"CONSTRAINT [pk_AutoId] PRIMARY KEY (AutoId)) ", conn)
Try
cmd.ExecuteNonQuery()
Catch ex As OleDb.OleDbException
MessageBox.Show(ex.Message, "OleDbException")
Exit Sub
Catch ex As Exception
MessageBox.Show(ex.Message, "GeneralException")
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

 

[Wayne Wengert]

Cor;

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

Wayne

 

[Paul Clement]

¤ 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 CreateADOUnitsTable(ByVal sTablename As String)
¤
¤ Dim oDB As ADOX.Catalog
¤
¤ Dim oUnits As ADOX.Table
¤
¤ oUnits = New ADOX.Table
¤
¤ oDB = New ADOX.Catalog
¤
¤ oDB.ActiveConnection = myConn
¤
¤ With oUnits
¤
¤ .Name = sTablename

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

..ParentCatalog = oDB

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

Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)

 

[Wayne Wengert]

Thanks Paul, I'll try that.

Wayne