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,

How Blockchain Is Impacting Every Industry

Blockchain expert Alex Tapscott talks to Acronis VP Frank Jablonski about this revolutionary technology and how it's making inroads into other industries and facets of everyday life.


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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses
Course of the Month11 days, 13 hours left to enroll

752 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