Too many Fields Defined Error 3190

[MePenguin]

Can anybody tell me why I'm getting this in the following code when the number of fields is greater than about 70?

For Each fld In rs.Fields
    If fld.name <> "CODE" And fld.name <> "Species Name" Then
        SQLText = "ALTER TABLE BugsCountsheet ALTER COLUMN [" & fld.name & "] long;"
        DoCmd.RunSQL SQLText
    End If
Next fld

the error is occuring on the DoCmd.RunSQL SQLText.

It's part of a large import, reformat, export routine, and works perfectly well for excel files with less than about 70 columns.

Is this the mysterious form lifetime controls limit???

Thanks,

Phil

Phil

---------------
Pass me the ether.

 

[ZmrAbdulla]

may be
[tt]
For Each fld In rs.Fields
If fld.name <> "CODE" Or fld.name <> "Species Name" Then
SQLText = "ALTER TABLE BugsCountsheet ALTER COLUMN [" & fld.name & "] long;"
DoCmd.RunSQL SQLText
End If
Next fld
[/tt]

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[jsteph]

zmr,
I think the "or" is not what he'd want, then it'd change both the "CODE" and "Species Name" fields.

I'm not sure the alter table syntax is correct either, phil. Is this a Jet table?
--Jim

 

[MePenguin]

Sorry, I missed a bit of background out.
It's an Access 2000 database, and the table is local (not linked), created using the transferspreadsheet method - I'll have to post the code this evening (CET).

You're right Jim, I need the 'or' (sorry Abdulla).

The systax works fine up to about 70 fields. I'm wondering whether I need to close the active form and run this part of the code from a separate module. If this didn't solve it, it would at least allow me to eliminate the 'form lifetime controls limit' problem...

Any thoughts? An alternative method for altering the field datatype?

Thanks,


Phil

---------------
Pass me the ether.

 

[ZmrAbdulla]

See this MSKB

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

to know more about ALTER TABLE syntax.

________________________________________________________
Zameer Abdulla
Help to find Missing people
Sharp acids corrode their own containers.

 

[MePenguin]

Thank's for the tip. Useful.


Phil

---------------
Pass me the ether.