Access To Excel Stopped working

[zheiling]

This is probably a really easy one for you all but I recently had my macro/VBA code stop working. I am exporting one record containing 16 fields. I was using Access 2003 originally and it had the built in macro transferspreadsheet. Now 2007 doesn't have that option so I am using VBA code to run transferspreadsheet. I am doing this in 2 other databases and they work just fine. The only thing I can think of that has changed is the data types were changed from float to decimal in my sql database that access pulls from. Also, another sql username/password had been added so some other users could access the database.

The error that comes up is "division by zero" which is odd since there aren't any calculations being done besides counts.

Any help would be greatly appreciated. Thanks

My code is:

' VSExport_to_Excel
'
'------------------------------------------------------------
Function VSExport_to_Excel()
On Error GoTo VSExport_to_Excel_Err
DoCmd.TransferSpreadsheet acExport, 10, "zach", "C:\Documents and Settings\jheiling\Desktop\Zach Drawings\M11VisionGraph.xls", False, ""

VSExport_to_Excel_Exit:
Exit Function
VSExport_to_Excel_Err:
MsgBox Error$
Resume VSExport_to_Excel_Exit
End Function

 

[Desafinada]

Hello,

I think the second parameter "102 might be the problem. According to this,
http://msdn.microsoft.com/en-us/library/aa193071(office.10).aspx
there is no 10 under the possible values

 

[zheiling]

10 is working for the second parameter in my other database but even if I change it to 8 it doesn't work. It worked about a week ago and I am not sure what changed. Is there a better way to send my table to excel?

 

[Desafinada]

Ist zach a table or a query? The error makes it sound like it's a query with 0 division. If it's a table, sorry, I run out of ideas.

 

[zheiling]

It is a query...But I am not doing any division in the query. It is a collection of 16 queries all pulled into one. None of the 16 have any division just counts.

 

[Desafinada]

Since I can't see your queries, I suggest that you check those queries again to see if they return any error. Maybe you could run them one by one and can then pinpoint which exact query/ies cause the problem.

 

[zheiling]

The queries all work. Each individual one and the combined one run and give good data. I eliminated a number of fields and it exported when I had 8 or less fields. Any more and it would fail. If the data types were changed from floats to decimal would that not allow all of my queries to run simultaneously?

 

[Desafinada]

I had a similar problem transferring spreadsheet but my error message at that time was "Error 3048, no more databases could be started" and I know that's because I have too many queries running. Try changing the data types and please let me know how it goes, maybe it's just an overload problem.