[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

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

---

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

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

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

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.