View tables in linked Access database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

View tables in linked Access database

Author

Topic

davers
Starting Member

14 Posts

Posted - 2008-04-14 : 14:25:34

Hey everyone. I've linke an access database and I am able to query the tables like so:


SELECT *
FROM QFinity...tblEmployees

I can do that to all the tables, however, I'd like to create a view to this linked database. Is this possible? I have a more complex query I'd like to run:


SELECT dbo_evaluations.eval_id, dbo_evaluations.quality_date, dbo_eval_questions.status
FROM QFinity...dbo_evaluations INNER JOIN dbo_eval_questions ON dbo_evaluations.eval_id = dbo_eval_questions.eval_id;

I get the error "Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo_evaluations'."

I'm afraid I've reached the limit of my know how concerning sql server 2005... I think I read that I need to create a view? But I can't figure out how to do that.

Thanks for any help!

Dave

Windows XP, Office XP

davers
Starting Member

14 Posts

Posted - 2008-04-14 : 14:28:41

Jeez! Sorry...I should have stated that my original question is why can't I see my tables in the linked Access database. I can query them and I get data back. But I can't actually see the tables. That's why I think I need to create views...

Dave

Windows XP, Office XP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 14:29:17

Does your database contain table dbo_evaluations? If its from linked access db prefix access db name to it. The error suggests it cant find the table.

davers
Starting Member

14 Posts

Posted - 2008-04-14 : 14:31:50

Well..the table dbo_evaluations is linked from another sql server to the Access database. I'm trying it this way because I can't figure out how to link to the actual SQL server database.

Dave

Windows XP, Office XP

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-14 : 14:38:53

Add the server as a linked server to your db server and use four part naming convention for table. server.db.schema.object.

http://msdn2.microsoft.com/en-us/library/aa560998.aspx

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources