Unable to Export Data to Excel (transferspreadsheet) (1 Viewer)

[Kheribus]

Hello,

I'm trying to export to an excel via vba script that's called in a vbs script.

Public Sub regedRiderUpdateExport()
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "reged_rider_update", "C:\Users\Administrator\Desktop\Live Trip Files\studentUpdates\reged_rider_update.xlsx", True
End Sub

The underlying query runs a join on two tables with some criteria. The export works when i leave out the last desired field from the WHERE clause.

When I put the last desired WHERE clause in the querie's SQL, I get an error that MS Access was unable to export the data. I think this has to do with the fact that I cast the field in the second table as CINT, since the default type is short text (these tables are file linked automatically updated).

Has anyone ever experienced an issue with Excel exports from queries where one of the query criteria is typecasted?

Here is the query:

 SELECT sbt_reg_riders.student_id, sbt_reg_riders.last_name, sbt_reg_riders.first_name, sbt_reg_riders.grade, sbt_reg_riders.School_Code, sbt_reg_riders.address, sbt_reg_riders.address2, sbt_reg_riders.city, sbt_reg_riders.zipcode, sbt_reg_riders.Expr1, sbt_reg_riders.homephone, sbt_reg_riders.mailaddress, sbt_reg_riders.resaddress, sbt_reg_riders.isCoupon, sbt_reg_riders.pm_route, sbt_reg_riders.am_route, sbt_reg_riders.am_isspace, sbt_reg_riders.pm_isspace
FROM sbt_reg_riders LEFT JOIN Routefinder_reg_riders ON sbt_reg_riders.student_id = Routefinder_reg_riders.[Local ID]
WHERE ([sbt_reg_riders].[address]<>[Routefinder_reg_riders].[Router Comments]) 
OR ([sbt_reg_riders].[school_code] <> CINT([Routefinder_reg_riders].[School of Attendance Code]));

Thanks in advance

 

[sneuberg]

Does the query work if you just run the query by itself?

 

[Kheribus]

Yes, the query does run just fine if i run it by itself (can see the data in datasheet view).

 

[sneuberg]

I tried a simple query like

SELECT Table1.ID, Table1.SchoolCode
FROM Table1
WHERE CINT(Table1.SchoolCode) = 2;

and it exported without complaining. So the presence of CINT doesn't seem to be the problem

Is there any chance the presence of that additional criteria in your query causes addition records in the result that might be causing the problem. If you remove all of the WHERE clause does it export ok?

MS Access was unable to export the data

Was that all the error message said. Was there a number.

 

[Kheribus]

It works with the first criteria, and with no criteria, but not the second criteria. ie this doesnt work:

SELECT sbt_reg_riders.student_id, sbt_reg_riders.last_name, sbt_reg_riders.first_name, sbt_reg_riders.grade, sbt_reg_riders.School_Code, sbt_reg_riders.address, sbt_reg_riders.address2, sbt_reg_riders.city, sbt_reg_riders.zipcode, sbt_reg_riders.Expr1, sbt_reg_riders.homephone, sbt_reg_riders.mailaddress, sbt_reg_riders.resaddress, sbt_reg_riders.isCoupon, sbt_reg_riders.pm_route, sbt_reg_riders.am_route, sbt_reg_riders.am_isspace, sbt_reg_riders.pm_isspace
FROM sbt_reg_riders LEFT JOIN Routefinder_reg_riders ON sbt_reg_riders.student_id = Routefinder_reg_riders.[Local ID]
WHERE ([sbt_reg_riders].[school_code] <> CINT([Routefinder_reg_riders].[School of Attendance Code]));

In my case, i'm thinking that it MUST be the CINT cast...

The error message has no code - just says [my database name] was unable to export the data.

 

[Kheribus]

the tables that i am using to create the query are imported from excel to create linked tables. I have some automated processes that throw them on the server. Perhaps the only way to make this work is to format the source table (via script) before it is imported to the link so that I don't have to typecast it?

 

[sneuberg]

I guess if all else fails you could convert your query into a make table query and then export the table. Sorry that I can't think of anything better.

 

[Kheribus]

Actually I hadn't thought of that. Thank you for the suggestion.

 

[MarkK]

What if you change it to CLng rather than CInt, and convert both sides of the comparison...

OR CLng([sbt_reg_riders].[school_code]) <> CLng([Routefinder_reg_riders].[School of Attendance Code]);

Are you certain that neither of the fields in this comparison are ever null or non-numeric? To test for that, does this query return any rows...

SELECT sr.student_id, sr.last_name, sr.first_name, sr.grade
FROM sbt_reg_riders As sr LEFT JOIN Routefinder_reg_riders As rr ON sr.student_id = rr.[Local ID]
WHERE Not IsNumeric(sr.school_code) Or Not IsNumeric(rr.[School of Attendance Code])

 

[The_Doc_Man]

My question is, what is the "natural" format of the field [School of Attendance Code]?

