Solved

ADOX - design changes in both Access & SQL Server

Posted on 2001-06-28
8
273 Views
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.
0
Comment
Question by:Argonaut
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 11

Expert Comment

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

Expert Comment

by:TimCottee
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.
0
 
LVL 2

Expert Comment

by:WalterM
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;"
    Else
        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,

Michel
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 1

Author Comment

by:Argonaut
ID: 6239663
Otana:
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.
0
 
LVL 43

Accepted Solution

by:
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.
0
 
LVL 2

Expert Comment

by:WalterM
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!

Michel
0
 
LVL 1

Author Comment

by:Argonaut
ID: 6245096
Tim,

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

Thanks
0
 
LVL 1

Author Comment

by:Argonaut
ID: 6245422
Thanks, good solution!
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

808 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