[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now


ADOX - design changes in both Access & SQL Server

Posted on 2001-06-28
Medium Priority
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,

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

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 800 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

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.

Question has a verified solution.

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

Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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 Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

656 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