"Too Many fields defined" mystery

[JBG]

This one is a mystery and I am delerious from trying to solve it. HELP!

The following SQL and code (I aplogize for it's length, but I am desperate for an answer) in Access 97 to create a temporary query, "tmp", that will be used in a transferspreadsheet command (I have left out just a few irrelevant lines of the subroutine):

......

 strSql = " SELECT 
tblChargeoff.recdate,tblChargeoff.rperson,tblChargeoff.NotRe
coverableReason,tblChargeoff.AUother," & _
" 
tblChargeoff.Fdate,tblChargeoff.etime,tblChargeoff.Amanager,
tblChargeoff.Platform,tblChargeoff.EntryDate, " & _
" 
tblChargeoff.PrepCompBy,tblChargeoff.Orig,tblChargeoff.Cause
Reason,tblChargeOff.qcDate,tblChargeOff.qc," & _
" tblChargeoff.area,tblChargeOff.TID, tblChargeOff.oSight, 
tblChargeOff.cDescription, " & _
" tblChargeOff.eDescription, tblChargeOff.Recoverable, 
tblChargeOff.Controller, " & _
" tblChargeOff.eType, tblChargeOff.eAmount, 
tblChargeOff.SCenter, tblChargeOff.AU, " & _
" tblChargeOff.Cause, tblChargeOff.eDate," & 
varFormattedDate & "  AS nDate, " & _
" tblChargeOff.PlanNumber, tblPlanNames.PlanName, 
tblChargeOff.rAmt, " & _
" CCur(IIf([etype]=""gain"",[eamount],0)) AS Gain, " & _
" CCur(IIf([etype]=""loss"",-[eamount],0)) AS Loss, "
strSql = strSql & varTotalValue & _
" AS Net," & VBA.Format(Date, "yyyy") & " as NowDate FROM 
tblChargeOff LEFT OUTER JOIN tblPlanNames ON 
tblChargeOff.PlanNumber = tblPlanNames.PlanNumber "

.......

Set db = CurrentDb
For Each qd In db.QueryDefs
    If qd.Name = "tmp" Then
        DoCmd.DeleteObject acQuery, "tmp"
    End If
Next
Set qd = db.CreateQueryDef("tmp", strSql)
qd.Close
Set qd = Nothing
db.Close

I then use:

 DoCmd.TransferSpreadsheet acExport, 
acSpreadsheetTypeExcel97, "tmp", _
        "H:\tmp2.xls", -1

On all of our Win 2000/Office 2000/Access 97 machines, this works fine. But on one manager's laptop, she get's "Error 3190 Too many fields defined" when the above code is executed. BTW, the query gets created with no problem. The error occurs when the transferspreadsheet command is executed.

I know of this error as it relates to a static table and Access not releasing, say, deleted fields. But this is a query, dynamically produced, and there are far less than 255 fields in it.

Anyone know why this error could be occuring on just one machine? I have upped the memory on that one machine (virtual included) so that it matches, say, my Win 2K machine. Still, she gets the error.

Please.....HELP!

Thanks,

JBG

 

[tman138]

One quick item to check is the service release version of Access. If the client machine has a version that differs from the one that the app was created with, then bad things (like this) happen.

 

[JBG]

Thank you tman138. I will do just that and post back if that was the problem

 

[JBG]

The problem was the the temporary .xls file taht the transferspreadsheet was, I think, corrupt. Though I had wiped out the file and created a new one before, for some reason this did the trick. However, our LAN support guy did reinstall Access 97 before I did the file change.

Nonetheless, thank you again for taking the time to reply tman138.

Jbg

 

[Dvel]

jbg, you are right. Temp xls files seems to be corrupt.
I had the same problem and I solved it just using two folders instead of one.
As I need the transferred files to feed another xls file, I had a "pass" temp folder where I store files before deleting. Obviously, final xls is referred to this "pass" folder instead the original one.
In every loop step I generate the files and copy them into the "pass" folder. Once secured I delete the original temps files from the original temp folder. That means that in every loop I reset the temp files, so I can avoid the 255 fields counter error that Access has.

Althoug is quite simple, here you are the code for further use. Of course is not a complete code: just have the parts I have explained...

For sbarea = 0 To (ActTableSubareas.RecordCount - 1)

...

DoCmd.TransferSpreadsheet acExport, cSpreadsheetTypeExcel9, QueryName, TempPath & QueryExportName & ".xls", True
DoCmd.DeleteObject acQuery, QueryName
Set CancelRatesQry = Nothing

...

Files = Dir(TempPath & "*.*", vbArchive)
Do Until Files = ""
FileCopy TempPath & Files, TempPathFin & Files
Files = Dir
Loop

Kill (TempPath & "*.*&quot

...

Next sbarea

I hope this will help you.

Best regards

Dvel

PD: sorry about my english

 

[JBG]

Thank u very much for the reply , and your English is fine.

I like the confirmation that my Acces issues are indeed ones that others experience, rather than something dumb that I am doing.

I will uset his code for future projects.

Again, thanks Dvel...

JBG