How much to convert to SQL server

Hi:

I have an Access database that's been running (in one form or another)
for a couple of different clients for a few years. Now a new client has
requested that it be implemented with a SQL server back-end. I'm doing
my best to learn about SQL server, and I plan to leave the front-end
more or less as-is, just linking to the SQL server back end, but here's
a basic question:

The db has a front-end linked to two back-ends. One of the back-ends has
completely static data, and so in an all-Access installation it sits on
the C drive along with the front end. Only the 2nd backend sits
on the server.

Now, should I convert both back-ends to SQL server, or just the one on
the server? Reasons, pros, cons?

TIA.

Jan

re: How much to convert to SQL server

Your new client is the one who wants the conversion and, presumably, is
paying you to convert the database. You should be asking the client what
_they_ want, but be ready with logical arguments if they want something
"flakey".

The following applies only to Access MDB, using ODBC drivers, to link to an
SQL server database:

Relatively unchanging lookup tables are often kept local to an Access
application -- States in the US is a prime example, company structure
(divisions, departments) is another. I'd personally see no need to migrate
static data to the back-end and have to access it across a
possibly-slower-than-I'd-like network.

Still, the client may have their own reasons (or even prejudices) and ideas
about what can reasonably be moved to the backend.

Larry Linson
Microsoft Access MVP



"Jan" <jan@stempelconsulting.com> wrote in message
news:11v77m878qsla75@corp.supernews.com...[color=blue]
> Hi:
>
> I have an Access database that's been running (in one form or another)
> for a couple of different clients for a few years. Now a new client has
> requested that it be implemented with a SQL server back-end. I'm doing
> my best to learn about SQL server, and I plan to leave the front-end
> more or less as-is, just linking to the SQL server back end, but here's
> a basic question:
>
> The db has a front-end linked to two back-ends. One of the back-ends has
> completely static data, and so in an all-Access installation it sits on
> the C drive along with the front end. Only the 2nd backend sits
> on the server.
>
> Now, should I convert both back-ends to SQL server, or just the one on
> the server? Reasons, pros, cons?
>
> TIA.
>
> Jan[/color]

re: How much to convert to SQL server

Hi, Larry:

Thanks for the quick reply.

Unfortunately, the client isn't directly mine; I'm more or less a
subcontractor. I'm not sure they know exactly why they want SQL Server,
except that they think it's the "better" way. And I'm not in a position
to argue. They do have some issues with lots of users possibly spread
out over several offices.

Anyway, I just wanted to be sure there wouldn't be some advantage to
having all the linked tables be converted to SQL Server. And yes, I'm
planning to use the ODBC link to the SQL Server backend. My preference
is certainly to keep as much of the data local, and in Access, as
possible, if only because it's what I know best.

Anyone have any other views?

Jan

Larry Linson wrote:[color=blue]
> Your new client is the one who wants the conversion and, presumably,
> is paying you to convert the database. You should be asking the
> client what _they_ want, but be ready with logical arguments if they
> want something "flakey".
>
> The following applies only to Access MDB, using ODBC drivers, to link
> to an SQL server database:
>
> Relatively unchanging lookup tables are often kept local to an Access
> application -- States in the US is a prime example, company
> structure (divisions, departments) is another. I'd personally see no
> need to migrate static data to the back-end and have to access it
> across a possibly-slower-than-I'd-like network.
>
> Still, the client may have their own reasons (or even prejudices) and
> ideas about what can reasonably be moved to the backend.
>
> Larry Linson Microsoft Access MVP
>
>
>
> "Jan" <jan@stempelconsulting.com> wrote in message
> news:11v77m878qsla75@corp.supernews.com...
>[color=green]
>> Hi:
>>
>> I have an Access database that's been running (in one form or
>> another) for a couple of different clients for a few years. Now a
>> new client has requested that it be implemented with a SQL server
>> back-end. I'm doing my best to learn about SQL server, and I plan
>> to leave the front-end more or less as-is, just linking to the SQL
>> server back end, but here's a basic question:
>>
>> The db has a front-end linked to two back-ends. One of the
>> back-ends has completely static data, and so in an all-Access
>> installation it sits on the C drive along with the front end. Only
>> the 2nd backend sits on the server.
>>
>> Now, should I convert both back-ends to SQL server, or just the one
>> on the server? Reasons, pros, cons?
>>
>> TIA.
>>
>> Jan[/color]
>
>
>[/color]

re: How much to convert to SQL server

Jan wrote:[color=blue]
> Hi, Larry:
>
> Thanks for the quick reply.
>
> Unfortunately, the client isn't directly mine; I'm more or less a
> subcontractor. I'm not sure they know exactly why they want SQL
> Server, except that they think it's the "better" way. And I'm not in
> a position to argue. They do have some issues with lots of users
> possibly spread out over several offices.
>
> Anyway, I just wanted to be sure there wouldn't be some advantage to
> having all the linked tables be converted to SQL Server. And yes, I'm
> planning to use the ODBC link to the SQL Server backend. My
> preference is certainly to keep as much of the data local, and in
> Access, as possible, if only because it's what I know best.
>
> Anyone have any other views?[/color]

Well even if you want to keep a few tables local (I see no reason to) you would
often want another copy of the same table on the server if it is ever used in a
query. You don't want to create queries in Access that use both a local table
and a link to a server table. That would definitely be an inefficient query to
run unless the local table was very small).

In my Access FE/SQL Server BE apps ALL the tables are on the server and
performance is just fine on a standard 100 mb LAN.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

re: How much to convert to SQL server

If you are using transactions, you probably want to re-write
any transactions that include the static tables. If you use
local jet tables, the transactions are more likely to block
internally, and if you use SQL Server tables, the transactions
are likely to block other users.

(david)



"Jan" <jan@stempelconsulting.com> wrote in message
news:11v7c5l7h5ig7dc@corp.supernews.com...[color=blue]
> Hi, Larry:
>
> Thanks for the quick reply.
>
> Unfortunately, the client isn't directly mine; I'm more or less a
> subcontractor. I'm not sure they know exactly why they want SQL Server,
> except that they think it's the "better" way. And I'm not in a position
> to argue. They do have some issues with lots of users possibly spread
> out over several offices.
>
> Anyway, I just wanted to be sure there wouldn't be some advantage to
> having all the linked tables be converted to SQL Server. And yes, I'm
> planning to use the ODBC link to the SQL Server backend. My preference
> is certainly to keep as much of the data local, and in Access, as
> possible, if only because it's what I know best.
>
> Anyone have any other views?
>
> Jan
>
> Larry Linson wrote:[color=green]
>> Your new client is the one who wants the conversion and, presumably,
>> is paying you to convert the database. You should be asking the client
>> what _they_ want, but be ready with logical arguments if they
>> want something "flakey".
>>
>> The following applies only to Access MDB, using ODBC drivers, to link
>> to an SQL server database:
>>
>> Relatively unchanging lookup tables are often kept local to an Access
>> application -- States in the US is a prime example, company structure
>> (divisions, departments) is another. I'd personally see no
>> need to migrate static data to the back-end and have to access it across
>> a possibly-slower-than-I'd-like network.
>>
>> Still, the client may have their own reasons (or even prejudices) and
>> ideas about what can reasonably be moved to the backend.
>>
>> Larry Linson Microsoft Access MVP
>>
>>
>>
>> "Jan" <jan@stempelconsulting.com> wrote in message
>> news:11v77m878qsla75@corp.supernews.com...
>>[color=darkred]
>>> Hi:
>>>
>>> I have an Access database that's been running (in one form or another)
>>> for a couple of different clients for a few years. Now a
>>> new client has requested that it be implemented with a SQL server
>>> back-end. I'm doing my best to learn about SQL server, and I plan
>>> to leave the front-end more or less as-is, just linking to the SQL
>>> server back end, but here's a basic question:
>>>
>>> The db has a front-end linked to two back-ends. One of the back-ends
>>> has completely static data, and so in an all-Access installation it sits
>>> on the C drive along with the front end. Only
>>> the 2nd backend sits on the server.
>>>
>>> Now, should I convert both back-ends to SQL server, or just the one
>>> on the server? Reasons, pros, cons?
>>>
>>> TIA.
>>>
>>> Jan[/color]
>>
>>[/color][/color]

re: How much to convert to SQL server

No transactions in this one.

david epsom dot com dot au wrote:[color=blue]
> If you are using transactions, you probably want to re-write
> any transactions that include the static tables. If you use
> local jet tables, the transactions are more likely to block
> internally, and if you use SQL Server tables, the transactions
> are likely to block other users.
>
> (david)
>
>
>
> "Jan" <jan@stempelconsulting.com> wrote in message
> news:11v7c5l7h5ig7dc@corp.supernews.com...
>[color=green]
>>Hi, Larry:
>>
>>Thanks for the quick reply.
>>
>>Unfortunately, the client isn't directly mine; I'm more or less a
>>subcontractor. I'm not sure they know exactly why they want SQL Server,
>>except that they think it's the "better" way. And I'm not in a position
>>to argue. They do have some issues with lots of users possibly spread
>>out over several offices.
>>
>>Anyway, I just wanted to be sure there wouldn't be some advantage to
>>having all the linked tables be converted to SQL Server. And yes, I'm
>>planning to use the ODBC link to the SQL Server backend. My preference
>>is certainly to keep as much of the data local, and in Access, as
>>possible, if only because it's what I know best.
>>
>>Anyone have any other views?
>>
>>Jan
>>
>>Larry Linson wrote:
>>[color=darkred]
>>>Your new client is the one who wants the conversion and, presumably,
>>> is paying you to convert the database. You should be asking the client
>>>what _they_ want, but be ready with logical arguments if they
>>> want something "flakey".
>>>
>>>The following applies only to Access MDB, using ODBC drivers, to link
>>> to an SQL server database:
>>>
>>>Relatively unchanging lookup tables are often kept local to an Access
>>> application -- States in the US is a prime example, company structure
>>>(divisions, departments) is another. I'd personally see no
>>> need to migrate static data to the back-end and have to access it across
>>>a possibly-slower-than-I'd-like network.
>>>
>>>Still, the client may have their own reasons (or even prejudices) and
>>> ideas about what can reasonably be moved to the backend.
>>>
>>>Larry Linson Microsoft Access MVP
>>>
>>>
>>>
>>>"Jan" <jan@stempelconsulting.com> wrote in message
>>>news:11v77m878qsla75@corp.supernews.com...
>>>
>>>
>>>>Hi:
>>>>
>>>>I have an Access database that's been running (in one form or another)
>>>>for a couple of different clients for a few years. Now a
>>>> new client has requested that it be implemented with a SQL server
>>>> back-end. I'm doing my best to learn about SQL server, and I plan
>>>> to leave the front-end more or less as-is, just linking to the SQL
>>>> server back end, but here's a basic question:
>>>>
>>>>The db has a front-end linked to two back-ends. One of the back-ends
>>>>has completely static data, and so in an all-Access installation it sits
>>>>on the C drive along with the front end. Only
>>>> the 2nd backend sits on the server.
>>>>
>>>>Now, should I convert both back-ends to SQL server, or just the one
>>>> on the server? Reasons, pros, cons?
>>>>
>>>>TIA.
>>>>
>>>>Jan
>>>
>>>[/color][/color]
>[/color]

re: How much to convert to SQL server

Hi, Rick:

This is a key one, I guess, because there are certainly queries that run
with some tables from each of the different sets. Most of the tables
are in the local set, but there are a crucial few that need to be on
the server. In the past, when it has been an entirely Access database,
I moved all the static tables to the local drive because it vastly
improved performance.

So do you think that with a SQL server backend I wouldn't run into the
slowness issues that I had with an all-Access db?

I have to say I'm really anxious about this whole conversion process.
The client is out of town and I have to make it work in a very short
time period when I'm out there. I can test it here on my machine, with
the "developers" version of SQL Server, but I worry that it isn't a good
proxy for the
"real thing."

Jan

Rick Brandt wrote:[color=blue]
> Jan wrote:
>[color=green]
>> Hi, Larry:
>>
>> Thanks for the quick reply.
>>
>> Unfortunately, the client isn't directly mine; I'm more or less a
>> subcontractor. I'm not sure they know exactly why they want SQL
>> Server, except that they think it's the "better" way. And I'm not
>> in a position to argue. They do have some issues with lots of
>> users possibly spread out over several offices.
>>
>> Anyway, I just wanted to be sure there wouldn't be some advantage
>> to having all the linked tables be converted to SQL Server. And
>> yes, I'm planning to use the ODBC link to the SQL Server backend.
>> My preference is certainly to keep as much of the data local, and
>> in Access, as possible, if only because it's what I know best.
>>
>> Anyone have any other views?[/color]
>
>
> Well even if you want to keep a few tables local (I see no reason to)
> you would often want another copy of the same table on the server if
> it is ever used in a query. You don't want to create queries in
> Access that use both a local table and a link to a server table. That
> would definitely be an inefficient query to run unless the local
> table was very small).
>
> In my Access FE/SQL Server BE apps ALL the tables are on the server
> and performance is just fine on a standard 100 mb LAN.
>[/color]

