Linking a table

[Ryan]

Little puzzle this.

I need to link from SQL2000 to Access to get to a table held within an
Access DB. Now you can link within Access to a SQL table, but can this
be done the other way round ? It's the first time I've needed to do
something like this.

What I have is an application which was written (by me) with an Access
backend (to run on a CD) and now it needs to run on SQL. However, I
want to be able to swap over between backends so that I can choose
where this runs from. I've done this with the sole exception of one
table. Previously most of the data was held in one MDB file and a link
to another (one local copy and one on CD). My queries use the link to
query both local and the CD copy data together.

I can re-write things if I need to, but I was curious about there being
a way of doing this. What I want in effect is to query a view on SQL,
but the data will be held in Access instead of on SQL.

I suppose I can import as I need it as the data is purely read only. I
don't think this can be done, but wanted to check first. I can always
move the remainder of the data over to SQL if needed. It's not a big
problem, just something I'm curious about.

Any pointers would be appreciated.

Thanks in advance

Ryan

p.s. In case anyone wonders why I have taken this approach it is
because we have a query tool and data that we send out to clients on
CD. Some now want this web based, so I will publish the application on
a Citrix server, and by changing a config file can swap within the
application to point to SQL (by changing the ODBC settings). This way,
I only need one copy of the application and can change backends as I
need to.

[SQLChallenge]

How about a linked server that uses the JET OLEDB provider?

---
Mark Graveline
Take The Challenge
http://www.sqlchallenge.com
---

[Ryan]

Cool. That looks about what I was after. I'll have a play with it, but
thanks for the pointer.