By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,705 Members | 1,815 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,705 IT Pros & Developers. It's quick & easy.

How to: change the Column type in exsiting DB via code.

P: 1
Hi
I use vb.Net to open an access db for my software and i have one issue that i don't know how to do, I need to change an exsiting Column that might have some records in it from Text(250) to Memo type cuz i need to store some text that its length is more then 250.

Is there any way that i can do something like:
Expand|Select|Wrap|Line Numbers
  1. Alter Table TABLENAME {Alter Column COLUMN1 Memo}
and to change the column type from text to memo without delete the records?
Thanks...
Nov 26 '06 #1
Share this Question
Share on Google+
1 Reply


PEB
Expert 100+
P: 1,418
PEB
Here VB exemple how u can add or remove column:


VB help
Sub CreateTableDefX()

Dim dbsNorthwind As Database
Dim tdfNew As TableDef
Dim prpLoop As Property

Set dbsNorthwind = OpenDatabase("Northwind.mdb")

' Create a new TableDef object.
Set tdfNew = dbsNorthwind.CreateTableDef("Contacts")

With tdfNew
' Create fields and append them to the new TableDef
' object. This must be done before appending the
' TableDef object to the TableDefs collection of the
' Northwind database.
.Fields.Append .CreateField("FirstName", dbText)
.Fields.Append .CreateField("LastName", dbText)
.Fields.Append .CreateField("Phone", dbText)
.Fields.Append .CreateField("Notes", dbMemo)

Debug.Print "Properties of new TableDef object " & _
"before appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop <> "" Then Debug.Print " " & _
prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop

' Append the new TableDef object to the Northwind
' database.
dbsNorthwind.TableDefs.Append tdfNew

Debug.Print "Properties of new TableDef object " & _
"after appending to collection:"

' Enumerate Properties collection of new TableDef
' object.
For Each prpLoop In .Properties
On Error Resume Next
If prpLoop <> "" Then Debug.Print " " & _
prpLoop.Name & " = " & prpLoop
On Error GoTo 0
Next prpLoop

End With

' Delete new TableDef object since this is a
' demonstration.
dbsNorthwind.TableDefs.Delete "Contacts"

dbsNorthwind.Close

End Sub
But just change the property in dao isn't available

in ADO you can use:


VB help
Public Sub AttributesX

Dim cnn1 As ADODB.Connection
Dim rstEmployees As ADODB.Recordset
Dim fldLoop As ADODB.Field
Dim proLoop As ADODB.Property
Dim strCnn As String

' Open connection and recordset.
strCnn = "Provider=sqloledb;" & _
"Data Source=srv;Initial Catalog=Pubs;User Id=sa;Password=; "
Set cnn1 = New ADODB.Connection
cnn1.Open strCnn
Set rstEmployees = New ADODB.Recordset
rstEmployees.Open "employee", cnn1, , , adCmdTable

' Display the attributes of the connection.
Debug.Print "Connection attributes = " & _
cnn1.Attributes

' Display the attributes of the Employee table's
' fields.
Debug.Print "Field attributes:"
For Each fldLoop In rstEmployees.Fields
Debug.Print " " & fldLoop.Name & " = " & _
fldLoop.Attributes
Next fldLoop

' Display Fields of the Employee table which are NULLABLE.
Debug.Print "NULLABLE Fields:"
For Each fldLoop In rstEmployees.Fields
If CBool(fldLoop.Attributes And adFldIsNullable) Then
Debug.Print " " & fldLoop.Name
End If
Next fldLoop

' Display the attributes of the Employee table's
' properties.
Debug.Print "Property attributes:"
For Each proLoop In rstEmployees.Properties
Debug.Print " " & proLoop.Name & " = " & _
proLoop.Attributes
Next proLoop

rstEmployees.Close
cnn1.Close

End Sub
Attributes method...

If you change an attribute and than you update your table the filed should be changed! Never tried!
Nov 26 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.