re: How much to convert to SQL server

"Jan" <jan@stempelconsulting.com> wrote in message
news:11v7mcfk0lp1a14@corp.supernews.com...[color=blue]
> Hi, Rick:
>
> This is a key one, I guess, because there are certainly queries that run
> with some tables from each of the different sets. Most of the tables
> are in the local set, but there are a crucial few that need to be on
> the server. In the past, when it has been an entirely Access database,
> I moved all the static tables to the local drive because it vastly
> improved performance.
>
> So do you think that with a SQL server backend I wouldn't run into the
> slowness issues that I had with an all-Access db?[/color]

If a query is slow because it is poorly designed then putting the tables on a
server won't magically fix that. If a query is slow because it is working
against very large tables then putting those tables on a server won't magically
cure that either.

There are many advantages to moving to a server-based data engine. Raw query
performance is not one of them. People often see performance gains when setting
up a new box for SQL Server because they will usually build that server with
high-spec'd hardware. Several years ago just about any server would be WAY more
capable than a desktop PC. That is still true, but not to the degree that it
once was because desktop PCs are simply very capable these days.

Client/Server performance is largely driven by minimizing traffic over the LAN
and good design. Those same strategies would likely result in an MDB Based
database that also performed well.


--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

re: How much to convert to SQL server

"Rick Brandt" <rickbrandt2@hotmail.com> wrote
[color=blue]
> Well even if you want to keep a few tables
> local (I see no reason to) you would
> often want another copy of the same table
> on the server if it is ever used in a
> query.[/color]

I specifically said, rarely-changing _lookup_ tables, with examples. I've
done this, with a master copy on the server, from which the local tables are
refreshed at startup, if need be. Running over a WAN, it is likely you'll
see some performance improvment if there are quite a few.
[color=blue]
> You don't want to create queries in Access that
> use both a local table and a link to a server table.
> That would definitely be an inefficient query to
> run unless the local table was very small).[/color]

I _strongly_ agree. In fact, even if the local table is very small, the
performance impact can be substantial because the entire table may be
brought from the server to the user's machine for the join. <OUCH!>
[color=blue]
> In my Access FE/SQL Server BE apps ALL
> the tables are on the server and performance is
> just fine on a standard 100 mb LAN.[/color]

Of course. But, move some of your users to the boonies on a WAN that shares
a T-1 line and you are likely to see a discernable difference. That's where
you may need to resort to "performance tricks".

If you have appropriate instrumentation, the difference may be "measurable"
on a 100 MBPS LAN, but it's unlikely to be discernable to someone sitting in
front of a screen. We didn't bother with local tables when everyone was on
the high-speed LAN.

Larry Linson
Microsoft Access MVP

re: How much to convert to SQL server

I don't know why exactly the database runs slow with the tables on the
server; I only know that when I moved them to the local disk, a
particular activity (which involves a lot of data manipulation, writing
to a Word document, and a variety of other tasks) went from taking 5
minutes down to less than one. I always blamed it on the slowness of
the client's network, but frankly didn't spend a lot of time
contemplating the reasons; the solution was very effective and I left it
at that.

Maybe this will help you understand the situation: the users are
entering data on rental units. The database is running a model that
involves manipulating a large amount of stored data on other rental
units (that's the static part; the research is done once and stays the
same for a year), but it has to be compared and calculations made in
order to come to some recommendations on the newly-entered unit.
Thus, most of the data is static, but that new record has to be involved.

Make any sense?

So maybe the question is this:
if I haven't had a performance hit from running queries that involved
both local and server tables in the past, would I have that hit when the
"server" tables are SQL Server and the local tables are Access?

Jan

Rick Brandt wrote:[color=blue]
> "Jan" <jan@stempelconsulting.com> wrote in message
> news:11v7mcfk0lp1a14@corp.supernews.com...
>[color=green]
>> Hi, Rick:
>>
>> This is a key one, I guess, because there are certainly queries
>> that run with some tables from each of the different sets. Most of
>> the tables are in the local set, but there are a crucial few that
>> need to be on the server. In the past, when it has been an
>> entirely Access database, I moved all the static tables to the
>> local drive because it vastly improved performance.
>>
>> So do you think that with a SQL server backend I wouldn't run into
>> the slowness issues that I had with an all-Access db?[/color]
>
>
> If a query is slow because it is poorly designed then putting the
> tables on a server won't magically fix that. If a query is slow
> because it is working against very large tables then putting those
> tables on a server won't magically cure that either.
>
> There are many advantages to moving to a server-based data engine.
> Raw query performance is not one of them. People often see
> performance gains when setting up a new box for SQL Server because
> they will usually build that server with high-spec'd hardware.
> Several years ago just about any server would be WAY more capable
> than a desktop PC. That is still true, but not to the degree that it
> once was because desktop PCs are simply very capable these days.
>
> Client/Server performance is largely driven by minimizing traffic
> over the LAN and good design. Those same strategies would likely
> result in an MDB Based database that also performed well.
>
>[/color]

re: How much to convert to SQL server

"Jan" <jan@stempelconsulting.com> wrote in message
news:11v7rq75bvk9t85@corp.supernews.com...[color=blue]
>I don't know why exactly the database runs slow with the tables on the
> server; I only know that when I moved them to the local disk, a
> particular activity (which involves a lot of data manipulation, writing
> to a Word document, and a variety of other tasks) went from taking 5
> minutes down to less than one. I always blamed it on the slowness of
> the client's network, but frankly didn't spend a lot of time
> contemplating the reasons; the solution was very effective and I left it
> at that.
>
> Maybe this will help you understand the situation: the users are
> entering data on rental units. The database is running a model that
> involves manipulating a large amount of stored data on other rental
> units (that's the static part; the research is done once and stays the
> same for a year), but it has to be compared and calculations made in
> order to come to some recommendations on the newly-entered unit.
> Thus, most of the data is static, but that new record has to be involved.
>
> Make any sense?
>
> So maybe the question is this:
> if I haven't had a performance hit from running queries that involved
> both local and server tables in the past, would I have that hit when the
> "server" tables are SQL Server and the local tables are Access?[/color]

Impossible to predict, but on the surface I can think of no reason for
performance to be worse. I just don't see much reason to believe it would be
any better either.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

re: How much to convert to SQL server

Well, it doesn't have to be better, it just has to be comparable.

Slightly different question here: will my testing on the local copy of
SQL server be in any way comparable to how the thing will behave in a
real production environment? I know that when I develop in general,
systems that work fast on my single-user machine can get very poky once
they hit the client's network. Is it the same with the developer's
edition of SQL server vs the full-blown one?

Rick Brandt wrote:[color=blue]
> "Jan" <jan@stempelconsulting.com> wrote in message
> news:11v7rq75bvk9t85@corp.supernews.com...
>[color=green]
>> I don't know why exactly the database runs slow with the tables on
>> the server; I only know that when I moved them to the local disk, a
>> particular activity (which involves a lot of data manipulation,
>> writing to a Word document, and a variety of other tasks) went
>> from taking 5 minutes down to less than one. I always blamed it
>> on the slowness of the client's network, but frankly didn't spend a
>> lot of time contemplating the reasons; the solution was very
>> effective and I left it at that.
>>
>> Maybe this will help you understand the situation: the users are
>> entering data on rental units. The database is running a model
>> that involves manipulating a large amount of stored data on other
>> rental units (that's the static part; the research is done once and
>> stays the same for a year), but it has to be compared and
>> calculations made in order to come to some recommendations on the
>> newly-entered unit. Thus, most of the data is static, but that new
>> record has to be involved.
>>
>> Make any sense?
>>
>> So maybe the question is this: if I haven't had a performance hit
>> from running queries that involved both local and server tables in
>> the past, would I have that hit when the "server" tables are SQL
>> Server and the local tables are Access?[/color]
>
>
> Impossible to predict, but on the surface I can think of no reason
> for performance to be worse. I just don't see much reason to believe
> it would be any better either.
>[/color]

re: How much to convert to SQL server

If it's been running for a few years undoubtedly it needs a complete
revision. Take the opportunity to trash the whole thing and start over.
I get sick to my stomach whenever I see anything of mine that is more
than two years old.

re: How much to convert to SQL server

I had a client ask me to substitue the Jet backend with SQL Server. The
logic they used for deciding which tables to put on SQL Server was:

"Our data needs to be on SQL Server".

Therefore, presumably data which was not theirs (like zip code tables, etc)
could remain local. Also, my proprietary tables could remain local as well.
Didn't do the job and have not thoroughly read the other posts in this thread.
Just passing on what their criteria was.


Jan wrote:[color=blue]
>No transactions in this one.
>[color=green]
>> If you are using transactions, you probably want to re-write
>> any transactions that include the static tables. If you use[/color]
>[quoted text clipped - 63 lines][color=green][color=darkred]
>>>>>
>>>>>Jan[/color][/color][/color]

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/200602/1

re: How much to convert to SQL server

Jan wrote:[color=blue]
> I don't know why exactly the database runs slow with the tables on the
> server; I only know that when I moved them to the local disk, a
> particular activity (which involves a lot of data manipulation, writing
> to a Word document, and a variety of other tasks) went from taking 5
> minutes down to less than one.[/color]

That's a phenomenally long time. I call that coffee-break coding...as
in I want to run the process but take a break.

If I'm not getting results back in 5 seconds (absolute tops) or less.
then there is a problem. If course, I'm not dealing with millions of
records, but for a typical desktop app 5 minutes is way, way too long.

Back in the old days I'd immediately suspect a field I am filtering on,
in a where clause, is not indexed. But in Access where I see people
discussing their coffee-break queries they usually are doing sub-selects
in the query. That's nearly a guaranteed yawner of a query.

I suspect that most regulars in this newsgroup could get your query down
to a couple of seconds if they were working on your application.

I always blamed it on the slowness of[color=blue]
> the client's network, but frankly didn't spend a lot of time
> contemplating the reasons; the solution was very effective and I left it
> at that.[/color]

Time is money. That's why there's a FedEx. If I can shave 5 minutes of
processing on something that is done every weekday day by 10 users, in a
year the time saving is about 210 hours. You do that for a few more
processes and you're taking some significant time.[color=blue]
>
> Maybe this will help you understand the situation: the users are
> entering data on rental units. The database is running a model that
> involves manipulating a large amount of stored data on other rental
> units (that's the static part; the research is done once and stays the
> same for a year), but it has to be compared and calculations made in
> order to come to some recommendations on the newly-entered unit.
> Thus, most of the data is static, but that new record has to be involved.
>
> Make any sense?
>
> So maybe the question is this:
> if I haven't had a performance hit from running queries that involved
> both local and server tables in the past, would I have that hit when the
> "server" tables are SQL Server and the local tables are Access?
>[/color]
Well, I can see why the client may want to go to SQL Server, anything
that may help cut down that 5 minute processing time. I simply don't
see SQLServer as the optimum solution. If the time lag was bad before,
why will it be better with SQLServer? Doubtful. Will your client be
happy if, instead of a 5 minute lag time, you cut it down to 4 minutes?
I wouldn't. The more instantaneous you do something, the better.

You do mention a "large amount of data". That means nothing to me or
many developers. Does that mean millions of records? Or does it mean a
few thousand records with a couple hundred fields?

I would really look at your queries. If you use subselects in any of
them, change tactics. Check your indexing and filtering. Do some time
tests.


[color=blue]
> Jan[/color]

re: How much to convert to SQL server

Lyle Fairfield wrote:[color=blue]
> I get sick to my stomach whenever I see anything of mine that
> is more than two years old.[/color]

I'm like that...
--
regards,

Br@dley

re: How much to convert to SQL server

I don't know about different editions, but
nay code runs much faster against the SQL Server
installed on my PC than it does running against
the SQL Server in another building in another
suburb on another subnet with a different name
server with the other production databases
also loaded.

(david)

