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,963

    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
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,299

    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
    I'm about to be a PowerPoster! Hack's Avatar
    Join Date
    Aug 2001
    Location
    Searching for mendhak
    Posts
    58,333

    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.)

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

    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,495

    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,963

    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
  •  



Click Here to Expand Forum to Full Width