• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

How do I change MS Access FIELD DATA TYPE from Long to Text?

I have a database that has a FIELD called [USER] and it is set to DATA TYPE of LONG. How do I programmatically look through every table to search for [USER], see if it is set to LONG and change it to TEXT?

I'm workingin Visual Basic 6
0
Taylor814
Asked:
Taylor814
  • 10
  • 10
  • 3
  • +1
1 Solution
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
try this

Public Function aaTest() As Boolean
 
    CurrentDb.Execute "ALTER TABLE YourTableName ALTER COLUMN [USER] Text"


End Function

mx
0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Sorry ... I missed the 'every table' part ..

mx
0
 
Rey Obrero (Capricorn1)Commented:

Sub ChangedFieldType(sFld As String)
Dim td As TableDef, db As DAO.Database, fld As DAO.Field
Set db = CurrentDb
For Each td In db.TableDefs
    For Each fld In td.Fields
        If fld.Name = sFld Then
            If fld.Type = dbLong Then
                CurrentDb.Execute _
                "alter table [" & td.Name & "] alter column " & sFld & " Text(25)"
            End If
        End If
    Next
Next


End Sub

to use

call ChangedFieldType("User")

0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Taylor814Author Commented:
I'm using ADO not DAO
0
 
rockiroadsCommented:
Since your using VB6, I think you can use ADOX

Here is a simple example of changing a type using sql. For some reason, it doesnt let me change the type using adox

    Set adoConn = New ADODB.Connection
    adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\mydb.mdb'"
    sSql = "alter table mytable alter column mytable text"
    adoConn.Execute sSql

Now to iterate thru tables, we can use ADOX
add adox as reference or define as object then create it as object
eg

    Dim tbl As ADOX.Table, cat As ADOX.Catalog, col As ADOX.Column
   
    Set adoConn = New ADODB.Connection
    adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='C:\mydb.mdb'"
    Set cat = New ADOX.Catalog
    cat.ActiveConnection = adoConn
    For Each tbl In cat.Tables
        For Each col In tbl.Columns
            Debug.Print col.Name
        Next col
    Next tbl

A little tweaking needs to be done when iterating thru tables to handle non user tables.

I will merge this to get your solution but just throwing you one solution that can be done in vb6.

Right now, I have to go out, when I get back, I will sort it out for ya
0
 
rockiroadsCommented:
here u go, remember to change path to your access db


Public Sub UpdateTables()

    Dim adoConn As Object
    Dim cat As Object, tbl As Object, col As Object
    Dim sSql As String
   
    Set adoConn = CreateObject("ADODB.Connection")
   
    adoConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\mydb.mdb"
   
    Set cat = CreateObject("ADOX.Catalog")

    cat.ActiveConnection = adoConn
    For Each tbl In cat.Tables
        If Left$(tbl.Name, 4) <> "MSys" Then
            For Each col In tbl.Columns
                If col.Name = "MyLongField" Then
                    Debug.Print "Changing Table", tbl.Name
                    sSql = "alter table " & tbl.Name & " alter column " & col.Name & " string"
                    adoConn.Execute sSql
                End If
            Next col
        End If
    Next tbl

    Set cat = Nothing
    adoConn.Close
    Set adoConn = Nothing

End Sub

0
 
rockiroadsCommented:
Oh the example above uses MyLongField. Obviously you need to change this to use your column which is USER
0
 
rockiroadsCommented:
Oh, one final thing. I forgot to handle spaces and the name user. So change the sql statement to have [] round it
eg

sSql = "alter table [" & tbl.Name & "] alter column [" & col.Name & "] string"


0
 
Taylor814Author Commented:
rockiroads: - I get error 91 when I get to:

adoConn.Execute sSql
0
 
Taylor814Author Commented:
And just to be clear, I don't want to change the column name, I want to change its data type to Text.
0
 
rockiroadsCommented:
the sql given just changes the datatype, not the name

Error 91, object or variable not set

If you run this but just comment out the call adoConn.execute, leaving just the debug.print statement, do you see anything appearing in the immediate window?

0
 
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Taylor814 ... just noting that in the future, it would be good to mention that you are using ADO.

mx
0
 
Taylor814Author Commented:
I have this

alter table [Comment] alter column [User] string
Changing Table              Comment
0
 
Taylor814Author Commented:
I did a debug.print for ssql which is what you're seeing
0
 
rockiroadsCommented:
right, well it seems there is nothing wrong with the adoConn object if we can set the activeconnection.

Have u got option explicit in the top of your code module? do a compile also, make sure there arent any unexpected typo's.
I cant see how you are getting error 91
0
 
Taylor814Author Commented:
If it helps, I had to take out some of your code because I'm already connected to a database. Here's what I have:


'This procedure is used to make connection to the database
Public Function MakeConnection() As Boolean
If MyConn.state = 0 Then
    On Error GoTo ErrMakeConnection
        With MyConn
            'mention the data provider
            .Provider = "Microsoft.Jet.OLEDB.4.0"
            'mention the type of cursorlocation
            .CursorLocation = adUseClient
            'provide the location where the database exists
            .Properties("Data Source") = App.Path & "\db\db.mdb"
            'For secured database we need to set-up the password property
            .open
        End With
        Exit Function
ErrMakeConnection:
     MsgBox Err.Number & " " & Err.description
End If
End Function
 
 
Public Sub UpdateTables()
 
   MakeConnection
 
    Dim cat As Object, tbl As Object, col As Object
    Dim sSql As String
   
    
   
    
   
    Set cat = CreateObject("ADOX.Catalog")
 
    cat.ActiveConnection = MyConn
    For Each tbl In cat.Tables
        Debug.Print tbl.Name
        If Left$(tbl.Name, 4) <> "MSys" Then
            For Each col In tbl.Columns
                If col.Name = "User" Then
                    Debug.Print "Changing Table", tbl.Name
                    sSql = "alter table [" & tbl.Name & "] alter column [" & col.Name & "] string"
                    adoConn.Execute sSql
                End If
            Next col
        End If
    Next tbl
 
 
End Sub '

Open in new window

0
 
rockiroadsCommented:
since your using MyConn and not adoConn, you need to use MyConn.execute

I recommend you put in

option explicit

in the top of your code module then compile. It will pick up on these kinds of errors
0
 
rockiroadsCommented:
adoConn is a variable not initialised which is why you get error code 91
0
 
Taylor814Author Commented:
Oh geez, how could I have missed that.
0
 
Taylor814Author Commented:
Would there be a piece of code I can put in there to make sure the field isn't already string?
0
 
rockiroadsCommented:
you mean option explicit?

first line in your code module
eg

option explicit


... rest of your code
0
 
Taylor814Author Commented:
No, I mean, check the field in the table to make sure it's not already of datatype text. If it is, then it'll skip it. This code is code that will have to run when the program first opens up to change a database that already exists. It should try to do this code every time the program is opened up.
0
 
rockiroadsCommented:
check the type as part of the name check

If col.Name = "User" and col.type <> 202 Then


202 represents adVarwChar which is the text field

0
 
Taylor814Author Commented:
Thank you for your help!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 10
  • 10
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now