How To Add Coulmn in table MS Access database

How To Add Coulmn in table MS Access database

• BMONSTER

  Goodness,

  I've been working on such a project for a month and this is different from anything I've been told and I've written entire ADOX classes around what I've been told.

  Let us hope that Blair strolls by and reads this in between negations of VB so he can comment, not to question you, but to find out why I've written all this ADOX code

  
  
  

• IvanP

  
  Actually it might work for both.

  The Jet database engine also supports DML and DDL so in most instances you don't need ADOX or DAO.

  Fundamental Microsoft Jet SQL for Access 2000
  Intermediate Microsoft Jet SQL for Access 2000
  Advanced Microsoft Jet SQL for Access 2000

  
  
  

• Latso

  "ADOR and ADOX are extensions of ADO. The only reason I can think of not to use the most recent Data access technology (ADO.NET) is if you are woking directly inside of Access were DAO is native."

  Dman,

  This was interesting.

  What I am working on IS ADO.NET accept for table Creation/Persistence and is only intended to local Access databases.

  Are you saying that DAO would be better

  
  
  

• Chris Clements

   

  Paul,

  I don't mean to question you, that's not my purpose.

  When we're all writing something, good solid answers are most helpful. I've been learning ADO.NET and Access. I'm actually writing something that I hope will help moderators help the community.

  I think I've gotten this corner of ADO.Net down somwhat. In this project, in order to do table creation and to persist that table in an Access database, I was told that ADOX was the way to go and I've written quite a bit of code around that.

  If there's a better way, that's always helpful.

  
  
  

• Orf Quarenghi

  Is that for an Access database Or is it for an SQL database

  
  
  

• Mark Tomlinson - MSFT

  Thanks for All,

  We are always looking for better, So we win always

  
  
  

• flinxperbil

  From what I've been able to tell, all of the databinding and other objects all get converted down to SQL instructions for you, so that's what I've been using all along.

  There are some differences in how MS Access vs. SQL Server vs. Oracle all handle some SQL syntax. For example, MS Access requires parens around multiple JOINs but SQL Sever/Oracle does not. But, for the most part, the syntax is similar enough that it's fairly easy to wrap it in a class if you need to support multiple platforms, and IMHO far simpler than ADOX.

  
  

