asked on

Access VBA change SQL data type

Hi

I am using the folllowing Access VBA code to create tables in a SQL database
Is it possible to use similar code to change one of the data types.
My nvarchar(100) should be nvarchar(2000)

Thanks

Public Sub Drop_SQL_Relationships(ByVal sTable As String, ByVal sRelationship_Name As String)

    
    On Error GoTo EH
    
    Set con = New ADODB.Connection
    
    con.Open "Provider=SQLOLEDB;Data Source=196.220.43.247,1444;Network Library=DBMSSOCN;Initial Catalog=psql;User ID=Gxxxx;Password=mcc4;"
   
    Set cmd = New ADODB.Command

    Dim sSQL As String

    sSQL = "ALTER TABLE [" & sTable & "] DROP CONSTRAINT " & sRelationship_Name

    
    With cmd
        .CommandText = sSQL
        .CommandType = adCmdText
        .ActiveConnection = con
        .Execute
    End With
    
    
    Set cmd = Nothing
    Set con = Nothing
    
    Exit Sub
    
EH:
    
    MsgBox Err.Description
    
End Sub

Open in new window

mbizup

Give this a try for the SQL involved:

sSql = "ALTER TABLE " & YourTableName & " ALTER COLUMN " & YourFieldName & " nvarchar(2000);" 
' etc

Open in new window

ASKER CERTIFIED SOLUTION

mbizup

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mbizup

That said, I'm curious why you would want to change the field size through code?

Field size seems like something you would manually set or increase once or very rarely as needed, not dynamically through code (which would be appropriate if you were changing it frequently).

Murray Brown

ASKER

It is an online database and their is no manual facility to change the data type

mbizup

Ok - go ahead and give the code a shot...

SOLUTION

datAdrenaline

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Murray Brown

ASKER

thank you both