linking to SQL databases

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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.
 
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
 
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
 
no worries , tks for ur reply
Cheers

Dirk said:
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.
Click to expand...
 
i tried to convert from .mdb to .adp but it's not so straight-forward
Click to expand...

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
 
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
 
How do i set up the DNS ? is this going to be faster than the
odbc-linked table
Click to expand...

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
Click to expand...

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
 
Back
Top