"Jan" <jan@stempelconsulting.com> wrote in message
news:11v7vd761o25u12@corp.supernews.com...[color=blue]
> Well, it doesn't have to be better, it just has to be comparable.
>
> Slightly different question here: will my testing on the local copy of
> SQL server be in any way comparable to how the thing will behave in a
> real production environment? I know that when I develop in general,
> systems that work fast on my single-user machine can get very poky once
> they hit the client's network. Is it the same with the developer's
> edition of SQL server vs the full-blown one?
>
> Rick Brandt wrote:[color=green]
>> "Jan" <jan@stempelconsulting.com> wrote in message
>> news:11v7rq75bvk9t85@corp.supernews.com...
>>[color=darkred]
>>> I don't know why exactly the database runs slow with the tables on
>>> the server; I only know that when I moved them to the local disk, a
>>> particular activity (which involves a lot of data manipulation,
>>> writing to a Word document, and a variety of other tasks) went
>>> from taking 5 minutes down to less than one. I always blamed it
>>> on the slowness of the client's network, but frankly didn't spend a
>>> lot of time contemplating the reasons; the solution was very
>>> effective and I left it at that.
>>>
>>> Maybe this will help you understand the situation: the users are
>>> entering data on rental units. The database is running a model
>>> that involves manipulating a large amount of stored data on other
>>> rental units (that's the static part; the research is done once and
>>> stays the same for a year), but it has to be compared and
>>> calculations made in order to come to some recommendations on the
>>> newly-entered unit. Thus, most of the data is static, but that new
>>> record has to be involved.
>>>
>>> Make any sense?
>>>
>>> So maybe the question is this: if I haven't had a performance hit
>>> from running queries that involved both local and server tables in
>>> the past, would I have that hit when the "server" tables are SQL
>>> Server and the local tables are Access?[/color]
>>
>>
>> Impossible to predict, but on the surface I can think of no reason
>> for performance to be worse. I just don't see much reason to believe
>> it would be any better either.
>>[/color][/color]

re: How much to convert to SQL server

'Theoretically' anything can happen. Using a mixture of
local tables and ODBC links, Access is unable to do a
number of optimisations.

For example, if Access can't convert all of your nested
queries into a single SQL statement, it will run separate
SQL queries against the ODBC source. This never happens
with all-jet tables or with all-SS stored procedures:
Jet doesn't run SQL internally, and neither does SQL Server,
so there is no reason to construct separate SQL statements
for complex nested queries.

On the other hand, from what you say, you might be returning
a single record from your SQL Server, and matching that
to your cloud of static data. There is no reason to expect
that returning a single record from SQL Server will take
any longer than returning a single record from a File Server.

(david)



"Jan" <jan@stempelconsulting.com> wrote in message
news:11v7rq75bvk9t85@corp.supernews.com...[color=blue]
>I don't know why exactly the database runs slow with the tables on the
> server; I only know that when I moved them to the local disk, a
> particular activity (which involves a lot of data manipulation, writing
> to a Word document, and a variety of other tasks) went from taking 5
> minutes down to less than one. I always blamed it on the slowness of
> the client's network, but frankly didn't spend a lot of time
> contemplating the reasons; the solution was very effective and I left it
> at that.
>
> Maybe this will help you understand the situation: the users are
> entering data on rental units. The database is running a model that
> involves manipulating a large amount of stored data on other rental
> units (that's the static part; the research is done once and stays the
> same for a year), but it has to be compared and calculations made in
> order to come to some recommendations on the newly-entered unit.
> Thus, most of the data is static, but that new record has to be involved.
>
> Make any sense?
>
> So maybe the question is this:
> if I haven't had a performance hit from running queries that involved
> both local and server tables in the past, would I have that hit when the
> "server" tables are SQL Server and the local tables are Access?
>
> Jan
>[/color]

re: How much to convert to SQL server

"Br@dley" <no@mail.com.au> wrote in message
news:dt146h$cll$1@news-02.connect.com.au...[color=blue]
> Lyle Fairfield wrote:[color=green]
>> I get sick to my stomach whenever I see anything of mine that
>> is more than two years old.[/color]
>
> I'm like that...
> --
> regards,
>
> Br@dley[/color]


I'm like that with my wife.

re: How much to convert to SQL server

"Lyle Fairfield" <lylefairfield@aim.com> wrote in message
news:1140065029.518318.171040@g47g2000cwa.googlegr oups.com...[color=blue]
> If it's been running for a few years undoubtedly it needs a complete
> revision. Take the opportunity to trash the whole thing and start over.
> I get sick to my stomach whenever I see anything of mine that is more
> than two years old.[/color]

I'm impressed. For me 6 months is usually enough :-)

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

re: How much to convert to SQL server

This is a very interesting subject that I am also involved in, but
which I don't fully understand. I have some questions and some
answers.....

1. I have a database program in Access with FE on workstations and BE
on a file server. It works fine. But I've never optimized anything for
anything, I just did it !!

2. I need to convert the BE to mySQL, doesn't matter why for now. If I
use ODBC and just link my existing FE without changing anything will
performance be better, worse or the same? My initial experiments
suggests that -- it depends what you are doing and what table type you
use. If I choose myISAM table type then most operations are fast,
especially ones where you are trying to display a single record, or run
a simple query. Faster than Access?, not really. The problem is with
more complex queries - they can take forever but I haven't got to the
bottom of this yet. As a workaround I copy the necessary data to the
local machine and work on it there - it works for me but it's not
exactly elegant.

3. I hear people talking about optimizing FE's to use SQL Server but
what does that mean? Does it mean using something other that ODBC and
linking to BE that we are all used to? Or does it mean adopting a
totally different approach.

4. Am I right in assuming that the objective is to move as little data
as possible across the network, and that a "proper" client-server
approach is the way to go? And if that's right then is an Access FE and
SQL BE doing that or not?

If anyone can throw any light on this ramble I for one would love to
hear it

Thanks
Dave

re: How much to convert to SQL server

Thanks for your comments, but did you see the rest of what I said? Once
I moved all the static tables to the local drive, the process took less
than a minute. I believe that some of the slowness has to do with the
fact that this process is creating a complicated document in Word. Any
thoughts on whether I should convert the local tables to SQL Server when
I convert the ones on the server?

