?
Solved

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

Posted on 2004-08-24
9
Medium Priority
?
342 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
9 Comments
 
LVL 10

Expert Comment

by:ebolek
ID: 11881694
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 101

Expert Comment

by:mlmcc
ID: 11881696
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
ID: 11882047
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
Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

 
LVL 10

Expert Comment

by:ebolek
ID: 11882105
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
 
LVL 1

Author Comment

by:RichNH
ID: 11882497
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
ID: 11882575
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
ID: 11882868
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
ID: 12040543
PAQed, with points refunded (250)

Computer101
E-E Admin
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses
Course of the Month12 days, 15 hours left to enroll

777 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