Solved

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

Posted on 2009-03-31
24
274 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 119

Expert Comment

by:Rey Obrero
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
 

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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

762 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now