Best Design for Front End of SQL2005

[DJMILLLS]

I am working on a front end in AccessXP for a SQL2005 DB. The main
purpose of the Access piece is to produce reports for sales, pricing
and inventory managment. The reports we get from the software vendor
are ugly, hard to read, and I need the flexibility of writing my own
reports, which I can do in Access. I have limited VBA knowledge,
virtually no SQL knowledge, but I have been working off and on in
Access for 8 years.

For the reports I need I have had to create additional tables, and so
far I have done this in Access and joined them to ODBC linked SQL
tables in the Access relationships window. The tables are not large
and are there to provide information that is not stored in the SQL
based program. Everythig was going great until some of the queries
and the reports based on those queries, started to run very slowly
(90-120 seconds to run). The worst query is one in which I have a
crosstab query (which runs OK by itself) with two fields(one each)
linked to two of my "homemade" tables.

Here are the questions:
1) Would I be better off: a) creating the "homemade" tables and
relationships in the SQL DB, b) spltting the Access tables, or c)
leaving it as is
2) Can any of this be helped via pass-through queries?, if so, how do
you do a pass-through in SQL
3) Do I need to dupicate the SQL relationships of the linked tables in
the Access relationships window?

 

[Susie DBA [MSFT]]

Access MDB is a waste of time.

If I were you; i would either use SQL 2000 or Access 2007.
Access 2002 and SQL 2005 is not a good combination.

Microsoft announced that they were going to release a patch for SQL
2005 and Access 2003.. but I haven't heard anything about it ever
since I heard it announced at the PDC convention.

-Susie

 

[Tony Toews [MVP]]

Note that this person is really A a r o n K e m p f and that he is not an employee
of Microsoft.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

 

[Susie DBA [MSFT]]

Tony;

why don't you stick to the facts

a) ADP is a better platform
b) ADP is more manageable
c) ADP doesn't have linked table manager, refresh / relink..
Connection Strings.. DAO-- NONE OF IT

MDB is worthless and it has been for a decade