-
Jul 20th, 2020, 08:46 AM
#1
[RESOLVED] Append table to ADO database
I'm a complete dummy when it comes to databases, so I thought I'd try out the simple ADO database code recommended by ChrisE in this VB.Net forum thread (post #48). It creates an .mdb file successfully but fails when I try to append a Table.
Code:
Imports ADOX
[....]
'create a connection string
Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mdbFileName
'replace existing file if any
IO.File.Delete(mdbFileName)
'create catalog
Dim cat As New Catalog()
cat.Create(connectionString)
'create a table with columns
Dim tbl1 As New Table
tbl1.Columns.Append("col1", DataTypeEnum.adInteger, 4)
tbl1.Columns.Append("col2", DataTypeEnum.adLongVarWChar, 255)
'add the table to the tables collection
Dim obj As Object = DirectCast(tbl1, Object)
cat.Tables.Append(obj)
The Append statement in the last line always throws an exception:
System.Runtime.InteropServices.COMException: 'Table ID is invalid.'
even when I comment out one or both columns. Can anyone tell me what is going wrong here?
BB
Last edited by boops boops; Jul 20th, 2020 at 08:50 AM.
-
Jul 20th, 2020, 12:44 PM
#2
Re: Append table to ADO database
is this with VB6 or with .NET ?
if with .NET do it like this
Code:
Public Function ExecuteSQL(ByVal Con As OleDb.OleDbConnection, _
ByVal sSQL As String, _
Optional ByRef ErrMessage As String = Nothing, _
Optional ByVal TransAction As _
OleDb.OleDbTransaction = Nothing) As Integer
ErrMessage = Nothing
Try
Dim Result As Integer = 0
Using Cmd As New OleDb.OleDbCommand(sSQL, Con, TransAction)
Result = Cmd.ExecuteNonQuery
End Using
Return Result
Catch ex As Exception
ErrMessage = ex.Message
Return 0
End Try
End Function
Private Sub Button3_Click(sender As System.Object, e As System.EventArgs) Handles Button3.Click
MakeTables()
End Sub
Public Sub MakeTables()
Dim sSQL As String
Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Berichte\Backup.mdb")
'here you create your Table in the Database
con.Open()
'create your Table tbl_Employee
sSQL = " Create Table tbl_Employee"
sSQL &= "( [EM_ID] AutoIncrement CONSTRAINT PRIMARYKEY PRIMARY KEY"
sSQL &= ", [EM_Firstname] varChar(50)"
sSQL &= ", [EM_Lastname] varChar(50) CONSTRAINT EM_Lastname UNIQUE"
sSQL &= ")"
ExecuteSQL(con, sSQL)
sSQL = " Create Table tbl_Contacts"
sSQL &= "( [CO_ID] AutoIncrement CONSTRAINT PRIMARYKEY PRIMARY KEY"
sSQL &= ", [FK_ID] Int NOT NULL"
sSQL &= ", [CO_Firstname] varChar(50)"
sSQL &= ", [CO_Lastname] varChar(50) CONSTRAINT CO_Lastname UNIQUE"
sSQL &= ")"
ExecuteSQL(con, sSQL)
sSQL = "ALTER TABLE tbl_Contacts "
sSQL &= " ADD FOREIGN KEY (FK_ID) REFERENCES tbl_Employee(EM_ID)"
ExecuteSQL(con, sSQL)
sSQL = "Insert Into tbl_Employee (EM_Firstname,EM_Lastname)Values('ChrisE','Test')"
ExecuteSQL(con, sSQL)
sSQL = "Insert Into tbl_Employee (EM_Firstname,EM_Lastname)Values('John','Doe')"
ExecuteSQL(con, sSQL)
sSQL = "Insert Into tbl_Contacts (CO_Firstname,CO_Lastname,FK_ID)Values('Mary','Poppins',1)"
ExecuteSQL(con, sSQL)
sSQL = "Insert Into tbl_Contacts (CO_Firstname,CO_Lastname,FK_ID)Values('Joe','Dipp',2)"
ExecuteSQL(con, sSQL)
con.Close()
con = Nothing
End Sub
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Jul 20th, 2020, 05:24 PM
#3
Re: Append table to ADO database
Thanks for your prompt reply Chris. It's VB.Net.
I was hoping I wouldn't get involved in sql because that will take fair amount of study if I want to understand what the code is doing. If I do, I'm sure I will find your example useful.
But is it possible to say in a few words what is wrong with my Tables.Append? I guess not.
BB
-
Jul 21st, 2020, 02:03 AM
#4
Re: Append table to ADO database
well you must assign to the Table collection
Code:
Dim tbl1 As ADOX Table
'...............................
Set tbl1 = New ADOX.Table
tbl1.Name = "yourTableName"
tbl1.Columns.Append("col1", DataTypeEnum.adInteger, 4)
tbl1.Columns.Append("col2", DataTypeEnum.adLongVarWChar, 255)
cat.Tables.Append tbl
'don't forget to set your Dim's to Nothing
con.Close
Set tbl1 = Nothing
'etc......
but I prefer creating table(s) with SQL, the way in Post#2
Last edited by ChrisE; Jul 21st, 2020 at 02:07 AM.
to hunt a species to extinction is not logical !
since 2010 the number of Tigers are rising again in 2016 - 3900 were counted. with Baby Callas it's 3901, my wife and I had 2-3 months the privilege of raising a Baby Tiger.
-
Jul 21st, 2020, 05:49 AM
#5
Re: [RESOLVED] Append table to ADO database
Thanks Chris, that's the solution: I have to give the table a name! I can now mark this thread Resolved and die happy.
My main aim for the moment is to find a way to store collections of graphics objects such as bitmaps, vectors, points and so on. I'm hoping that a database will be a good alternative to Serialization. Perhaps I shouldn't expect ADO or ADOX to be suitable for the purpose but for the moment it won't do me any harm to read about ADO and ADOX in the Microsoft documentation. No doubt I'll have plenty more stupid questions to ask.
cheers, BB
-
Jul 28th, 2020, 03:30 AM
#6
Lively Member
Re: [RESOLVED] Append table to ADO database
Hello,@boops boops
Please try this code,To Append table to ADO database
Code:
adVarWChar = 202
adInteger = 3
file = "C:\TEMP\NEW.MDB" ; change to suit your situation
Conn="Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%file%;Jet OLEDB:Engine Type=4"
IF FileExist(file) == @TRUE
FileDelete(file)
Endif
cat = ObjectOpen("ADOX.Catalog")
tbl = ObjectOpen("ADOX.Table")
cat.Create(Conn)
tbl.name = "Test" ; IMPORTANT: Name the table before attempting to append.
tbls = cat.Tables ; collection of tables
tbls.Append(tbl) ; Append table to tables collection
props = tbl.Properties; collection of properties
cols = tbl.Columns ; collection of columns
nCnt = props.Count()
message("Number of Table Properties",nCnt) ;
message("Number of Column Properties",cols.Count)
cols.append("ID",adInteger) ; Append column to columns collection
cols.append("ID2",adInteger) ; Append column to columns collection
cols.append("Name",adVarWChar) ; Append column to columns collection
message("NEW Number of Table Properties",props.Count)
message("NEW Number of Column Properties",cols.Count)
ObjectClose(tbl)
ObjectClose(tbls)
ObjectClose(cat)
BoxShut()
exit
I hope this code will be useful.
Thank you.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|