PC Review


Reply
Thread Tools Rate Thread

Best Design for Front End of SQL2005

 
 
DJMILLLS
Guest
Posts: n/a
 
      22nd Mar 2007
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?

 
Reply With Quote
 
 
 
 
Susie DBA [MSFT]
Guest
Posts: n/a
 
      22nd Mar 2007
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

On Mar 22, 2:34 pm, "DJMILLLS" <djmil...@gmail.com> wrote:
> 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?



 
Reply With Quote
 
Tony Toews [MVP]
Guest
Posts: n/a
 
      22nd Mar 2007
"Susie DBA [MSFT]" <(E-Mail Removed)> wrote:

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
 
Reply With Quote
 
Susie DBA [MSFT]
Guest
Posts: n/a
 
      23rd Mar 2007
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


On Mar 22, 3:41 pm, "Tony Toews [MVP]" <tto...@telusplanet.net> wrote:
> "Susie DBA [MSFT]" <susie...@hotmail.com> wrote:
>
> 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 athttp://www.granite.ab.ca/accsmstr.htm



 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Front Page design Issue =?Utf-8?B?Sm9obiBGb3Jk?= Microsoft Frontpage 4 2nd Feb 2006 02:04 PM
New to web design and Front Page =?Utf-8?B?TWlrZQ==?= Microsoft Frontpage 5 9th Jul 2005 03:42 AM
form design from front end Scott Microsoft Access External Data 2 12th Jan 2005 10:41 PM
Front End Back End Design =?Utf-8?B?ams=?= Microsoft Access Database Table Design 1 26th Sep 2004 02:45 PM
Front Page design faults? Keith Foster Microsoft Frontpage 6 23rd Jan 2004 09:12 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 06:12 AM.