Linking to Excel - spreadsheet locking

[JD5241]

Crystal XI, MS Excel: I'm linking a report to a spreadsheet that is updated manually every day. I'm curious about the behavior of the spreadsheet, stemming from the issue that it seems Crystal is locking the spreadsheet, and not unlocking it after reports are run and users are logged off InfoView. Since the spreadsheet is locked or "in use" as the message states, I can't open it to update it.
This is my first use of a spreadsheet as a database for a report, and I'm not sure how it works together with Crystal, if there are any special considerations I might not have thought of that are causing the spreadsheet to stay locked. Any insight would be greatly appreciated.

 

[synapsevampire]

How are you connecting to the spreadsheet?

In general they are horrible data sources.

Here are some options:

In lieu of using the file connect method, use ODBC and make sure that you have READ ONLY checked.

or

Link the spreadsheet into an MS Access database, and cfreate a query in Access to use as the Crystal Report data source.

-k

 

[JD5241]

I'm using the Access/Excel (DAO) connection to get to the spreadsheet. It's in a public folder that everyone has access to, but now it won't even run because it's looking for a login that is nonexistant... ergo, we're going to go with Access. It was our original idea to go with that, but it *was* working just linking to the spreadsheet at first, so we put the Access idea on the shelf. Now that it's sketchy as heck trying to run the reports off the spreadsheet itself, we've changed our minds. Thanks for the advice.

"It's a dog-eat-dog world, and I'm wearing Milk Bone underwear." - Norm

 

[JD5241]

Ok. I've linked the spreadsheet to an Access database. I'm still having the issue of a login and password. This is the error I get:
"CrystalReportViewer
Unable to retrieve Object.
The database logon information for this report is either incomplete or incorrect."

In the Process/Database tab in the CMC, I have "Use SSO context for database logon" selected. No login and password is required to get to the Access database, or the spreadsheet driving it, so I don't know why it's saying they're missing, unless I have to specify one?

"It's a dog-eat-dog world, and I'm wearing Milk Bone underwear." - Norm

 

[JD5241]

More on the subject:

In lieu of using the file connect method, use ODBC and make sure that you have READ ONLY checked.

or

Link the spreadsheet into an MS Access database, and cfreate a query in Access to use as the Crystal Report data source.

I tried both, but am still not able to run the report in Infoview... "Unable to retrieve object".

"It's a dog-eat-dog world, and I'm wearing Milk Bone underwear." - Norm

 

[synapsevampire]

When you connect to the file use the UNC, not a mapped drive, as in:

\\machinename\path...

not

D:\path...

-k

 

[JD5241]

I'm not sure where you mean... in linking the spreadsheet to the Access database or setting up the ODBC? I don't see anywhere that I have a choice of using the UNC or mapped drive...?

"It's a dog-eat-dog world, and I'm wearing Milk Bone underwear." - Norm

 

[synapsevampire]

In either connectivity situation it will be required as the server may not have the same drive mappings, anyway, Crystal suggests doing so.

So how could you have linked to the spreadhseet without using some sort of BROWSE or some such to the drive?

Instead of selecting the drive letter, type in the machine name.

You're so close now...

-k

 

[JD5241]

I figured out what you meant and did it, but I'm still getting the same error when running the report. "Unable to retrieve Object. Failed to open the connection."

I'm wondering if it doesn't have to do with my lack of login since the database doesn't require one... does running a report off of an Access table require a login id and password?

"It's a dog-eat-dog world, and I'm wearing Milk Bone underwear." - Norm

 

[synapsevampire]

Access doesn't require a login unless you force it to.

It may be that the account CE runs under on the server doesn't have access to the file (database).

A good test bed is to always have Crystal Reports installed on the CE server for testing.

Now your messages are vague, I assume that you mean that Infoview can't open tthe report, not just Crystal.

-k

 

[JD5241]

The report works in Crystal, but not Infoview. But the more I'm messing with it, I think there might be a couple of other issues going on, so I broke down and logged a ticket with BO tech support to try to straighten the whole kit and caboodle out.

Thanks for all your help, I really appreciate it.

"It's a dog-eat-dog world, and I'm wearing Milk Bone underwear." - Norm