Access project vs. Access db

[Brian]

I'm just beginning to experiment with upsizing from .mdb's to SQL using the
upsizing wizard. One specific question, one general question:

1. This line give me an "Invalid column name 'True'" error. Why? Active is
the name of a Yes/No field that upsized OK. I can look at the table and see
it just fine.

If DCount("[UserID]", "[UserRoles]", "[UserID] = '" & [UserID] & "' and
[Role] = 'Application' and Active = True") > 0

2. Where can I get general/ongoing help with what may be a host of these
little syntax (?) changes when migrating from Access fe/be to Access project
/ SQL?

 

['69 Camaro]

Hi, Brian.

1. This line give me an "Invalid column name 'True'" error. Why?

Because the syntax you are using is asking the database engine to compare
the value in the "Active" column with the value in the "True" column for each
record. Unless you actually had a field in your MDB database table named
"True" holding a Boolean value, the block of code below this IF statement
will never execute, because no records will ever meet the condition "Active =
True." Jet won't warn you of this logic error, but SQL Server does.

2. Where can I get general/ongoing help with what may be a host of these
little syntax (?) changes when migrating from Access fe/be to Access project
/ SQL?

Download the SQL Server Books Online (BOL) on the following Web page:

http://www.microsoft.com/downloads/...b1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en

As a quick reference, please see the following Web page for Jet 4.0 Reserved
words to avoid, which are ANSI SQL-92 compliant, which is used for SQL Server
2000 and MSDE:

http://support.microsoft.com/?id=321266

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

I'm just beginning to experiment with upsizing from .mdb's to SQL using the
upsizing wizard. One specific question, one general question:

1. This line give me an "Invalid column name 'True'" error. Why? Active is
the name of a Yes/No field that upsized OK. I can look at the table and see
it just fine.

If DCount("[UserID]", "[UserRoles]", "[UserID] = '" & [UserID] & "' and
[Role] = 'Application' and Active = True") > 0

2. Where can I get general/ongoing help with what may be a host of these
little syntax (?) changes when migrating from Access fe/be to Access project
/ SQL?

 

[Brian]

Thanks. Good info at those links. There are times when I wish Access was not
so forgiving about accomodating these things because it lets me get into bad
habits that can be hard to break later. I will probably find the answer in
the SQL docs, but in the meantime, what is the correct syntax for Boolean
checks? The field in my example is a Yes/No field called Active, and I
already tried leaving out the " = True" (i.e. just "If Active...", which I
use successfully throughout my current Access mdb fe/be structure), but got
an error on this also.

Also, since I am just starting to migrate and have the opportunity to go
with either MSDE 2000 or SQL Express 2005, have you any opinions on
whether/when one is prefereable to the other as a backend?

Hi, Brian.

1. This line give me an "Invalid column name 'True'" error. Why?

Because the syntax you are using is asking the database engine to compare
the value in the "Active" column with the value in the "True" column for each
record. Unless you actually had a field in your MDB database table named
"True" holding a Boolean value, the block of code below this IF statement
will never execute, because no records will ever meet the condition "Active =
True." Jet won't warn you of this logic error, but SQL Server does.

2. Where can I get general/ongoing help with what may be a host of these
little syntax (?) changes when migrating from Access fe/be to Access project
/ SQL?

Download the SQL Server Books Online (BOL) on the following Web page:

http://www.microsoft.com/downloads/...b1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en

As a quick reference, please see the following Web page for Jet 4.0 Reserved
words to avoid, which are ANSI SQL-92 compliant, which is used for SQL Server
2000 and MSDE:

http://support.microsoft.com/?id=321266

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

I'm just beginning to experiment with upsizing from .mdb's to SQL using the
upsizing wizard. One specific question, one general question:

1. This line give me an "Invalid column name 'True'" error. Why? Active is
the name of a Yes/No field that upsized OK. I can look at the table and see
it just fine.

If DCount("[UserID]", "[UserRoles]", "[UserID] = '" & [UserID] & "' and
[Role] = 'Application' and Active = True") > 0

2. Where can I get general/ongoing help with what may be a host of these
little syntax (?) changes when migrating from Access fe/be to Access project
/ SQL?

 

['69 Camaro]

Hi, Brian.

what is the correct syntax for Boolean
checks?

Try:

If DCount("[UserID]", "[UserRoles]", "[UserID] = '" & [UserId] & _
"' and [Role] = 'Application' and Active = " & True) > 0 Then

have you any opinions on
whether/when one is prefereable to the other as a backend?

I haven't used SQL Express 2005 (beta) yet, but I know the capabilities are
superior to MSDE, which is six year old technology. However, using an ADP
instead of an MDB limits the capabilities of the front-end database
application. Code and techniques that work fine in an MDB don't always work
in an ADP. If you have a somewhat complex application, you are going to make
some aggravating discoveries after you migrate. If it's possible to link the
tables to SQL Express 2005 (I don't know as I haven't tried), then go that
route with an MDB database file instead. And if you can't, I'd recommend a
client/server database, such as SQL Server or Oracle to link the tables to
from the MDB front-end.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

Thanks. Good info at those links. There are times when I wish Access was not
so forgiving about accomodating these things because it lets me get into bad
habits that can be hard to break later. I will probably find the answer in
the SQL docs, but in the meantime, what is the correct syntax for Boolean
checks? The field in my example is a Yes/No field called Active, and I
already tried leaving out the " = True" (i.e. just "If Active...", which I
use successfully throughout my current Access mdb fe/be structure), but got
an error on this also.

Also, since I am just starting to migrate and have the opportunity to go
with either MSDE 2000 or SQL Express 2005, have you any opinions on
whether/when one is prefereable to the other as a backend?

Hi, Brian.

1. This line give me an "Invalid column name 'True'" error. Why?

Because the syntax you are using is asking the database engine to compare
the value in the "Active" column with the value in the "True" column for each
record. Unless you actually had a field in your MDB database table named
"True" holding a Boolean value, the block of code below this IF statement
will never execute, because no records will ever meet the condition "Active =
True." Jet won't warn you of this logic error, but SQL Server does.

2. Where can I get general/ongoing help with what may be a host of these
little syntax (?) changes when migrating from Access fe/be to Access project
/ SQL?

Download the SQL Server Books Online (BOL) on the following Web page:

http://www.microsoft.com/downloads/...b1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en

As a quick reference, please see the following Web page for Jet 4.0 Reserved
words to avoid, which are ANSI SQL-92 compliant, which is used for SQL Server
2000 and MSDE:

http://support.microsoft.com/?id=321266

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

I'm just beginning to experiment with upsizing from .mdb's to SQL using the
upsizing wizard. One specific question, one general question:

1. This line give me an "Invalid column name 'True'" error. Why? Active is
the name of a Yes/No field that upsized OK. I can look at the table and see
it just fine.

If DCount("[UserID]", "[UserRoles]", "[UserID] = '" & [UserID] & "' and
[Role] = 'Application' and Active = True") > 0

2. Where can I get general/ongoing help with what may be a host of these
little syntax (?) changes when migrating from Access fe/be to Access project
/ SQL?

 

[Brian]

Thank you again. You are so correct about the aggravating discoveries - I
have a LOT of time invested in my Access MDB fe's, and do not relish the idea
of rebuilding them as ADP's. I was anticipating a lot of work transferring
functionality from the front end to stored procedures.

The main reason I am migrating is the severe network latency I experience
with multiple users connecting the Access FE to Access BE shared on the
network. Do you think that ODBC link to SQL server (MSDE, SQL,or SQL Express
2005) will present an advantage in the speed department? Would Access Project
have any advantage here? I certainly don't want to waste my time if this is
not a solution, but I have seen enough posts recommending ADP's over FE/BE
MDB's that it seemed like the next step for me.

Hi, Brian.

what is the correct syntax for Boolean
checks?

Try:

If DCount("[UserID]", "[UserRoles]", "[UserID] = '" & [UserId] & _
"' and [Role] = 'Application' and Active = " & True) > 0 Then

have you any opinions on
whether/when one is prefereable to the other as a backend?

I haven't used SQL Express 2005 (beta) yet, but I know the capabilities are
superior to MSDE, which is six year old technology. However, using an ADP
instead of an MDB limits the capabilities of the front-end database
application. Code and techniques that work fine in an MDB don't always work
in an ADP. If you have a somewhat complex application, you are going to make
some aggravating discoveries after you migrate. If it's possible to link the
tables to SQL Express 2005 (I don't know as I haven't tried), then go that
route with an MDB database file instead. And if you can't, I'd recommend a
client/server database, such as SQL Server or Oracle to link the tables to
from the MDB front-end.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

Thanks. Good info at those links. There are times when I wish Access was not
so forgiving about accomodating these things because it lets me get into bad
habits that can be hard to break later. I will probably find the answer in
the SQL docs, but in the meantime, what is the correct syntax for Boolean
checks? The field in my example is a Yes/No field called Active, and I
already tried leaving out the " = True" (i.e. just "If Active...", which I
use successfully throughout my current Access mdb fe/be structure), but got
an error on this also.

Also, since I am just starting to migrate and have the opportunity to go
with either MSDE 2000 or SQL Express 2005, have you any opinions on
whether/when one is prefereable to the other as a backend?

Hi, Brian.

1. This line give me an "Invalid column name 'True'" error. Why?

Because the syntax you are using is asking the database engine to compare
the value in the "Active" column with the value in the "True" column for each
record. Unless you actually had a field in your MDB database table named
"True" holding a Boolean value, the block of code below this IF statement
will never execute, because no records will ever meet the condition "Active =
True." Jet won't warn you of this logic error, but SQL Server does.

2. Where can I get general/ongoing help with what may be a host of these
little syntax (?) changes when migrating from Access fe/be to Access project
/ SQL?

Download the SQL Server Books Online (BOL) on the following Web page:

http://www.microsoft.com/downloads/...b1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en

As a quick reference, please see the following Web page for Jet 4.0 Reserved
words to avoid, which are ANSI SQL-92 compliant, which is used for SQL Server
2000 and MSDE:

http://support.microsoft.com/?id=321266

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.


:

I'm just beginning to experiment with upsizing from .mdb's to SQL using the
upsizing wizard. One specific question, one general question:

1. This line give me an "Invalid column name 'True'" error. Why? Active is
the name of a Yes/No field that upsized OK. I can look at the table and see
it just fine.

If DCount("[UserID]", "[UserRoles]", "[UserID] = '" & [UserID] & "' and
[Role] = 'Application' and Active = True") > 0

2. Where can I get general/ongoing help with what may be a host of these
little syntax (?) changes when migrating from Access fe/be to Access project
/ SQL?

 

[Larry Linson]

. . . Do you think that ODBC link to SQL server
(MSDE, SQL,or SQL Express 2005) will present
an advantage in the speed department?

An Access 2.0 client to Informix supported just under 200 users the last
time I worked on it, with no "performance issues". An MVP colleague, Tom
Ellison, used MSDE with ADP on the same machine, because MSDE was
multithreaded and performed better than Jet -- he did very complex SQL, all
written from scratch.

Would Access Project have any advantage
here? I certainly don't want to waste my time
if this is not a solution, but I have seen enough
posts recommending ADP's over FE/BE
MDB's that it seemed like the next step for me.

Knowledgeable Microsoft insiders, including one who was the Product Manager
for ADPs and ADO on a previous Access release, now tell us that
MDB-Jet-ODBC-Server is generally preferred over ADP-OLEDB - SQL Server for
new development.

There are still a lot of people who believed the hype (and, goodness knows,
it was strong enough to make people believe) about ADP being the "Access of
tomorrow", adopted it, and, because of the nature of their database
requirements, have not enountered any fatal flaws. I have done a little work
on client ADPs -- I didn't see any advantage and found there was a learning
curve (but not as steep as I had imagined). However, not seeing any
advantage and given the current recommendations from Microsoft people whose
opinions I trust, I would not begin new development of an ADP.

On the other hand, if you have not carefully considered and tried the many
multi-user Access performance suggestions at MVP Tony Toews' site
http://www.granite.ab.ca/accsmstr.htm, I'd give that a try. You may solve
your response problems with a LOT less effort!

Larry Linson
Microsoft Access MVP

 

['69 Camaro]

Hi, Brian.

I was anticipating a lot of work transferring
functionality from the front end to stored procedures.

It probably will be, especially if you aren't well-versed in T-SQL and your
queries are at least somewhat complex.

The main reason I am migrating is the severe network latency I experience
with multiple users connecting the Access FE to Access BE shared on the
network.

Oh. I thought you had a _good_ reason for migrating, such as this database
has become mission-critical, or the data now needs to be secure, or the
database is getting too big. Migrating may not speed up your database
significantly if you haven't already taken steps to make it as fast as
possible and then repeat the same slow database design and slow techniques
for the new backend.

Do you think that ODBC link to SQL server (MSDE, SQL,or SQL Express
2005) will present an advantage in the speed department? Would Access Project
have any advantage here?

That's a new question. I suggest that you post your new question with a
subject line such as, "Make my db as fast as possible," and you'll get a
whole truckload of suggestions from the experts. Of course, ask if the
migration to the other alternatives on your list would be even faster and
include information in your post describing the situation. You have "a split
database with separate Access database front ends on each workstation, but
the application is always slow whenever ... " and fill in the blanks. Is it
queries? Is it just opening the database? Is it opening the forms? What
version of Access are you using?

Explain with some details and provide information on what you've already
done to speed up the database, like indexes on join and sort columns,
normalized data, AutoNumber primary keys instead of composite keys of text
fields, record-level locking, not using the IN operator in queries, not using
Domain Functions, placing the database file as close to the root directory as
possible, only using the DOS 8.3 naming convention for the directory path and
file name, et cetera.

You may find that the experts' suggestions help you speed up your database
application so much that you don't even need to migrate, if that is the only
significant reason you have for wanting to do so.

If you have found my replies helpful then please consider marking my reply
as an answer to your question.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

Thank you again. You are so correct about the aggravating discoveries - I
have a LOT of time invested in my Access MDB fe's, and do not relish the idea
of rebuilding them as ADP's. I was anticipating a lot of work transferring
functionality from the front end to stored procedures.

The main reason I am migrating is the severe network latency I experience
with multiple users connecting the Access FE to Access BE shared on the
network. Do you think that ODBC link to SQL server (MSDE, SQL,or SQL Express
2005) will present an advantage in the speed department? Would Access Project
have any advantage here? I certainly don't want to waste my time if this is
not a solution, but I have seen enough posts recommending ADP's over FE/BE
MDB's that it seemed like the next step for me.

Hi, Brian.

what is the correct syntax for Boolean
checks?

Try:

If DCount("[UserID]", "[UserRoles]", "[UserID] = '" & [UserId] & _
"' and [Role] = 'Application' and Active = " & True) > 0 Then

have you any opinions on
whether/when one is prefereable to the other as a backend?

I haven't used SQL Express 2005 (beta) yet, but I know the capabilities are
superior to MSDE, which is six year old technology. However, using an ADP
instead of an MDB limits the capabilities of the front-end database
application. Code and techniques that work fine in an MDB don't always work
in an ADP. If you have a somewhat complex application, you are going to make
some aggravating discoveries after you migrate. If it's possible to link the
tables to SQL Express 2005 (I don't know as I haven't tried), then go that
route with an MDB database file instead. And if you can't, I'd recommend a
client/server database, such as SQL Server or Oracle to link the tables to
from the MDB front-end.

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.

Thanks. Good info at those links. There are times when I wish Access was not
so forgiving about accomodating these things because it lets me get into bad
habits that can be hard to break later. I will probably find the answer in
the SQL docs, but in the meantime, what is the correct syntax for Boolean
checks? The field in my example is a Yes/No field called Active, and I
already tried leaving out the " = True" (i.e. just "If Active...", which I
use successfully throughout my current Access mdb fe/be structure), but got
an error on this also.

Also, since I am just starting to migrate and have the opportunity to go
with either MSDE 2000 or SQL Express 2005, have you any opinions on
whether/when one is prefereable to the other as a backend?

:

Hi, Brian.

1. This line give me an "Invalid column name 'True'" error. Why?

Because the syntax you are using is asking the database engine to compare
the value in the "Active" column with the value in the "True" column for each
record. Unless you actually had a field in your MDB database table named
"True" holding a Boolean value, the block of code below this IF statement
will never execute, because no records will ever meet the condition "Active =
True." Jet won't warn you of this logic error, but SQL Server does.

2. Where can I get general/ongoing help with what may be a host of these
little syntax (?) changes when migrating from Access fe/be to Access project
/ SQL?

Download the SQL Server Books Online (BOL) on the following Web page:

http://www.microsoft.com/downloads/...b1-a420-445f-8a4b-bd77a7da194b&DisplayLang=en

As a quick reference, please see the following Web page for Jet 4.0 Reserved
words to avoid, which are ANSI SQL-92 compliant, which is used for SQL Server
2000 and MSDE:

http://support.microsoft.com/?id=321266

HTH.

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

(Please remove ZERO_SPAM from my reply E-mail address, so that a message
will be forwarded to me.)
Beware to those who use munged addresses: known newsgroup E-mail harvesters
for spammers are (e-mail address removed) and (e-mail address removed)

- - -
When you see correct answers to your question posted in Microsoft's Online
Community, please sign in to the Community and mark these posts as "Answers,"
so that all may benefit by filtering on "Answered questions" and quickly
finding the right answers to similar questions. Remember that questions
answered the quickest are often from those who have a history of rewarding
the contributors who have taken the time to answer questions correctly.


:

I'm just beginning to experiment with upsizing from .mdb's to SQL using the
upsizing wizard. One specific question, one general question:

1. This line give me an "Invalid column name 'True'" error. Why? Active is
the name of a Yes/No field that upsized OK. I can look at the table and see
it just fine.

If DCount("[UserID]", "[UserRoles]", "[UserID] = '" & [UserID] & "' and
[Role] = 'Application' and Active = True") > 0

2. Where can I get general/ongoing help with what may be a host of these
little syntax (?) changes when migrating from Access fe/be to Access project
/ SQL?

 

[Dirk Goldgar]

Thanks. Good info at those links. There are times when I wish Access
was not so forgiving about accomodating these things because it lets
me get into bad habits that can be hard to break later. I will
probably find the answer in the SQL docs, but in the meantime, what
is the correct syntax for Boolean checks? The field in my example is
a Yes/No field called Active, and I already tried leaving out the " =
True" (i.e. just "If Active...", which I use successfully throughout
my current Access mdb fe/be structure), but got an error on this
also.

Probably the most reliable test is for "<> 0". Both Jet and SQL
Server -- and every other database and programming system I'm aware
of -- use 0 to represent "false", but Jet uses -1 for "true", while SQL
Server uses 1 for "true".

 

[Tom Wickerath]

Hi Brian,

The main reason I am migrating is the severe network latency...

If you decide that you really need to migrate, after following the
recommendations that you have already received from Gunny and Larry Linson,
then you would do good to obtain a copy of the book titled "Microsoft Access
Developer's Guide to SQL Server", written by Mary Chipman and Andy Baron
(SAMS Publishing). Here is a link to the book at Amazon.com:

http://www.amazon.com/exec/obidos/t...103-8424237-6872610?v=glance&s=books&n=507846

Early in the book (page 6) the authors write:

<Begin Quote>
"Many people think that upsizing from the Jet database engine to SQL Server
is a universal panacea for whatever is ailing their Access databases. It's
not. In fact, just the opposite is usually true. If your Access application
is a dog, then most likely it will still be a dog after you upsize it to SQL
Server--perhaps an even bigger, uglier, shaggier dog! Even a well-designed
Jet database often won't run any faster after the tables are upsized to SQL
Server if you are using the same data access methods you used for your Access
database. In order to successfully convert your Access database, you have to
be clear about why it needs to be upsized, and you need to understand how to
take advantage of the strengths of SQL Server by reworking the data access
elements of your Access application."
</End Quote>

This book is highly recommended. It will help you with these issues, should
you decide that you need to upsize.

If my answer has helped you, please answer yes to the question that reads
"Did this post answer the question?" at the bottom of the message thread.

Tom
_______________________________________

:

Thank you again. You are so correct about the aggravating discoveries - I
have a LOT of time invested in my Access MDB fe's, and do not relish the idea
of rebuilding them as ADP's. I was anticipating a lot of work transferring
functionality from the front end to stored procedures.

The main reason I am migrating is the severe network latency I experience
with multiple users connecting the Access FE to Access BE shared on the
network. Do you think that ODBC link to SQL server (MSDE, SQL,or SQL Express
2005) will present an advantage in the speed department? Would Access Project
have any advantage here? I certainly don't want to waste my time if this is
not a solution, but I have seen enough posts recommending ADP's over FE/BE
MDB's that it seemed like the next step for me.