linking to SQL databases

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

maxzsim - 14 Dec 2005 04:14 GMT

Hi,

Is ODBC linked-table the only way to have a pre-defined link to SQL
databases ?

can it somehow be linked as ADO (or it must be done thru programming) ?

i have already an access program that is running but i am thinking of
speeding it up as it's getting slower.

if i set up a link to the SQL database via ADO programming , how do i
actually get the existing queries to work or it has to be changed as well

kindly advise

Reply to this Message

Dirk - 14 Dec 2005 10:12 GMT

As far as I know you can not configure ADO connections outside of code in
Access 2003 or earlier. ADO recordsets are incompatible with the Access
controls and can therefore not be used to populate them. You could however
open a DAO connection to the MS-SQL database which should result in DAO
recordsets which can be used to link to controls.

Your queries do not have to change because of the change from DAO to ADO.
They might need to change because the Jet database engine uses a different
dialect of SQL than MS-SQL's. I do not know the details of these differences
though.

> Hi,
>
[quoted text clipped - 10 lines]
>
> kindly advise

Reply to this Message

maxzsim - 14 Dec 2005 11:45 GMT

no worries , tks for ur reply
Cheers

> As far as I know you can not configure ADO connections outside of code in
> Access 2003 or earlier. ADO recordsets are incompatible with the Access
[quoted text clipped - 21 lines]
> >
> > kindly advise

Reply to this Message

Alex Dybenko - 14 Dec 2005 10:49 GMT

once you have linked tables - you can use them with either ADO or DAO, no
matter. also you can you pass-through queries for read-only data.
furthermore you can use Access project (ADP) against sql server

Signature

Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

> Hi,
>
[quoted text clipped - 10 lines]
>
> kindly advise

Reply to this Message

maxzsim - 14 Dec 2005 11:42 GMT

Hi,

i tried to convert from .mdb to .adp but it's not so straight-forward

for example in my previous access , 1 query uses iif(x , then A , else B)
but i cannot simple cut & paste into the queries of an .adp , the sames goes
of the nz function.

is there anyway to over come this ?

tks & rdgs

> once you have linked tables - you can use them with either ADO or DAO, no
> matter. also you can you pass-through queries for read-only data.
[quoted text clipped - 14 lines]
> >
> > kindly advise

Reply to this Message

Tim Ferguson - 14 Dec 2005 18:03 GMT

> i tried to convert from .mdb to .adp but it's not so straight-forward

ADO and DAO use different versions of SQL: dao is older, uses Jet-SQL (even
talking to SQL Server); ADO uses T-SQL (even when talking to jet databases)
which is closer to ISO standards. Note particularly the change in
wildcards, text quoting, and presentation of dates.

You can still set up a DNS to point to the actual data, and Link your
tables in the same way as between mdb files; then continue to use the DAO
code that still works in the expected way.

Hope that helps

Tim F

Reply to this Message

maxzsim - 15 Dec 2005 00:26 GMT

Hi Tim ,

How do i set up the DNS ? is this going to be faster than the odbc-linked
table

my whole purpose is to speed up the performance but i do not want to do a
wholesale change to the codings

tks & rdgs

> > i tried to convert from .mdb to .adp but it's not so straight-forward
>
[quoted text clipped - 10 lines]
>
> Tim F

Reply to this Message

Tim Ferguson - 15 Dec 2005 18:04 GMT

> How do i set up the DNS ? is this going to be faster than the
> odbc-linked table

Sorry: my spelling is getting worse. It's a DSN, and it _is_
(approximately) an ODBC link. Look at help or Google for Data Source
Names.

> my whole purpose is to speed up the performance but i do not want to
> do a wholesale change to the codings

There is more to optimising performance than changing the library. I
cannot find the web page at the moment (Allen?) but you need to look at
basic things first (in no particular order)

- appropriate indexing

- minimise the number of fields returned by queries. Ban SELECT *
forever!

- minimise the number of records returned - never open tables, always use
WHERE clauses, preferably base forms on a single record

- keep a recordset open to prevent the ldb file being created and torn
down all the time

- make sure the network is squeaky-clean. Access is really sensitive to
network noise and a bad NIC (even not involved in the app directly) can
lead to file corruption.

Just some thoughts...
All the best

Tim F

Reply to this Message