salad wrote:[color=blue]
> Jan wrote:
>[color=green]
>> I don't know why exactly the database runs slow with the tables on
>> the server; I only know that when I moved them to the local disk, a
>> particular activity (which involves a lot of data manipulation,
>> writing to a Word document, and a variety of other tasks) went
>> from taking 5 minutes down to less than one.[/color]
>
>
> That's a phenomenally long time. I call that coffee-break
> coding...as in I want to run the process but take a break.
>
> If I'm not getting results back in 5 seconds (absolute tops) or less.
> then there is a problem. If course, I'm not dealing with millions
> of records, but for a typical desktop app 5 minutes is way, way too
> long.
>
> Back in the old days I'd immediately suspect a field I am filtering
> on, in a where clause, is not indexed. But in Access where I see
> people discussing their coffee-break queries they usually are doing
> sub-selects in the query. That's nearly a guaranteed yawner of a
> query.
>
> I suspect that most regulars in this newsgroup could get your query
> down to a couple of seconds if they were working on your application.
>
>
> I always blamed it on the slowness of
>[color=green]
>> the client's network, but frankly didn't spend a lot of time
>> contemplating the reasons; the solution was very effective and I
>> left it at that.[/color]
>
>
> Time is money. That's why there's a FedEx. If I can shave 5 minutes
> of processing on something that is done every weekday day by 10
> users, in a year the time saving is about 210 hours. You do that for
> a few more processes and you're taking some significant time.
>[color=green]
>>
>> Maybe this will help you understand the situation: the users are
>> entering data on rental units. The database is running a model
>> that involves manipulating a large amount of stored data on other
>> rental units (that's the static part; the research is done once and
>> stays the same for a year), but it has to be compared and
>> calculations made in order to come to some recommendations on the
>> newly-entered unit. Thus, most of the data is static, but that new
>> record has to be involved.
>>
>> Make any sense?
>>
>> So maybe the question is this: if I haven't had a performance hit
>> from running queries that involved both local and server tables in
>> the past, would I have that hit when the "server" tables are SQL
>> Server and the local tables are Access?
>>[/color]
> Well, I can see why the client may want to go to SQL Server, anything
> that may help cut down that 5 minute processing time. I simply
> don't see SQLServer as the optimum solution. If the time lag was bad
> before, why will it be better with SQLServer? Doubtful. Will your
> client be happy if, instead of a 5 minute lag time, you cut it down
> to 4 minutes? I wouldn't. The more instantaneous you do something,
> the better.
>
> You do mention a "large amount of data". That means nothing to me or
> many developers. Does that mean millions of records? Or does it
> mean a few thousand records with a couple hundred fields?
>
> I would really look at your queries. If you use subselects in any of
> them, change tactics. Check your indexing and filtering. Do some
> time tests.
>
>
>[color=green]
>> Jan[/color][/color]

re: How much to convert to SQL server

Hi, Larry:

Regarding your last point about "the entire table may be brought from
the server..." so-- if I can make sure that the db is dealing with only
one record from the server table when working with the local tables, I
should be ok? I know this is possible, although I can't remember at the
moment if it's completely implemented in the current version.

Also, I've always had the "local" tables in a separate backend on the C:
drive, just because I'm so stuck in the frontend-backend model that it
didn't occur to me to actually put them in the front end! We're talking
a lot of tables here: close to a couple dozen, and they literally never
change except in the annual overhaul, which is done in an entirely
different process that the end-user doesn't see. (FYI, there are only
about
6 tables that need to stay on the server.)

Jan

Larry Linson wrote:[color=blue]
> "Rick Brandt" <rickbrandt2@hotmail.com> wrote
>[color=green]
>> Well even if you want to keep a few tables local (I see no reason
>> to) you would often want another copy of the same table on the
>> server if it is ever used in a query.[/color]
>
> I specifically said, rarely-changing _lookup_ tables, with examples.
> I've done this, with a master copy on the server, from which the
> local tables are refreshed at startup, if need be. Running over a
> WAN, it is likely you'll see some performance improvment if there are
> quite a few.
>[color=green]
>> You don't want to create queries in Access that use both a local
>> table and a link to a server table. That would definitely be an
>> inefficient query to run unless the local table was very small).[/color]
>
> I _strongly_ agree. In fact, even if the local table is very small,
> the performance impact can be substantial because the entire table
> may be brought from the server to the user's machine for the join.
> <OUCH!>
>[color=green]
>> In my Access FE/SQL Server BE apps ALL the tables are on the server
>> and performance is just fine on a standard 100 mb LAN.[/color]
>
> Of course. But, move some of your users to the boonies on a WAN that
> shares a T-1 line and you are likely to see a discernable difference.
> That's where you may need to resort to "performance tricks".
>
> If you have appropriate instrumentation, the difference may be
> "measurable" on a 100 MBPS LAN, but it's unlikely to be discernable
> to someone sitting in front of a screen. We didn't bother with local
> tables when everyone was on the high-speed LAN.
>
> Larry Linson Microsoft Access MVP
>
>[/color]

re: How much to convert to SQL server

Well, actually I've done a lot of that in the past couple of years. It
improves every time we revise it for a new client. I'm glad to know I'm
not the only one who has that reaction to past work.

Lyle Fairfield wrote:[color=blue]
> If it's been running for a few years undoubtedly it needs a complete
> revision. Take the opportunity to trash the whole thing and start
> over. I get sick to my stomach whenever I see anything of mine that
> is more than two years old.
>[/color]

re: How much to convert to SQL server

Hi, David:

I was afraid of that. It makes it really hard to troubleshoot, doesn't it?

david epsom dot com dot au wrote:[color=blue]
> I don't know about different editions, but nay code runs much faster
> against the SQL Server installed on my PC than it does running
> against the SQL Server in another building in another suburb on
> another subnet with a different name server with the other production
> databases also loaded.
>
> (david)
>
> "Jan" <jan@stempelconsulting.com> wrote in message
> news:11v7vd761o25u12@corp.supernews.com...
>[color=green]
>> Well, it doesn't have to be better, it just has to be comparable.
>>
>> Slightly different question here: will my testing on the local
>> copy of SQL server be in any way comparable to how the thing will
>> behave in a real production environment? I know that when I develop
>> in general, systems that work fast on my single-user machine can
>> get very poky once they hit the client's network. Is it the same
>> with the developer's edition of SQL server vs the full-blown one?
>>
>> Rick Brandt wrote:
>>[color=darkred]
>>> "Jan" <jan@stempelconsulting.com> wrote in message
>>> news:11v7rq75bvk9t85@corp.supernews.com...
>>>
>>>
>>>> I don't know why exactly the database runs slow with the tables
>>>> on the server; I only know that when I moved them to the local
>>>> disk, a particular activity (which involves a lot of data
>>>> manipulation, writing to a Word document, and a variety of
>>>> other tasks) went from taking 5 minutes down to less than one.
>>>> I always blamed it on the slowness of the client's network, but
>>>> frankly didn't spend a lot of time contemplating the reasons;
>>>> the solution was very effective and I left it at that.
>>>>
>>>> Maybe this will help you understand the situation: the users
>>>> are entering data on rental units. The database is running a
>>>> model that involves manipulating a large amount of stored data
>>>> on other rental units (that's the static part; the research is
>>>> done once and stays the same for a year), but it has to be
>>>> compared and calculations made in order to come to some
>>>> recommendations on the newly-entered unit. Thus, most of the
>>>> data is static, but that new record has to be involved.
>>>>
>>>> Make any sense?
>>>>
>>>> So maybe the question is this: if I haven't had a performance
>>>> hit from running queries that involved both local and server
>>>> tables in the past, would I have that hit when the "server"
>>>> tables are SQL Server and the local tables are Access?
>>>
>>>
>>> Impossible to predict, but on the surface I can think of no
>>> reason for performance to be worse. I just don't see much reason
>>> to believe it would be any better either.
>>>[/color][/color]
>
>
>[/color]

