Problems after changing Linked Table data source

[EBGreen]

We have an Access DB that I inherited that has some information in it but is primarily the front end for a SQL DB. The SQL DB was moved to a new server yesterday. This of course broke all of the linked tables in the Access DB. I have fixed that by updating the DSN for the SQL DB and using the linked table manager to re-link the tables. The problenm I have now is that every single report refuses to open in either Preview or Design mode. When I right click on a report and try to view Object dependencies, I get an error that lists three possibilites for the failure.
1) The DB is read only (It is not)
2) I do not have permissions to open the object in design view (I did as of yesterday)
3) The source of a linked table could not be found

Does anyone have any idea how I can approach this? I won't be at work over the weekend, so there isn't any rush, but I will be working on this first thing Monday morning if anyone does have any ideas.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[Kanga35]

Just a question pop to mind: Do you have write or admin rights to the directory of that new server?

 

[EBGreen]

Thanks for the reply.

The SQL DB backend changed to a new server and I do not have admin rights on the new server. I never had admin rights on the old server either. The connection to the SQL backend is purely read only.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[me2you]

Hi TomThumbKP
I think you should cross check you queries, views and stored procedures (if any).

Peace,
me2you

 

[EBGreen]

Hanks for the idea. The queries are working fine. As for views and stored procedures, there are none in the SQL DB that are used by this Acces DB.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[EBGreen]

Just to add some more information, I cannot create a new report that shows a query that I can open just fine.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[Hap007]

Dear TomThumbKP,

Just an other Access quirk, job security for us Access Developers.

Thought for you, do you have any printers defined on the system that you are opening the Access DB on?

I have seen this error occur on earlier versions of Access when the PC inquestion did not have a Printer installed. Seems access needs at least one Printer installed to open and or design a report.

Hope This Helps,
Hap


Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/groups/accessDevelopers

 

[EBGreen]

Thanks for the input. I do have a default printer installed. Just for grins, I deleted the default printer and re-installed it then reopened the DB. No joy though.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[EBGreen]

More fuel for the fire. I think my problem may be even biger. I created a new Access DB with one table. In the table I created four rows of dummy data in three fields. I then created a 'SELECT *' query for this table. Whenever I attempt to create a report to display the query, I get an error that 'The wizard is unable to create your report.' If I try to create a report in design view, nothing happens at all.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[PHV]

Unfortunately, seems that a reinstall (at least a repair) of Access should be an option.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[EBGreen]

I've tried repairing with no luck. I'm about to reinstall.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[Hap007]

Dear TomThumbKP,

I agree with PHV, but more important, you stated that it all worked yesterday and the only thing that changed was a new DSN and server.

Form what you describe, something also changed on your PC. Perhaps some new software install or maybe worse, a virus.

I suggest that you try the same thing on a different user's PC regarding accessing the reports in the DB in question. If it works on the other PC, then it is your PC and the Access install that is the problem.

Question for you, did you recently upgrade to a new version of MS Office, such as Office XP or Office 2003, but are using MS Access 97 or Access 2000. What you describe in your most recent post can cause those types of problems.

Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/groups/accessDevelopers

 

[EBGreen]

Yeah, like most people asking a question I left out some key information. I am actually currently packaging software for a desktop refresh project that will replace all of the desktop cpus in the company (something like 35,000 cpus). As part of this, my computer was swapped out last week in the pilot. I'm beginning to think that there is a problem in the build.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[Hap007]

Dear TomThumb

I agree, better check the build.

Sounds like you have quite a task there, good luck,

Hap...

Access Developer

http://www.fcs-software.com/fcsaccessdev.html

Access based Add-on Solutions
Access Consultants forum

http://www.tek-tips.com/groups/accessDevelopers

 

[EBGreen]

Uninstalled and reinstalled Access 2003. No luck so far. I may kick this to the tech support nerds. I wish I could figure it out myself, but my packaging queue is building up.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]

 

[EBGreen]

Just a quick update. We still haven't solved the problem, but I've found a workaround. It is related to the printers (a star for Hap007). The workaround is to:
1)Set the default printer to the Microsoft Office Document Image Writer.
2)Open the DB.
3)Select a report.
4)File->Page Setup
5)On the Page Tab, select "Use Specific Printer" then select the printer that is normally the default printer from the dropdown list.
6)Repeat for every report.
7)Set the default printer back to normal.

[red]"... isn't sanity really just a one trick pony anyway?! I mean, all you get is one trick, rational thinking, but when you are good and crazy, oooh, oooh, oooh, the sky is the limit!" - The Tick[/red]