Easy way to change hundred of long integer fields to doubles?

[JTQ911]

I have a database with hundreds of fields that are defined in tables as long integers. Is there any way to easily convert all number fields to doubles? Thanks

 

[datAdrenaline]

Pats thougts on the table structure are definately accurate!! ... And I hope you take that advice to heart!!! ...

In addition ...

In NO WAY do I intend to come off as brash or arrogant, as the info I present is just that ... information ... and I hope and trust others would do the same for me if/when discrepancies in my advice exists!! ... with that ease into things with the upmost respect for Pat ... Pats advice on how to change the datatype programatically is inaccurate (ouch .. that was NOT meant to hurt!! ). Once a field is appended to the fields collection, you can not change its datatype (the .Type property) through the DAO.Fields collection of the DAO.TableDef object. For example .. the following code will FAIL (error 3219 - invalid operation):

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
 
Set db = CurrentDb
Set tdf = db.TableDefs("SomeTable")
 
For Each fld In tdf.Fields
    If fld.Type = dbLong Then
        fld.Type = dbDouble
    End If
Next fld

However ... all hope is not lost (with the exception of your presumed table structure !! ... <jovial "ribbing" .. with the hope you really will look at it!>) ... You CAN still change the datatype of an EXISTING field via code. You can execute a DDL SQL statement to accomplish the task.

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim fld As DAO.Field
Dim strSQL As String
 
Set db = CurrentDb
Set tdf = db.TableDefs("SomeTable")
 
For Each fld In tdf.Fields
    If fld.Type = dbLong Then
        fld.Type = dbDouble
        strSQL = "ALTER TABLE [SomeTable]" & _
                 " ALTER COLUMN [" & fld.Name & "] (FLOAT)"
        db.Execute strSQL, dbFailOnError
    End If
Next fld

Please note that I will, almost exclusively, execute DDL statements through ADO (ie: CurrentProject.Connection.Execute) simply because ADO has the capability to fully exercise JET 4.0 features, where as DAO does not ... so ...

db.Execute strSQL, dbFailOnError

Could be changed to (if references to ADO are set) ...

CurrentProject.Connection.Execute strSQL, ,adCmdText

...

Hope that helps!

 

[JTQ911]

Excellent! Thanks

 

[datAdrenaline]

Your welcome! ... Always glad to assist! .... But, please consider the structure advice given by Pat and re-inforced by me... you really do need a good foundation or you will find yourself creating work arounds for the life of a project .... I realize that Pat and I can't the see your complete scope, so you will have to make that call, but from our experiences, the description of your table has definately raised a warning flag!! ....

Good Luck with your project! ...

 

[datAdrenaline]

Hello Pat ...

>> Now I'm curious about which properties can and cannot be changed by modifing properties in the collection <<

Here is what I found in the help file, and some MINOR testing, with respect to properties used to define a Field object of a TableDef object ... AFTER its appended to the collection.

AllowZeroLength, R/W
Attributes, depends on the bit you want to turn on
DefaultValue, R/W
ForeignName, R/W IF the relationship being modified (created) is NOT appended to the collection
Name, R/W
OrdinalPosition, R/W
Required, R/W
Size, RO
Type, RO

As noted above, I did not have all these stats tucked away in my brain! ... but I hit the snag often enough with Type,that I did remembered that one! ... Now, I do alot of my schema changes through DDL statements .

Also, with respect to DAO and ADO ... the big point I was trying to get across is that some DDL clauses/statements (ie: like the one I used in this post) are equally effective being executed through DAO (ie: CurrentDb) and through the JET OLE DB provider with ADO, (ie: CurrentProject.Connection), but there are features of JET that CAN NOT be invoked if executed through DAO, but they are valid statements through ADO ... for example:

strSQL = "ALTER TABLE SomeTable ADD CONSTRAINT MyCheck CHECK (MyField > 0)"

The statement will raise and error if executed through DAO (CurrentDb.Execute), but is successful if executed through JET OLE DB (CurrentProjet.Connection.Execute) simply because the CHECK clause is not understood by DAO's SQL/DDL interpreter/compiler.

Another example is this DDL statement ...

strSQL = "ALTER TABLE tblForeignTable" & _
         " ADD CONSTRAINT riSetFKNull" & _
         " FOREIGN KEY (FK_FeildName)" & _
         " REFERENCES tblPrimaryTable (PK_FieldName)" & _
         " ON DELETE CASCADE"

Because the DAO SQL interpreter does not understand DELETE CASCADE, or the other referential integrity options, but JET OLE DB can do it with out issue.

So ... with that ... I usually execute my DDL statements through an ADO connection object because of its full ability to utilize JET 4.0.

Now ... with respect to generalities, ADO vs DAO and such, I'm with you both overlap with some exclusive functionality in either model. I generally tend towards DAO (I "cut my teeth" on it, per se) when working with JET data because, as you state, DAO is optimized for JET. But at the same time I encourage knowledge in both object models because you never know what you might need to do with your data!