Solved

Error received when trying to switch location of DB to another server using DSN file.

Posted on 2004-08-24
9
330 Views
Last Modified: 2008-02-01
Hi,  I have a report which uses five tables and view located on a single DB.  It worked but three of the tables had no data in them.  I found out that these three tables were actually on another server and that I was looking at empty copies of the tables.  So, I had to change the location of these three tables so that they would pick up the data.  When I tried to do this I got the following error message.

Microsoft OLE DB Service Components: The file is not a valid compound file.

This is in Cr 8.5
What I did was click Database|Set Location to get the Set Location dialog box.  Then I highlighted one of the thrre tables which needs to be changed. and clicked the Set Location button.  I get the Data Explorer button, navigate to the More Data Sources folder and open the OLE DB folder.  Under this folder I click the "Connect Using Data Link File".  This opens up a new dialog box which allows me to navigate to a specific file.  I changed the file type to ODBC DSNs and navigate to the folder where several DSN files have been set up by our system administrator.  He assures me that these DSNs work with MS Access as he just tested them.  When I click on the DSN that refers to the table I'm trying to set the location for I get the error mentioned above.  The system Admin's take on it is that CR is becoming confused and still thinks that the DSN file I'm trying to use is a UDL (User Data Link) file.

Any ideas?

RichNH
0
Comment
Question by:RichNH
9 Comments
 
LVL 10

Expert Comment

by:ebolek
Comment Utility
Why dont you click on create new on set datasource connection and select Access/Excel ADo connection. Then new box will pop up and ask you database name, secure logon vs. Find the table and click on it then then click on the old table. Then click update butto that you did. I am using 10 so it is like that in version 10. Oledb is for sql server. Access uses DAO connection. Or ypu can use ODBC RDO too

Regards
Emre
0
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
Try connecting using an ODBC connection.  I don't think an OLE driver will work with an ODBC DSN.

mlmcc
0
 
LVL 1

Author Comment

by:RichNH
Comment Utility
Ummmm, I may have misled.  The tables I'm trying to reconnect to are on MS SQL Server.  The system Admin. used Access to connect to them simply to see if the DSN files worked ok.

I went in to Set Location and tried using the ODBC connection and when I tried it wanted me to log in as the System Admin.  I wonder if he tested this stuff using his priv'd account...  Thing is, I don't know if the DB that's shown under the ODBC area is even the right DB that I should be using.  I am supposed to be using a specific copy of the DB on a specific test server, that's the only instance of the DB that has data in it....  This is frustrating.  I think I'll have to wait for him to get out of his meeting and the we can sit down at MY terminal with MY login and he can show how it is so simple to do.

RichNH
0
 
LVL 10

Expert Comment

by:ebolek
Comment Utility
if you are using sql server 2000 then use oledb connection

Click set location
then create new connection
2. pick oledb + sign
3. A new box will pop up find OLEDB for SQL server
4. click next
5. Enter the server name you want to log on
6. enter the database you want to logon
7. click integrated security to use windows authentication or enetr the user name and password to use sql authentication
make sure whatever authentication mwthod to use, the user must have the right to the tabkle, view, or your datasource

Regards
Emre

You can use ODBC too. If you want create a system dsn, but i dont recommend. OLEDB is the right way to go,
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:RichNH
Comment Utility
Emre, Thanks for the suggestion, is there no way to re-point a DB that's already added?  
0
 
LVL 10

Expert Comment

by:ebolek
Comment Utility
you can click on the exisating connection tab and if you see the connection that you want to use, you can point to an exisitng connection too.
0
 
LVL 1

Author Comment

by:RichNH
Comment Utility
The system admin has agreed to copy the data from the test server to the live server so I can run tests against the report.

What is going on is that the three DB tables in question are copies of the same table which is the standard cost buildup table.  We have three separate locations for our company each of which has its own standard cost buildup.  So as we approach year end the cost accountent is setting the future unit costs for items.  The DBs are located on a different server because when we do a full cost buildup we cannot differentiate between test and production.  Having the DBs on a separate server allows the accountants to do the full cost buildups they have to do without impacting production.  However they have the table definitions on the live side (those were the ones I built the report against) because eventually that's where the buildup data will be put prior to taking this whole mess live.  Apparently one can't repoint the tables using the DSN thru the DLF piece of CR.  So the system admin will copy what data they have already put into the tables to the live side and when we really need it he'll copy it again.

Thanks for your help!

Could the moderator cancel this issue?

RichNH
0
 
LVL 1

Accepted Solution

by:
Computer101 earned 0 total points
Comment Utility
PAQed, with points refunded (250)

Computer101
E-E Admin
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now