Results 1 to 6 of 6

Thread: [RESOLVED] Append table to ADO database

  1. #1

    Thread Starter
    PowerPoster boops boops's Avatar
    Join Date
    Nov 2008
    Location
    Holland/France
    Posts
    3,201

    Resolved [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

  2. #2
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,042

    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.

  3. #3

    Thread Starter
    PowerPoster boops boops's Avatar
    Join Date
    Nov 2008
    Location
    Holland/France
    Posts
    3,201

    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

  4. #4
    PowerPoster ChrisE's Avatar
    Join Date
    Jun 2017
    Location
    Frankfurt
    Posts
    3,042

    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.

  5. #5

    Thread Starter
    PowerPoster boops boops's Avatar
    Join Date
    Nov 2008
    Location
    Holland/France
    Posts
    3,201

    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

  6. #6
    Lively Member
    Join Date
    Jan 2020
    Posts
    120

    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
  •  



Click Here to Expand Forum to Full Width