linked tables access - sql

[xperre van wilrijk]

Hi,

I have inherited an access userinterface that links to sql server
tables through ODBC. The SQL server database contains data related to
villages in my country and is populated by my collegues.

Now I have to create an access data file for each village and deliver
it to the local governments ... so they can see only their data using
the same userinterface.

I've made a little program that extracts the data by village from sql
server into access. So I have one access data mdb for each village,
with only their data, but now the userinterface has to access the
access database, no longer the SQL Server via ODBC.

I wondered if I could accomplish this without touching the UI mdb by
using two types of datafiles, one pointing to the ODBC and one with
it's own data, but that doesn't seem possible:

If I rename the name of the linked table in the datafile and I make a
linked table to the ODBC with the original table name the
userinterface does not accepts this. See the screenshot to see what I
have done : testlinked is my UI and testlinked_be is the data mdb.

I've tried to dynamically link tables when starting up the
userinterface, but since I'm a .NET developer with nearly no Access
experience I did not manage to do so.

Anyone experienced the same problem or anyone with a workaround
suggestion? I hoped I could stay with one UI so there's no need to
update two versions when some business logic needs to be adapted ...
but now I'm afraid there's no way around, maybe I can't without 2
versions of the userinterface ... one pointing to the ODBC and one
pointing to access data.

Thanks in advance,
Perre.

[lyle fairfield]

Somewhere in the mists of time I think I recall, maybe, TTBOMR, this
setup:

Create an ADP linked to the SQL DB (via OLEDB of course, not ODBC). This
ADP file acts only as a conduit.
Create an MDB and link to the Tables in the ADP. The MDB can have its own
local tables as well of course.

Maybe this will help with what you are trying to do; maybe not.
xperre van wilrijk <xp****@gmail.comwrote in news:7df743cd-9f59-4741-
93***************@k37g2000hsf.googlegroups.com:

Hi,

I have inherited an access userinterface that links to sql server
tables through ODBC. The SQL server database contains data related to
villages in my country and is populated by my collegues.

Now I have to create an access data file for each village and deliver
it to the local governments ... so they can see only their data using
the same userinterface.

I've made a little program that extracts the data by village from sql
server into access. So I have one access data mdb for each village,
with only their data, but now the userinterface has to access the
access database, no longer the SQL Server via ODBC.

I wondered if I could accomplish this without touching the UI mdb by
using two types of datafiles, one pointing to the ODBC and one with
it's own data, but that doesn't seem possible:

If I rename the name of the linked table in the datafile and I make a
linked table to the ODBC with the original table name the
userinterface does not accepts this. See the screenshot to see what I
have done : testlinked is my UI and testlinked_be is the data mdb.

I've tried to dynamically link tables when starting up the
userinterface, but since I'm a .NET developer with nearly no Access
experience I did not manage to do so.

Anyone experienced the same problem or anyone with a workaround
suggestion? I hoped I could stay with one UI so there's no need to
update two versions when some business logic needs to be adapted ...
but now I'm afraid there's no way around, maybe I can't without 2
versions of the userinterface ... one pointing to the ODBC and one
pointing to access data.

Thanks in advance,
Perre.

[xperre van wilrijk]

Hi,

I have succeeded another approach. I've created a local table with
the names of all linked tables and I have a startupform which allows
the user to create the linked tables based on the data in the linked
tables. It seems you cannot relink tables linked to ODBC to .mdb and
vice versa. So I added an additional option to drop the linked tables
and to recreate them ... linking to ODBC or linking to .mdb.

Thanks for your suggestion Lyle.
Peter.

[Larry Linson]

"xperre van wilrijk" <xp****@gmail.comwrote

. . . It seems you cannot relink tables linked to
ODBC to .mdb and vice versa. So I added an
additional option to drop the linked tables
and to recreate them ... linking to ODBC or
linking to .mdb.

That was my experience, years ago, linking Informix tables to Access 2.0 via
ODBC.

It turned out, in our situation, not to be particularly burdensome.

[Chuck Grimsby]

You (an Larry) are correct. ODBC tables can not be simply
"re-cinked". The linkages have to be dropped then (re-) created.

Dev has a rather good example of this at the Access Web:
http://www.mvps.org/access/tables/tbl0010.htm

On Thu, 19 Jun 2008 02:45:43 -0700 (PDT), xperre van wilrijk
<xp****@gmail.comwrote:

>Hi,

I have succeeded another approach. I've created a local table with
the names of all linked tables and I have a startupform which allows
the user to create the linked tables based on the data in the linked
tables. It seems you cannot relink tables linked to ODBC to .mdb and
vice versa. So I added an additional option to drop the linked tables
and to recreate them ... linking to ODBC or linking to .mdb.

Thanks for your suggestion Lyle.
Peter.

--
Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!