• Frango

  You can do that With ADOX.

  'note this class depends upon two COM MDAC v2.8 References

  ' made in Project References

  ' Microsoft ActiveX Data Objects 2.8 Library

  ' Microft ADO V2.8 for DDL and Security

  Protected Const cNoConn As String = "No Connection has been Opened."

  Public Connection As ADODB.Connection

  Public Catalogue As ADOX.Catalog

  Public Table As New ADOX.Table

  Protected m_databaseOpen As Boolean = False

  Public Sub New()

  Connection = New ADODB.Connection

  Catalogue = New ADOX.Catalog

  Table = New ADOX.Table

  m_databaseOpen = False

  Dim v As New ADOX.Table

  End Sub

  Public Function OpenDB() As Boolean

  OpenDB = False

  Dim connectionstring As String = GetConnectionString()

  If Me.ConnectionIsOpen Then

  MsgBox("OpenDB - Failed to Establish Connection", MsgBoxStyle.Exclamation, "Connection State is Open")

  Exit Function

  End If

  Try

  Connection.Open(connectionstring)

  Catalogue.ActiveConnection = Connection

  OpenDB = True

  m_databaseOpen = True

  Catch e As Exception

  Dim a = connectionstring.Substring(connectionstring.IndexOf(""""))

  MsgBox("OpenDB - Failed to open File: " & a, MsgBoxStyle.Exclamation, e.Message)

  End Try

  End Function

  Public Function SelectTable(ByVal TableName As String) As Boolean

  SelectTable = False

  For i As Short = 0 To Catalogue.Tables.Count - 1

  If TableName = Catalogue.Tables.Item(i).Name Then

  Table = Catalogue.Tables.Item(i)

  Return True

  End If

  Next

  End Function

  Public Sub CreateColumn(ByVal ColumnName As String, ByVal Datatype As ADOX.DataTypeEnum, Optional ByVal Size As Integer = 0)

  'This routine creates a column described in the Table object so it assumes it is set.

  'One method of setting it is to call Select Table after opening the database

  If Not Me.ConnectionIsOpen Then

  MsgBox("CreateColumn - Failed to Create Column : " & ColumnName, MsgBoxStyle.Exclamation, cNoConn)

  Exit Sub

  End If

  Dim col As New ADOX.Column

  col.Name = ColumnName

  Try

  col.Type = Datatype

  Catch e As Exception

  MsgBox("CreateColumb - Failed to Create Column : " & ColumnName, MsgBoxStyle.Exclamation, e.Message)

  col = Nothing

  Exit Sub

  End Try

  If Size <> 0 Then col.DefinedSize = Size

  Try

  Table.Columns.Append(ColumnName, Datatype)

  Catch e As Exception

  If Err.Number() <> 0 Then

  MsgBox(Err.Source & "-->" & Err.Description, , "Error")

  End If

  MsgBox("CreateColumb - Failed to Append Column : " & ColumnName, MsgBoxStyle.Exclamation, e.Message)

  End Try

  col = Nothing

  End Sub

  Public Sub DeleteColumb(ByVal ColumnName As String)

  If Not Me.ConnectionIsOpen Then

  MsgBox("DeleteColumn - Failed to Delete Column : " & ColumnName, MsgBoxStyle.Exclamation, cNoConn)

  Exit Sub

  End If

  For i As Short = 0 To Table.Columns.Count

  If Table.Columns.Item(i).Name = ColumnName Then

  Try

  Table.Columns.Delete(i)

  Catch e As Exception

  MsgBox("DeleteColumb - Unable to delete Column: " & ColumnName, MsgBoxStyle.Exclamation, e.Message)

  End Try

  Exit For

  End If

  Next

  End Sub

  Public Function DeleteTable(ByVal Table As DataTable) As Boolean

  DeleteTable = False

  For i As Short = 0 To Catalogue.Tables.Count - 1

  If Catalogue.Tables(i).Name.ToLower = Table.TableName.ToLower Then

  Catalogue.Tables.Delete(i)

  Return True

  End If

  Next

  End Function

  End class

  
  
  

• Render

  ReneeC wrote:

  Goodness, I've been working on such a project for a month and this is different from anything I've been told and I've written entire ADOX classes around what I've been told. Let us hope that Blair strolls by and reads this in between negations of VB so he can comment, not to question you, but to find out why I've written all this ADOX code

  I sent you those links when you started!!!
  
  

• Quirk

  RDO, DAO, and ADO are all *forefathers*/precursors to ADO.NET.

  ADOR and ADOX are extensions of ADO. The only reason I can think of not to use the most recent Data access technology (ADO.NET) is if you are woking directly inside of Access were DAO is native.

  Just my 2 cents worth...

  
  
  

• Luke B

  ReneeC wrote:

  What I am working on IS ADO.NET accept for table Creation/Persistence and is only intended to local Access databases. Are you saying that DAO would be better

  Jumping in here again...The primary advantages for using DAO are performance and full support of Access database features. Otherwise, it's a dated, clunky object library that requires interop to work with .NET. I personally don't use it unless absolutely necessary.

  My recommendation is to use the Jet OLEDB Provider with the native .NET OLEDB Library and only use interop with DAO and ADO/ADOX when you absolutely require a feature that is not supported through ADO.NET.

  One additional tip...avoid using the old MS Access ODBC driver. That is unless you need to perform SQL queries between two different Jet databases.

  
  
  

• Mr pinchy

  from your post a few months ago

  hey renee.  .. In .Net 1.1, the easiest was to interop ADOX. see: HOW TO: Create a Microsoft Access Database Using ADOX and Visual Basic .NET and HOWTO: Create a Table with Primary Key Through ADOX. You can also execute Jet DDL against any jet database. Begin here. Just remember to routinely compact your access databases!!! There may be an easier way in ADO.NET 2.0, but I don't know.

  see the link "Begin here"

  but then again. . . you never listen to me

  
  
  

• bennykfc

  ReneeC wrote:

  Goodness, I've been working on such a project for a month and this is different from anything I've been told and I've written entire ADOX classes around what I've been told. Let us hope that Blair strolls by and reads this in between negations of VB so he can comment, not to question you, but to find out why I've written all this ADOX code

  Hey it's OK to question me. I don't always have the best (or correct) answer.

  I've written quite a bit of ADOX and DAO code as well, and if I remember correctly there are some features that are not supported by Jet DML/DDL (such as creating a Jet database container).

  In any event, I have the benefit of working with both Visual Basic and Access since versions 2.0 and 1.1 respectively so I've been exposed to quite a few different Microsoft data access technologies.

  
  
  

• Kerry Wano

  I found this

  conn = New OleDbConnection(connStr)

  conn.Open()

  Dim sql As String = "ALTER TABLE CustomerInfo ADD COLUMN CustomerFamilyName string,CustomerFirstName string "

  Dim cmd As OleDbCommand = conn.CreateCommand()

  cmd.CommandText = sql

  cmd.ExecuteNonQuery()

  I think, it does the same !!! because I test it.

  Do you think that also orthere any problem

  
  
  
• How To Add Coulmn in table MS Access database