ADOX - design changes in both Access & SQL Server

Posted on 2001-06-28
Last Modified: 2013-11-27
As my application goes thru revisions I need to make changes in the database structure.  Originally the application used Access97 and these changes were accomplished with DAO methods.

Now the application has been changed to ADO to support Access and SQL Server.  I have been attempting to do an upgrade with ADOX.  I need to change a text field size from 50 to 200.  I couldn't come up with a way to do this to the original column so I created a new column and copied the data.  Since there is code that references the columns by index I needed to recreate all columns after this text field.  I was almost successful however I hit a snag with the dynamic property "Default".  I could not get this property to accept the value from the original column.  Other properties (built-in & dynamic) worked fine.  I also had problems with the foreign keys but have not researched that as much.  It seems that this is a long way around for a simple change.

First is creating a new field the only way to change the size?

If not is there a way to reorder the columns so I don't have to recreate all columns after the changed colums?

I have already reviewed the ADOX examples in MSDN including the DefinedSize Property Example without sheding any light on this problem.
Question by:Argonaut
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
  • 3
  • 2
  • 2
  • +1
LVL 11

Expert Comment

ID: 6236911
Can't you just insert a new column after your other columns, and then change the order in your query?
LVL 43

Expert Comment

ID: 6237145
Why don't you do it just using ADO and send it the Alter Table instruction:

Connection.Execute "ALTER TABLE MyTable ALTER [MyFieldName] VarChar(200)"

Will adjust the field size of the specified field without having to create a new one and copy the data, the index position will also remain the same.

Expert Comment

ID: 6237349
I found out that MDAC version 2.5 an up has a bug preventing the change of column default values. Use DDL instead.

I created a small helper function for this pupose:

--- code starts here ---

Public Sub SetColumnDefault(ByVal TableName As String, ByVal ColumnName As String, _
                            Optional ByVal DefaultValue As String = vbNullString, _
                            Optional Drop As Boolean = False)
    ' Assume global connection gCon is open.

    If Drop Then
        gCon.Execute "ALTER TABLE [" & TableName & "]" & vbNewLine & _
                     "   ALTER COLUMN [" & ColumnName & "] DROP DEFAULT;"
        gCon.Execute "ALTER TABLE [" & TableName & "]" & vbNewLine & _
                     "   ALTER COLUMN [" & ColumnName & "] SET DEFAULT " & FormatSQL(DefaultValue)
    End If
End Sub

--- code ends here ---

Although I think that in this case it be simpler to use Tim Cottee's solution.

Hope this helps,

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.


Author Comment

ID: 6239663
Yes an easy solution but there is far reaching effects on several applications and COM objects.  The database must maintain backward compatibility for these, and there would be too much change required to upgrade them.

Tim Cottee, Walter M:
This may end up being part of the solution for SQL Server but it does not help for Access.  The preferred solution is to use ADOX so the same code works with both databases.

Up until the switch from DAO to ADO there were 13 database upgrades.  Developing separate code for each database may not sem like much of a problem for this small change but there will be more changes and doubling the code is not desired.
LVL 43

Accepted Solution

TimCottee earned 200 total points
ID: 6244121
Argonaut, the same code does work with access and SQL Server, the Alter Table statement is compatible with both and works fine with both. I understand the desire to use ADOX but I have found that in many ways it is less flexible than using the SQL statements directly. As Access and SQL server both support this syntax I would still suggest that this is a better way to go.

Expert Comment

ID: 6244286
Argonaut, I've specifically written and tested the code I posted earlier for the Jet Engine 4.0 as a workaround for the ADOX default bug you mentioned. I agree it would be nice if ADOX was able to do this, but unfortunately it isn't.
True database independency is hard and laborious to implement anyway, even when using ADO/ADOX because of the differing behaviour of the miscellaneous underlying drivers. There will almost always be certain incompatibilities between different drivers, and this is one of them. You should encapsulate these kind of driver-specific workarounds to make it easier to adapt them to another driver in the future.

Let's hope Microsoft will release a bug-fixed ADOX version in the near future!


Author Comment

ID: 6245096

Alter table works with Access?  I din't know this, I'll give it a try and if it works you got the points.


Author Comment

ID: 6245422
Thanks, good solution!

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

718 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