?
Solved

Access VBA change SQL data type

Posted on 2011-09-16
7
Medium Priority
?
420 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:Murray Brown
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 1400 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:Murray Brown
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 600 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:Murray Brown
ID: 36899978
thank you both
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

777 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