Solved

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

Posted on 2009-03-31
24
277 Views
Last Modified: 2012-05-06
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
Comment
Question by:Taylor814
  • 10
  • 10
  • 3
  • +1
24 Comments
 
LVL 75
ID: 24035741
try this

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


End Function

mx
0
 
LVL 75
ID: 24035748
Sorry ... I missed the 'every table' part ..

mx
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 24035750

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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Taylor814
ID: 24036789
I'm using ADO not DAO
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24037275
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
 
LVL 65

Accepted Solution

by:
rockiroads earned 500 total points
ID: 24038022
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24038028
Oh the example above uses MyLongField. Obviously you need to change this to use your column which is USER
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24038035
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
 

Author Comment

by:Taylor814
ID: 24041027
rockiroads: - I get error 91 when I get to:

adoConn.Execute sSql
0
 

Author Comment

by:Taylor814
ID: 24041031
And just to be clear, I don't want to change the column name, I want to change its data type to Text.
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24041285
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
 
LVL 75
ID: 24041326
Taylor814 ... just noting that in the future, it would be good to mention that you are using ADO.

mx
0
 

Author Comment

by:Taylor814
ID: 24041364
I have this

alter table [Comment] alter column [User] string
Changing Table              Comment
0
 

Author Comment

by:Taylor814
ID: 24041366
I did a debug.print for ssql which is what you're seeing
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24041410
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
 

Author Comment

by:Taylor814
ID: 24041423
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24041495
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24041505
adoConn is a variable not initialised which is why you get error code 91
0
 

Author Comment

by:Taylor814
ID: 24041560
Oh geez, how could I have missed that.
0
 

Author Comment

by:Taylor814
ID: 24041578
Would there be a piece of code I can put in there to make sure the field isn't already string?
0
 
LVL 65

Expert Comment

by:rockiroads
ID: 24041598
you mean option explicit?

first line in your code module
eg

option explicit


... rest of your code
0
 

Author Comment

by:Taylor814
ID: 24041620
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
 
LVL 65

Expert Comment

by:rockiroads
ID: 24042469
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
 

Author Closing Comment

by:Taylor814
ID: 31565149
Thank you for your help!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

733 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question