re: How much to convert to SQL server

Jan wrote:[color=blue]
> Thanks for your comments, but did you see the rest of what I said? Once
> I moved all the static tables to the local drive, the process took less
> than a minute.[/color]

I still find a minute exceptionally long.

I believe that some of the slowness has to do with the[color=blue]
> fact that this process is creating a complicated document in Word. Any
> thoughts on whether I should convert the local tables to SQL Server when
> I convert the ones on the server?[/color]

I usually look at my bottlenecks. What processes are fast, which are
slow? I address the processes that are slow. I look at my code and put
in breakpoints to determine where something might be slow. Once I know
what's slow, I know what to fix.

For example, you say that the word document is slow. But you really
don't know. If I were the owner of the company, I'd get myself a Word
guru as a consultant. Someone that really knows their stuff. And have
them look at the document and see how I could get that document to mail
merge as fast as possible. I might do this before I rework the entire
app and bring in SQLServer. A few hours with a Word guru will be a heck
of a lot cheaper.

I remember taking over an app (not done in Access) years ago where a few
reports, run daily, each would take over 30 minutes to process. I got
them down to the 2 to 3 second range. I was saving about 4 hours a day
in processing time just in fixing up those reports. The rest of the
system was built for the developer, not for the end-user comfort. So
the entire system ran slow and crashed a lot. What amazed me is that
the client even employed the developer...he was atrocious. I think he
felt sorry for the developer. Oh well, the client went off into
tangents like web stuff and poor database planning (ex: going from
relational to flat file processing) and utilized that developer for the
new technology and didn't focus on what he knew best. He's now out of
business.

Time is of the essence.

If this is coming over a WAN, then there's not much to expect but slow.
[color=blue]
>
> salad wrote:
>[color=green]
>> Jan wrote:
>>[color=darkred]
>>> I don't know why exactly the database runs slow with the tables on
>>> the server; I only know that when I moved them to the local disk, a
>>> particular activity (which involves a lot of data manipulation,
>>> writing to a Word document, and a variety of other tasks) went
>>> from taking 5 minutes down to less than one.[/color]
>>
>>
>>
>> That's a phenomenally long time. I call that coffee-break
>> coding...as in I want to run the process but take a break.
>>
>> If I'm not getting results back in 5 seconds (absolute tops) or less.
>> then there is a problem. If course, I'm not dealing with millions
>> of records, but for a typical desktop app 5 minutes is way, way too
>> long.
>>
>> Back in the old days I'd immediately suspect a field I am filtering
>> on, in a where clause, is not indexed. But in Access where I see
>> people discussing their coffee-break queries they usually are doing
>> sub-selects in the query. That's nearly a guaranteed yawner of a
>> query.
>>
>> I suspect that most regulars in this newsgroup could get your query
>> down to a couple of seconds if they were working on your application.
>>
>>
>> I always blamed it on the slowness of
>>[color=darkred]
>>> the client's network, but frankly didn't spend a lot of time
>>> contemplating the reasons; the solution was very effective and I
>>> left it at that.[/color]
>>
>>
>>
>> Time is money. That's why there's a FedEx. If I can shave 5 minutes
>> of processing on something that is done every weekday day by 10
>> users, in a year the time saving is about 210 hours. You do that for
>> a few more processes and you're taking some significant time.
>>[color=darkred]
>>>
>>> Maybe this will help you understand the situation: the users are
>>> entering data on rental units. The database is running a model
>>> that involves manipulating a large amount of stored data on other
>>> rental units (that's the static part; the research is done once and
>>> stays the same for a year), but it has to be compared and
>>> calculations made in order to come to some recommendations on the
>>> newly-entered unit. Thus, most of the data is static, but that new
>>> record has to be involved.
>>>
>>> Make any sense?
>>>
>>> So maybe the question is this: if I haven't had a performance hit
>>> from running queries that involved both local and server tables in
>>> the past, would I have that hit when the "server" tables are SQL
>>> Server and the local tables are Access?
>>>[/color]
>> Well, I can see why the client may want to go to SQL Server, anything
>> that may help cut down that 5 minute processing time. I simply
>> don't see SQLServer as the optimum solution. If the time lag was bad
>> before, why will it be better with SQLServer? Doubtful. Will your
>> client be happy if, instead of a 5 minute lag time, you cut it down
>> to 4 minutes? I wouldn't. The more instantaneous you do something,
>> the better.
>>
>> You do mention a "large amount of data". That means nothing to me or
>> many developers. Does that mean millions of records? Or does it
>> mean a few thousand records with a couple hundred fields?
>>
>> I would really look at your queries. If you use subselects in any of
>> them, change tactics. Check your indexing and filtering. Do some
>> time tests.
>>
>>
>>[color=darkred]
>>> Jan[/color][/color][/color]

re: How much to convert to SQL server

I've got complex Word automation mail merge code.
It used to take 45 minutes to print 45 sets.

Then the client got a faster computer and printer,
and it took 45 minutes to print 75 sets.

Then the client got a faster computer and printer,
Then the client got a faster computer and printer,
and it now takes 20 minutes to print 150 sets


When I see the printer working without a print queue,
then it will be time to optimise my mail merge code.

Either that, or the client will buy a color laser.

(david)

