Linking SQL7 Tables to Access 97 via SQLDMO?

Linking SQL7 Tables to Access 97 via SQLDMO?

Post by JC » Sat, 15 Jan 2000 04:00:00



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 their
own queries in Access 97.  We are not keen to allow users direct access to
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 server/db
    3. manipulate/copy data from the selected server/db into the Access DB
    4. drop the link to the selected server/db

I can see ways to do most of this except linking the required tables from
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 linking
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

 
 
 

Linking SQL7 Tables to Access 97 via SQLDMO?

Post by Robert Dufou » Sat, 15 Jan 2000 04:00:00


Here's a suggestion.
Have an ODBC data source on each workstation pointing to the SQl server and
database you want to use and make sure it's named the same on each machine.
On your development machine, make your Access data mdb with links to the SQL
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 you
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 of
part of the SQL data transferred locally so they can work on that if they
want to see what happens when they make changes to it. But they can never
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 a
million records. It could slow the server but it won't destroy it :-)

--
Robert Dufour, MCP, MCT
President SGI IMS Inc.
www.sgiims.com

Quote:> 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 their
> own queries in Access 97.  We are not keen to allow users direct access to
> 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 server/db
>     3. manipulate/copy data from the selected server/db into the Access DB
>     4. drop the link to the selected server/db

> I can see ways to do most of this except linking the required tables from
> 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 linking
> 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


 
 
 

Linking SQL7 Tables to Access 97 via SQLDMO?

Post by JC » Sun, 16 Jan 2000 04:00:00


Robert,
    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
and
> database you want to use and make sure it's named the same on each
machine.
> On your development machine, make your Access data mdb with links to the
SQL
> 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
you
> 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 of
> part of the SQL data transferred locally so they can work on that if they
> want to see what happens when they make changes to it. But they can never
> 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 a
> 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
their
> > own queries in Access 97.  We are not keen to allow users direct access
to
> > 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
server/db
> >     3. manipulate/copy data from the selected server/db into the Access
DB
> >     4. drop the link to the selected server/db

> > I can see ways to do most of this except linking the required tables
from
> > 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
linking
> > 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

 
 
 

Linking SQL7 Tables to Access 97 via SQLDMO?

Post by Robert Dufou » Sun, 16 Jan 2000 04:00:00


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
is executing.

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.
www.sgiims.com

> Robert,
>     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
> and
> > database you want to use and make sure it's named the same on each
> machine.
> > On your development machine, make your Access data mdb with links to the
> SQL
> > 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
> you
> > 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
of
> > part of the SQL data transferred locally so they can work on that if
they
> > want to see what happens when they make changes to it. But they can
never
> > 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
a
> > 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
> their
> > > own queries in Access 97.  We are not keen to allow users direct
access
> to
> > > 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
> server/db
> > >     3. manipulate/copy data from the selected server/db into the
Access
> DB
> > >     4. drop the link to the selected server/db

> > > I can see ways to do most of this except linking the required tables
> from
> > > 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
> linking
> > > 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

 
 
 

Linking SQL7 Tables to Access 97 via SQLDMO?

Post by Joe Fallo » Sun, 16 Jan 2000 04:00:00


In Access 97 Help you get:
"This example uses the RegisterDatabase method to register a Microsoft SQL
Server data source named Publishers in the Windows Registry."

Under Connect Property in Help you get:
ODBC;
DATABASE=database;
UID=user;
PWD=password;
DSN= datasourcename;

You can write code to link all the tables in the database and strip off the
dbo_ from their name.
I can dig up a sample if you need it.
--
Joe
Access MVP
Check out Dev Ashish's web site for answers to common questions
http://www.mvps.org/access/


Quote:> 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 their
> own queries in Access 97.  We are not keen to allow users direct access to
> 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 server/db
>     3. manipulate/copy data from the selected server/db into the Access DB
>     4. drop the link to the selected server/db

> I can see ways to do most of this except linking the required tables from
> 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 linking
> 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

 
 
 

Linking SQL7 Tables to Access 97 via SQLDMO?

Post by JC » Mon, 17 Jan 2000 04:00:00


Joe
    Thanks for this.  I'll look into and I may take you up on your offer of
a smaple.

John Campbell


> In Access 97 Help you get:
> "This example uses the RegisterDatabase method to register a Microsoft SQL
> Server data source named Publishers in the Windows Registry."

> Under Connect Property in Help you get:
> ODBC;
> DATABASE=database;
> UID=user;
> PWD=password;
> DSN= datasourcename;

> You can write code to link all the tables in the database and strip off
the
> dbo_ from their name.
> I can dig up a sample if you need it.
> --
> Joe
> Access MVP
> Check out Dev Ashish's web site for answers to common questions
> http://www.mvps.org/access/



> > 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
their
> > own queries in Access 97.  We are not keen to allow users direct access
to
> > 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
server/db
> >     3. manipulate/copy data from the selected server/db into the Access
DB
> >     4. drop the link to the selected server/db

> > I can see ways to do most of this except linking the required tables
from
> > 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
linking
> > 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

 
 
 

Linking SQL7 Tables to Access 97 via SQLDMO?

Post by JC » Mon, 17 Jan 2000 04:00:00


Robert

    Thanks for your persistence...ADO is my fallback option.  It's just such
a crying shame to have DMO on the desktop and not be able to make the link
(Pls understand I'm not blubbing that I should be able to, it would just
have been nice)

John


> 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
> is executing.

> 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.
> www.sgiims.com


> > Robert,
> >     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
> > and
> > > database you want to use and make sure it's named the same on each
> > machine.
> > > On your development machine, make your Access data mdb with links to
the
> > SQL
> > > 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
> > you
> > > 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
> of
> > > part of the SQL data transferred locally so they can work on that if
> they
> > > want to see what happens when they make changes to it. But they can
> never
> > > 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
> a
> > > 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
> > their
> > > > own queries in Access 97.  We are not keen to allow users direct
> access
> > to
> > > > 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
> > server/db
> > > >     3. manipulate/copy data from the selected server/db into the
> Access
> > DB
> > > >     4. drop the link to the selected server/db

> > > > I can see ways to do most of this except linking the required tables
> > from
> > > > 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
> > linking
> > > > 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

 
 
 

1. Access 97, SQL7 ODBC link

I have an access 97 database pulling data from SQL server
7. All works fine, untill you open a form. Opening a form
makes you wait for about 40 mins before it runs the first
line of code! removing the data source from the form stops
this but is useless as i want to get the data. Opening the
query or table by double clicking on it opens instantly.
On the sql server the process sqlservr takes up 100% cpu
untill the first line of code is run.

Anyone got any ideas?

2. C Precompiler For embedded SQL

3. SQL Server ---->Access 97 DB via Linked Server

4. Transactional commit of Informix and MQ Series together

5. Linking Access 97 to Visual Foxpro 3.0 via ODBC

6. how to take differential backup and store on another server

7. #Deleted Access '97 Records when linking via D3 ODBC

8. 16-bit ODBC to Oracle 8x

9. Linked Table between SQL 6.5 and Access 2.0 or Access 97 through ODBC

10. IBM Client Access and Microsoft Access 97 linked tables

11. Access to access 2000 mdb by access 97 program via VB6 and ADO

12. Linking to Access table via BDE via ODBC

13. Access 97/SqlServer 7: Appending Access Tables into Sql Server tables doesnt work for big tables