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

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

0
Murray Brown
Asked:
Murray Brown
  • 4
  • 2
2 Solutions
 
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 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:
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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
 
datAdrenalineCommented:
>> 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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now