"Jan" <jan@stempelconsulting.com> wrote in message
news:11v8t99ol0rp8f8@corp.supernews.com...[color=blue]
> Thanks for your comments, but did you see the rest of what I said? Once
> I moved all the static tables to the local drive, the process took less
> than a minute. I believe that some of the slowness has to do with the
> fact that this process is creating a complicated document in Word. Any
> thoughts on whether I should convert the local tables to SQL Server when
> I convert the ones on the server?
>
> salad wrote:[color=green]
>> Jan wrote:
>>[color=darkred]
>>> I don't know why exactly the database runs slow with the tables on
>>> the server; I only know that when I moved them to the local disk, a
>>> particular activity (which involves a lot of data manipulation,
>>> writing to a Word document, and a variety of other tasks) went
>>> from taking 5 minutes down to less than one.[/color]
>>
>>
>> That's a phenomenally long time. I call that coffee-break
>> coding...as in I want to run the process but take a break.
>>
>> If I'm not getting results back in 5 seconds (absolute tops) or less.
>> then there is a problem. If course, I'm not dealing with millions
>> of records, but for a typical desktop app 5 minutes is way, way too
>> long.
>>
>> Back in the old days I'd immediately suspect a field I am filtering
>> on, in a where clause, is not indexed. But in Access where I see
>> people discussing their coffee-break queries they usually are doing
>> sub-selects in the query. That's nearly a guaranteed yawner of a
>> query.
>>
>> I suspect that most regulars in this newsgroup could get your query
>> down to a couple of seconds if they were working on your application.
>>
>>
>> I always blamed it on the slowness of
>>[color=darkred]
>>> the client's network, but frankly didn't spend a lot of time
>>> contemplating the reasons; the solution was very effective and I
>>> left it at that.[/color]
>>
>>
>> Time is money. That's why there's a FedEx. If I can shave 5 minutes
>> of processing on something that is done every weekday day by 10
>> users, in a year the time saving is about 210 hours. You do that for
>> a few more processes and you're taking some significant time.
>>[color=darkred]
>>>
>>> Maybe this will help you understand the situation: the users are
>>> entering data on rental units. The database is running a model
>>> that involves manipulating a large amount of stored data on other
>>> rental units (that's the static part; the research is done once and
>>> stays the same for a year), but it has to be compared and
>>> calculations made in order to come to some recommendations on the
>>> newly-entered unit. Thus, most of the data is static, but that new
>>> record has to be involved.
>>>
>>> Make any sense?
>>>
>>> So maybe the question is this: if I haven't had a performance hit
>>> from running queries that involved both local and server tables in
>>> the past, would I have that hit when the "server" tables are SQL
>>> Server and the local tables are Access?
>>>[/color]
>> Well, I can see why the client may want to go to SQL Server, anything
>> that may help cut down that 5 minute processing time. I simply
>> don't see SQLServer as the optimum solution. If the time lag was bad
>> before, why will it be better with SQLServer? Doubtful. Will your
>> client be happy if, instead of a 5 minute lag time, you cut it down
>> to 4 minutes? I wouldn't. The more instantaneous you do something,
>> the better.
>>
>> You do mention a "large amount of data". That means nothing to me or
>> many developers. Does that mean millions of records? Or does it
>> mean a few thousand records with a couple hundred fields?
>>
>> I would really look at your queries. If you use subselects in any of
>> them, change tactics. Check your indexing and filtering. Do some
>> time tests.
>>
>>
>>[color=darkred]
>>> Jan[/color][/color][/color]

re: How much to convert to SQL server

salad <oil@vinegar.com> wrote in
news:kTTIf.19282$vU2.5607@newsread3.news.atl.earth link.net:
[color=blue]
> Back in the old days I'd immediately suspect a field I am
> filtering on, in a where clause, is not indexed. But in Access
> where I see people discussing their coffee-break queries they
> usually are doing sub-selects in the query. That's nearly a
> guaranteed yawner of a query.[/color]

Not necessarily. If the subselect is happening first, yes, it will
probably be slow. But if there is other filtering of the recordset
that can be applied before the subselect runs, it's often going to
be very fast.

I just don't know where people get the idea that all subselects are
slow. Correlated subqueries, sure, of course they are, because they
have to run for every row of the recordsource, but other types of
subselects are not by definition slow. They can be incredibly useful
tools.

IN clauses can often be replaced with joins to "virtual tables"
(i.e., a subselect treated as a data source), and those are often
very fast, too.

IN clauses are a little unpredictable sometimes in regard to when
the indexes on both sides are used or not, but most of the time,
they are.

I am less convinced of the utility and performance advantages of
other types of subselects (like EXISTS and so forth), because they
are just not useful as often as an IN clause subselect (which is the
best way to get editable recordsets from uneditable equivalent
recordsets that use joins).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

re: How much to convert to SQL server

"Lyle Fairfield" <lylefairfield@aim.com> wrote in
news:1140065029.518318.171040@g47g2000cwa.googlegr oups.com:
[color=blue]
> If it's been running for a few years undoubtedly it needs a
> complete revision. Take the opportunity to trash the whole thing
> and start over. I get sick to my stomach whenever I see anything
> of mine that is more than two years old.[/color]

While my old code often dismays me (since I'm constantly learning
better ways to accomplish certain tasks), I would never, ever under
any circumstances chuck a working application and rewrite it.

That is stupidity of the highest order.

If one needs to extend an existing application, then it's often
profitable to refactor the existing app to bring it up to current
coding standards, and then re-engineer after the refactoring to
enable the addition of the extended features.

But I'd never refactor just because I was offended by the code. If
it works, it works. Any changes you make are likely to introduce
bugs, which means that you've changed working code into non-working
code.

That's why Netscape basically no longer exists -- they did exactly
that, chucked their existing codebase, which was chockfull of both
badly engineered code but also with code that included a lot of
hardwon solutions to really bad problems (like bug fixes for
specific platforms). In starting from scratch, Netscape lost 5 years
when they just didn't have a product to offer.

DON'T DINK WITH WORKING CODE.

Ever.

No matter how offensive it may be.

I have a client app, still in production use, that is 10 years old
this spring. It uses a lot of macros. But I'm not about to go in and
try to convert it to not use macros BECAUSE IT WORKS JUST FINE.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

re: How much to convert to SQL server

This is how I handle the reporting over a WAN with a similar situation:

1. The query on the BE returns only the minimal amount of data, raw and
unformatted - often foreign keys to the bulky, static table that are stored
in the FE - the ones that as you say, rarely if ever change.

2. The query is sent to a table in the FE - I don't use temp tables,
instead, I have several tables just to store the date - cleverly named like
"Report_LLiDD".

3. My reports' then have as their record source a large query which brings
in several of these 'static' or 'lookup' tables from the FE, the newly
filled table of dynamic data from the BE, and all those formatting commands.


--
Darryl Kerkeslager


"Jan" <jan@stempelconsulting.com> wrote[color=blue]
> Also, I've always had the "local" tables in a separate backend on the C:
> drive, just because I'm so stuck in the frontend-backend model that it
> didn't occur to me to actually put them in the front end! We're talking
> a lot of tables here: close to a couple dozen, and they literally never
> change except in the annual overhaul, which is done in an entirely
> different process that the end-user doesn't see. (FYI, there are only
> about
> 6 tables that need to stay on the server.)
>[/color]