Solved

Access VBA change SQL data type

Posted on 2011-09-16
7
376 Views
Last Modified: 2012-05-12
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
Comment
Question by:murbro
  • 4
  • 2
7 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 36548479
Give this a try for  the SQL involved:

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

Open in new window

0
 
LVL 61

Accepted Solution

by:
mbizup earned 350 total points
ID: 36548486
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
 
LVL 61

Expert Comment

by:mbizup
ID: 36548558
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:murbro
ID: 36548790
It is an online database and their is no manual facility to change the data type
0
 
LVL 61

Expert Comment

by:mbizup
ID: 36548824
Ok - go ahead and give the code a shot...
0
 
LVL 11

Assisted Solution

by:datAdrenaline
datAdrenaline earned 150 total points
ID: 36549796
>> 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
 

Author Closing Comment

by:murbro
ID: 36899978
thank you both
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

863 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

26 Experts available now in Live!

Get 1:1 Help Now