In theory, ANSI SQL does not require the comparands to match precise types across any arithmetic relational operator. That is, all integers are alike to the version of SQL used by Access. BYTE Integer, WORD Integer, and LONG Integer can be directly compared to each other gainfully. You don't need type conversions - particularly for the "<>" comparison. NOTE that this is not necessarily true for JOINs, where I think the joining field types DO have to match.

By the way, I have to offer a very minor tweak. It is considered to be asking for trouble to have field names with embedded spaces. You might never have to upconvert your tables to something like SQL Server or ORACLE or SYBASE or one of those other "big boy" back ends... but with spaces in a name, you CAN'T do so. You also forever force yourself to use brackets on names containing spaces.

Mark's question is more to the point, because if you were running a valid query that resulted in an empty recordset, I think either it would work (giving an empty spreadsheet) or would give you a message about having no records. The error message tells me something else is going on with your export and that CINT is diverting you from finding the real problem.

 

[sneuberg]

I was able to duplicate your symptoms with the simple query in post 4. I got this

but to get that error message I had to put non-numeric data in the field that CINT was applied to. So when I run the the query directly this way I get a type mismatch error. If your problem turns out to be in the data then I don't understand why running the query isn't producing any errors.

Anyway I found that I don't get the error if I used the Val function in place of the CInt function and also if i used if before Cint, i.e., CINT(VAL([Field])). So I suggest giving that a try.

In a previous post I said

I guess if all else fails you could convert your query into a make table query and then export the table. Sorry that I can't think of anything better.

This would be a pretty sorry solution as this would cause your database to increase in size by the table size each time the query was run. You'd have to run regular compact and repair on the database to keep the size down. So I suggest avoiding this solution but trying it might tell you sometime about the problem.

 

[Kheribus]

Thank you everyone for your thought and responses.

First suggestion, using CLNG on both sides of the criteria expression gives me a datasheet table of 2537 records but still gives the non coded error message, "[mydatabase] was unable to export the data."

Regarding spaces in column names: Yes I agree. The data from these tables is coming from a fairly archaic third party software, and is exported as-is. I can add a line in my vbscript to change this column name. I don't use spaces in column names in my main app.

Regarding the query provided to return non-numeric values:

SELECT sr.student_id, sr.last_name, sr.first_name, sr.grade
FROM sbt_reg_riders As sr LEFT JOIN Routefinder_reg_riders As rr ON sr.student_id = rr.[Local ID]
WHERE Not IsNumeric(sr.school_code) Or Not IsNumeric(rr.[School of Attendance Code])

Yes, it does have non numeric values. That is why I am attempting to cast the right criterion [School of Attendance Code] as a number. Routefinder_reg_riders.[School of Attendance Code] is of type Number and sbt_reg_riders.School_Code is of type Short Text.

These files are auto exported from legacy style systems, where they are transferred to my server into the linked folder/file and the table in Access is automatically updated.

This may be as simple as me using a vbscript to re-format the sbt_reg_riders.school_code before auto-linking by ftp update script.

This is just an exercise, I suppose, of understanding the intricacies of Access SQL and what kind of frankenstein crap i can get away with here.

Sorry if my misunderstanding of this situation is unduly complicating things.

Cheers,

 

[MarkK]

So is this solved then? Did you try to filter out the non-numeric rows and do the export?

 

[Kheribus]

No. Like I said the school code in one of the tables is of type short text since in the base linked excel it is formatted that way.

I was trying to cast it as a number so that I could do the comparison and return where they are not equal.

I will play around a little more with this and report back.

Cheers,

 

[The_Doc_Man]

If the school code is numeric text, that is one thing. However, if your codes include things like "abc" in text as well as "123" in text, that ABC is going to cause headaches. You would do better to convert the OTHER side of the comparison to a text string and then drop the CINT entirely. Because the "<>" operator ALSO works for text compares and doesn't care if the strings are of unequal length. There are rules for that "unequal length" case that would allow the comparison to be transparent. Again, since this is the "not equal" case, ALMOST anything goes - but not the implied type mismatches.

 

[Kheribus]

Thank you, Doc Man, for the advice. Of course now that you mention it, that makes perfect sense. Both sides should be type converted to CSTR.

Tried that and I am still getting the same generic error, [my database name] was unable to export the data.

The file is created in the destination folder, with the sheet created and named (same as file name), but it is blank.

I may have some time to get back around to this this afternoon, so hopefully screwing around with everything will help me figure out why this is happening...

 

[MarkK]

My bet is that you have a null value in a row in a field. The query runs fine because it can display #ERR in the null field, but it can't export because no matter what type the field is supposed to be output as, #ERR is not a valid member of that type. So use an IsNull() filter to find this row and correct your data. Then it will hardly matter if your result fields are CStr()ed or CInt()ed or CLng()ed. Those will all fail if they encounter a Null in the data.

 

[cat2phat]

On my side the generic error "... unable to export the data." occurs when there are outer joins. By removing the outer joins my query exports without error.