dcsimg
Results 1 to 11 of 11

Thread: Add table to database

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Mar 2009
    Posts
    166

    Add table to database

    Hey,

    I have database called 'BCN.mdb'

    the database includes a table called "BCN_Table"

    I want to add a table while program running with DAL

    something like:

    Code:
    Dim selectCommand As String = "add table " & TableName & " to file " & FileName
            Try
                Dim dataAdapter As New OleDbDataAdapter(selectCommand, oleDbConnection)
                Dim dataSet As New DataSet
    
                dataAdapter.Fill(dataSet)
    
                Return dataSet
    
            Catch
                Throw
            End Try

    thank you all

  2. #2
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,966

    Re: Add table to database

    For a SQL Server Database, you can use the following:

    http://www.tizag.com/sqlTutorial/sqlcreate.php

    As for whether that will work on an Access Database, I am really not sure.

    Gary

  3. #3
    Addicted Member adamlonsdale's Avatar
    Join Date
    Oct 2005
    Posts
    210

    Re: Add table to database

    Something like this (Although this used ADOX?)

    Code:
            Dim ADOXtable As New ADOX.Table
            Dim ADOXindex As New ADOX.Index
            ADOXcatalog.Create("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FILEPATH;Jet OLEDB:Database Password=PASSWORD")
            ADOXcatalog.ActiveConnection = ("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=FILEPATH;Jet OLEDB:Database Password=PASSWORD")
            ADOXtable.Name = "tblName"
            ADOXtable.Columns.Append("ID", ADOX.DataTypeEnum.adInteger)
            ADOXtable.Columns.Append("field2", ADOX.DataTypeEnum.adVarWChar)
            ADOXtable.Columns.Append(field3", ADOX.DataTypeEnum.adVarWChar)
            ADOXcatalog.Tables.Append(ADOXtable)
            ADOXindex.Name = "IDIndex" 'name of index
            ADOXindex.Columns.Append("ID")
            ADOXtable.Indexes.Append(ADOXindex)

  4. #4
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    103,712

    Re: Add table to database

    Why exactly do you want to add the table in the first place? Adding tables from applications is not unheard of but it's not a common thing to do. Most people I've seen post about it were doing the wrong thing, so I'm interested to know your reason.

  5. #5
    Super Moderator Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,335

    Re: Add table to database

    Quote Originally Posted by gep13 View Post
    As for whether that will work on an Access Database, I am really not sure.
    CREATE TABLE works against any database that supports some form of SQL (and, IMO, is both the easiest and best, way to create a new table programmtically.)
    Please use [Code]your code goes in here[/Code] tags when posting code.
    When you have received an answer to your question, please mark it as resolved using the Thread Tools menu.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    I dont answer coding questions via PM or EMail. Please post a thread in the appropriate forum section.

    Creating A Wizard In VB.NET
    Paging A Recordset
    What is wrong with using On Error Resume Next
    Good Article: Language Enhancements In Visual Basic 2010
    Upgrading VB6 Code To VB.NET
    Microsoft MVP 2005/2006/2007/2008/2009/2010/2011/2012/Defrocked

  6. #6
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,966

    Re: Add table to database

    Good to know, thanks Hack.

    It has been ages since I have done anything with Access, and I wasn't sure

    Gary

  7. #7

    Thread Starter
    Addicted Member
    Join Date
    Mar 2009
    Posts
    166

    Re: Add table to database

    What do you (all of you guys) say about this?

    Code:
    Public Sub AddTableToDatabase(ByVal FileName As String, ByVal dataTable As DataTable)
            Dim Cmd As OleDbCommand
            Dim objCmd As New OleDbCommand
            Dim selectCmd As String = "CREATE TABLE " & dataTable.TableName & " (" & MakeNewTableSQLString(dataTable) & ")" '"[Id] COUNTER, [Stock Code] TEXT(10), [Account Number] TEXT(6))"
    
            Dim Con = New OleDbConnection("Provider=Microsoft.Jet.OleDb.4.0;data source=" & FileName)
    
            Cmd = New OleDbCommand(selectCmd, Con)
    
            Con.Open()
            objCmd = New OleDbCommand(selectCmd, Con)
            objCmd.ExecuteNonQuery()
            Con.Close()
        End Sub

  8. #8

  9. #9
    A SQL Server fool GaryMazzone's Avatar
    Join Date
    Aug 2005
    Location
    Dover,NH
    Posts
    7,377

    Re: Add table to database

    Since Counter is not a valid data type I would say no it does not work.
    Sometimes the Programmer
    Sometimes the DBA

    Mazz1

  10. #10

    Thread Starter
    Addicted Member
    Join Date
    Mar 2009
    Posts
    166

    Re: Add table to database

    It is working!

  11. #11
    ASP.NET Moderator gep13's Avatar
    Join Date
    Nov 2004
    Location
    The Granite City
    Posts
    21,966

    Re: Add table to database

    Gary,

    It would seem like for an Access Database, Counter is a valid DataType:

    http://msdn.microsoft.com/en-us/libr...40(VS.85).aspx

    Gary

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width