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

Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAsked:
Who is Participating?
 
mbizupConnect With a Mentor Commented:
That assumes you have variables for YourTableName and YourFieldName.

To hardcode it for a specific column/table:

sSql = "ALTER TABLE tblMyTableName  ALTER COLUMN MyFieldName nvarchar(2000);"
0
 
mbizupCommented:
Give this a try for  the SQL involved:

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

Open in new window

0
 
mbizupCommented:
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).
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
It is an online database and their is no manual facility to change the data type
0
 
mbizupCommented:
Ok - go ahead and give the code a shot...
0
 
datAdrenalineConnect With a Mentor Commented:
>> It is an online database and their is no manual facility to change the data type <<

Have you ever tried to use an Access Data Project file?  They connect directly to a SQL Server database, thus giving you the ability to make many changes to the schema of the database.  Its actually quite handy.  Also, IIRC, you can download/install SQL Server Management Studio and connect to your database to manage the schema.

In either case you will need to information you use to connect via the ADODB connection, namely the IP Address and Port.
0
 
Murray BrownMicrosoft Cloud Azure/Excel Solution DeveloperAuthor Commented:
thank you both
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.