I don't think you can realy get away from using ODBC data sources on the
workstations. If you want to link tables from SQL server to an access mdb,
when you're doing it manually you have to select to link to an ODBC
datasource and you are given the choice of using an existing one or creating
a new one, so anything you would do to automate this linking process in code
would need to use the same process logic.
You could conceivably use ADO code in access modules to be able to retrieve
recordsets data from the SQL tables without having a existing link in the
access mdb. This won't create links either.
You would need to set a reference to the ADO Libraries in Access (means that
the libraries must exist on the workstations, or be installed before you can
use your access program there).
However, obviously your users won't be able to use the Access query
designer against these recordsets since they will exist only when the code
Mind you, I'm willing to be wrong about all this. If I am, let me know, I
always like to learn new stuff :-)
Robert Dufour, MCP, MCT
President SGI IMS Inc.
> Thanks but...There is an unknown number of servers and databases (MSDE
> going on to the desktop); hence the reason why ODBC data sources aren't
> really a flyer.
> John Campbell
> > Here's a suggestion.
> > Have an ODBC data source on each workstation pointing to the SQl server
> > database you want to use and make sure it's named the same on each
> > On your development machine, make your Access data mdb with links to the
> > tables, via your ODBC datasource.
> > You can then distribute a copy of the data mdb to the user's individual
> > computers. You can also use Access security via your own system file (eg
> > MyOwn.mdw) to limit the access to the linked SQL files as read-only.
> > Your users will only be able to open the Access mdb with a password and
> > can control exactly what they can do in it.
> > You can then let users query the SQL database to their hearts content,
> > writing any query they like. You can write import queries to get copies
> > part of the SQL data transferred locally so they can work on that if
> > want to see what happens when they make changes to it. But they can
> > damage the data on the SQL server. You don't have to worry about
> > disconnecting them and reconnecting them. You could of course do this in
> > code, but the worst your users could do is run a query that tries to get
> > million records. It could slow the server but it won't destroy it :-)
> > --
> > Robert Dufour, MCP, MCT
> > President SGI IMS Inc.
> > www.sgiims.com
> > > We are moving a Jet based project to SQL Server/MSDE. One of the
> > facilities
> > > that was previously available was that users could create and store
> > > own queries in Access 97. We are not keen to allow users direct
> > > the SQL Server/MSDE data so what we'd like to do is:
> > > 1. provide users with an Access DB
> > > 2. when the DB is opened users are prompted to connect to a
> > > 3. manipulate/copy data from the selected server/db into the
> > > 4. drop the link to the selected server/db
> > > I can see ways to do most of this except linking the required tables
> > > the selected server/db to the Access db on a temporary basis - I can't
> > > expect users to be forever creating ODBC datasources and doing the
> > > themselves. I can connect to the server/db and enumerate the tables I
> > need
> > > using SQLDMO...The core question therefore is can I then use that
> > > connection/tables collection to link those tables to Access 97 or do I
> > need
> > > to back up and use ADO/DAO?
> > > Thanks for any help
> > > John Campbell