• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 345
  • Last Modified:

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

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
RichNH
Asked:
RichNH
1 Solution
 
ebolekCommented:
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
 
mlmccCommented:
Try connecting using an ODBC connection.  I don't think an OLE driver will work with an ODBC DSN.

mlmcc
0
 
RichNHAuthor Commented:
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
[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

 
ebolekCommented:
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
 
RichNHAuthor Commented:
Emre, Thanks for the suggestion, is there no way to re-point a DB that's already added?  
0
 
ebolekCommented:
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
 
RichNHAuthor Commented:
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
 
Computer101Commented:
PAQed, with points refunded (250)

Computer101
E-E Admin
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now