Solved

ADOX - design changes in both Access & SQL Server

Posted on 2001-06-28
8
271 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
 
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
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now