Linked tables + ADO question

[Rob Hofkens]

Hello everyone.

I have been reading and asking about Access & SQL Server.
I hope this question is in the right newsgroup.

Using Access as frontend and SQL Server as the database I can link the
tables via ODBC.
I have some statements which I would like to see if I got it right.

1) Using linked tables I am able to manipulate the data stored in the
tables.

2) I cannot change the structure of the SQL database with normal access
tools.

3) I can change the structure of the SQL database with ADO commands.

4) I could do everything with ADO commands but using linked tables is easier
to manipulate data is easier.

I am looking forward to comments on these statements.

Thank you in advance.

Rob Hofkens

 

[Brian Wilson]

Hello everyone.

I have been reading and asking about Access & SQL Server.
I hope this question is in the right newsgroup.

Using Access as frontend and SQL Server as the database I can link the
tables via ODBC.
I have some statements which I would like to see if I got it right.

1) Using linked tables I am able to manipulate the data stored in the
tables.

2) I cannot change the structure of the SQL database with normal access
tools.

3) I can change the structure of the SQL database with ADO commands.

4) I could do everything with ADO commands but using linked tables is
easier to manipulate data is easier.

I am looking forward to comments on these statements.

Thank you in advance.

Rob Hofkens

Basically all of the points are correct, but I would add:

1. Yes provided each table has a primary key, the user has permissions, etc
2. If you are using an mdb file with linked tables then this is correct.
However, Access can create another type of file with an adp extension. This
is called a project and this will provide you with standard GUI tools to
directly alter the design of the SQL Server tables, just as you could if the
tables were in an mdb file. So, in general, Access can alter the structure
of the SQL database.
3. Yes but you could also use DAO.
4. Yes - with linked tables you get get a basic bound form working in 2
seconds with no need for any code whatsover.

 

[Ed Warren]

Brian,
I've also been trying to use Access as a front-end and SQL server as a
back-end. I would like to use ADO to do the link's rather than ODBC. Is
there a place that provides some sample code to do this?

Thanks,

Ed Warren.

 

[Brian Wilson]

Brian,
I've also been trying to use Access as a front-end and SQL server as a
back-end. I would like to use ADO to do the link's rather than ODBC. Is
there a place that provides some sample code to do this?

Thanks,

Ed Warren.

If you are using linked tables - then this means odbc. You cannot use ado
to create a linked table that would somehow not involve odbc. Is that what
you were asking?
As to writing code, it very much depends on what sort of thing you are
trying to do. Perhaps you could say what you would like to do and what
advantages you would hope to see over using linked tables? Are you looking
at bound/unbound forms and does your application make use of stored
procedures.
If you are simply looking for an alternative to linked odbc tables, then you
could try creating an Access project (.adp format). This connects directly
to the SQL Server tables and is more recent technology than odbc - although
I do not recommend them myself.

 

[Ed Warren]

Thanks you have answered my question. I wanted to use ado code to do the
linking. From your answer, I'm stuck with ODBC.
I can live with that for now. It's better for me than the adp route.

Thanks, I can now quit 'looking'

Ed Warren.

 

[Rob Hofkens]

Basically all of the points are correct, but I would add:

1. Yes provided each table has a primary key, the user has permissions,
etc
2. If you are using an mdb file with linked tables then this is correct.
However, Access can create another type of file with an adp extension.
This is called a project and this will provide you with standard GUI tools
to directly alter the design of the SQL Server tables, just as you could
if the tables were in an mdb file. So, in general, Access can alter the
structure of the SQL database.
3. Yes but you could also use DAO.
4. Yes - with linked tables you get get a basic bound form working in 2
seconds with no need for any code whatsover.

Thank you Brian for answering my questions.
I would like to comment on your answers if thats ok.

1) I manage the SQL Server so that wouldn't be a problem.

2) I tested with ADP and that works fine too. But most people seem to
dislike ADP's for some reason I still don't know. I noticed that in ADP's
you can do a lot more with the database in respect to structure.

3) I have read that DAO is posible but not recommended anymore. ADO seems to
be superior over DAO. I guess thats because DAO works only within Access ?

4) With regards to Bound or Unbound form , is there a "rule" when to use one
or the other ?

 

[Brian Wilson]

Thank you Brian for answering my questions.
I would like to comment on your answers if thats ok.

1) I manage the SQL Server so that wouldn't be a problem.

2) I tested with ADP and that works fine too. But most people seem to
dislike ADP's for some reason I still don't know. I noticed that in ADP's
you can do a lot more with the database in respect to structure.

3) I have read that DAO is posible but not recommended anymore. ADO seems
to be superior over DAO. I guess thats because DAO works only within
Access ?

4) With regards to Bound or Unbound form , is there a "rule" when to use
one or the other ?

Personally speaking, I find adp's most useful when there is not much of a
front end - in other words, no complex forms, reports, modules etc. They
allow a user with Access to easily change server objects such as tables,
stored procedures, etc. The problems come in building finished applications
for end users who will never need to see any of the tables, etc. The
problems are due to the differences in the way forms work within an adp
(which take time to learn if you are used to mdb's) coupled with the fact
that some of these features don't (or certainly didn't) work correctly. I
can't remember the details of these irritations/bugs/features but when I
found I couldn't quickly get to develop working applications with them I
gave up. Perhaps some people have persevered but I know many others also
lost patience - especially as some of the advertised benefits (such as the
lack of dependence on JET) were not really true.

The DAO/ADO debate has been written about an awful lot. If you are using an
all-Access solution then you only need to know DAO and ADO brings very
little new and lacks a number of features. If you are using SQL Server,
then probably it is handy to know both. Although, with DAO and stored
procedures, you could achieve an awful lot without needing ADO.

My basic strategy is to use unbound forms very sparingly. Bound forms do,
and always have, work very well with Access and I would have to be shown the
benefits before I would consider ignoring this feature.

 

[Rob Hofkens]

Thanx again Brian !
This information is realy usefull for me

Cheers,

Rob Hofkens