A better driver for Access to SQL

[Simon Harvey]

HI all,

We currently have access working as a front end to sql server 2000. The
database in sql server is very large and is taking a long time to execute
queries. I'm wondering if their is a faster driver available other than the
odbc one that we are currently using. As I understand it the odbc driver is
a lowest common denominator driver so I'm hoping there will be a more
specialised driver available.

Does anyone have any ideas?

Thanks all

Simon

 

[Arvin Meyer]

You can use the OLEDB driver which may be a bit faster. Proper indexing will
greatly improve performance, but the biggest gain will be seen by using
stored procedures or views on the server and only bringing down the data you
need.
--
Arvin Meyer, MCP, MVP
Microsoft Access
Free Access downloads:
http://www.datastrat.com
http://www.mvps.org/access

 

[Simon Harvey]

Thanks for that

Kindest Regards

Simon

 

[Norman Yuan]

You may want to consider using Access project (*.adp) as SQL Server's front
end. Access project uses OLEDB privider and since it is specially designed
to work with SQL Server, I assume it should be faster (or as fast as linked
tables through ODBC, at least).

 

[Kevin3NF]

But converting .mdb to .adp is a WHOLE lot of work....just an FYI

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.

 

[Simon Harvey]

Thanks guys.

I have a couple of questions still though:

1. I think you're right, we're not using an adp. It isn't really something
we can help - we didnt make the original app. If we're not using an adp, I'm
not sure whats happening at the moment when queries are used. The data is in
sql server so when our queries run the data is obviously being received from
the sql server. Why is there a performance increase in using adp version

2. Why is it a lot of work to convert the project to an adp project

thanks again for your help

Simon

 

[Kevin3NF]

1. The Access Jet engine is located on your desktop, so a large number of
records come from the SQL Server to your desktop, and are then queried using
your (presumably) weaker processor, instead of the server processor(s). The
SQL Server in this case is essentially sending you entire tables, instead of
the few records needed by the query. pass-thru queries are a great way
around this, as they pass the querying effort over to the SQL Server and let
the SS db engine run the query.

2. No local tables. Limitations on how big the SQL string can be.
Parameter queries in your Access db must be converted to SQL Server stored
procedures. Having to learn about "Max Records", "Input Parameters" and
"Server filters" among other things....simply, there are things that are
different, and not teribly well documented.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm
www.DallasDBAs.com/forum - new DB forum for Dallas/Ft. Worth area DBAs.