none
why the error occured when I append the new table in DAO database using vb.net code RRS feed

  • Question

  •  I want to append a new dao.table in dao.database , i take the name of table from textbox my code is:

    Dim db As Microsoft.Office.Interop.Access.Dao.Database

    Dim tdf As Microsoft.Office.Interop.Access.Dao.TableDef

    tdf = db.CreateTableDef(TextBox1.Text)
    With tdf
          .Fields.Append(.CreateField("accunt", DbType.String))
          .Fields.Append(.CreateField("kind_name", DbType.String))
          .Fields.Append(.CreateField("thickness", DbType.String))
          .Fields.Append(.CreateField("unit", DbType.String))
          .Fields.Append(.CreateField("kind_q", DbType.Int64))
          .Fields.Append(.CreateField("price_u", DbType.Currency))
          .Fields.Append(.CreateField("price_t", DbType.Currency))
          .Fields.Append(.CreateField("dist", DbType.String))
          .Fields.Append(.CreateField("idate", DbType.Date))
          .Fields.Append(.CreateField("per_num", DbType.Int64))
          .Fields.Append(.CreateField("invoice", DbType.Int64))
          .Fields.Append(.CreateField("incoming_num", DbType.Int64))
          .Fields.Append(.CreateField("note", DbType.String))
          .Fields.Append(.CreateField("type", DbType.String))
    End With
    db.TableDefs.Append(tdf.Name) <------------------the error message---------------------- the operation is not supported for this object
    db.TableDefs.Refresh()

    when i run my program an error message appear

    please, some body help me because I don't know why this error occured

    thanks

    Monday, August 27, 2012 6:28 AM

Answers

  • I know when creating a new DAO.field i must use its datatype dbtext or dbdate or etc.

    these datatype don't declared in visual basic 2010 express because an error occurred 

     but I searched in visual basic 2010 express DAO.datatype Enum until I reached to the correct way,  its like the code below

    tdf.Fields.Append(tdf.CreateField("accunt", Access.Dao.DataTypeEnum.dbText))

    Friday, August 31, 2012 2:28 AM

All replies

  • 
    Set db = CurrentDb
    Set tdf = db.CreateTableDef("ll21ll")
    With tdf
          .Fields.Append .CreateField("accunt", dbText)
          .Fields.Append .CreateField("type", dbText)
    End With
    db.TableDefs.Append tdf
    db.TableDefs.Refresh


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Monday, August 27, 2012 7:39 AM
    Answerer
  • Thank you for replying my question.

    But this way occur an error because I use visual basic 2010 express and my database has password (Protected database)

    so, I use opendatabase method like that

    db = de.OpenDatabase("D:\Stores\Ali_Stores\Stores.accdb", False, False, ";PWD=" & str)

    where de as  Microsoft.Office.Interop.Access.Dao.DBengine

    Is there another way?



    Monday, August 27, 2012 9:00 AM
  • The problem in your code was:

    You did not specify any database

    The datatype are not supported

    But if you can set db by the method you speciefied.I used currentdb for simplification.

    I think you are only defining a table def which is reasonably perfect.

    But there is another option by SQL CREATE TABLE statement.I do not suggest this because this will give less freedom than tabledefs


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Monday, August 27, 2012 9:39 AM
    Answerer
  • how can I specify my database?
    Monday, August 27, 2012 6:20 PM
  • You have already done that in 2nd post by

    db = de.OpenDatabase("D:\Stores\Ali_Stores\Stores.accdb", False, False, ";PWD=" & str)

    And I used below:

    Set db = CurrentDb



    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Tuesday, August 28, 2012 5:59 AM
    Answerer
  • I am using visual basic 2010 express, I can't use currentDB

    I want  to append the new table to my database but the append method occured error why ,I don't know

    please, help me

    Tuesday, August 28, 2012 10:47 AM
  • Dear VB_Princess.

    1. In DAO you have to specify a database where all the table will be referred.In your first code no such statement was there.

    You use below which is fine.Before createTabledef insert below

    db = de.OpenDatabase("D:\Stores\Ali_Stores\Stores.accdb", False, False, ";PWD=" & str)

    tdf = db.CreateTableDef(TextBox1.Text)

    2.db.TableDefs.Append(tdf.Name)  - See the help files Append method needs a tabledef object.Use below.

    db.TableDefs.Append (tdf)

    Other statements are fine.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Tuesday, August 28, 2012 11:22 AM
    Answerer
  • I wrote the code

    db.TableDefs.Append (tdf)

    another error occurred:-------------------------valid data type what shall i do?

    and thank you very much

     
    Wednesday, August 29, 2012 10:06 AM
  •       .Fields.Append(.CreateField("unit", DbType.String))

    See in the above case you used a datatype of DbType.String or DbTypeInt64 which is probably not supported in DAO.

    In Access -Go to VBE by Alt+F11 and press F2 for object browser.In search box type DataTypenum ,you will get a list of supported datatype.Then you have to search which is appropriate for you.

    And use like (Don't prefix DbType)

     .Fields.Append(.CreateField("idate", DbDate))

    All the best.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Wednesday, August 29, 2012 12:10 PM
    Answerer
  • I know when creating a new DAO.field i must use its datatype dbtext or dbdate or etc.

    these datatype don't declared in visual basic 2010 express because an error occurred 

     but I searched in visual basic 2010 express DAO.datatype Enum until I reached to the correct way,  its like the code below

    tdf.Fields.Append(tdf.CreateField("accunt", Access.Dao.DataTypeEnum.dbText))

    Friday, August 31, 2012 2:28 AM
  • It is good to see that your issue solved.

    Pls try to always mark answer the post which solved your issue.

    All the best.


    Best Regards,
    Asadulla Javed, Kolkata
    ---------------------------------------------------------------------------------------------
    Please do not forget to click “Vote as Helpful” if any post helps you and "Mark as Answer”if it solves the issue.

    Friday, August 31, 2012 6:15 AM
    Answerer