Recommendation for Access database type

#1

We're going to have multiple users entering data on a form in Access.
Once the data entry is finished the user clicks a button, goes through
verification, and the data is appended with a query to the main table.
The form is unbound.

Certain users who log into the database from other sites are having
performance issues in Access with Speed etc.

Would it be better if I setup a back-end/front-end database structure
here? The front end would sit on the person's hard drive and backend
on the network. Can i do the same append type query to the network
database without risk of corruption etc?

Thanks for the advice

#2

pieler8 <captainweet (AT) gmail (DOT) com> wrote in
news:65ddee54-0148-4624-9211-6a1db6d98c6b (AT) r29g2000yqj (DOT) googlegroups.co
m:

Quote:

Certain users who log into the database from other sites are
having performance issues in Access with Speed etc.

Would it be better if I setup a back-end/front-end database
structure here? The front end would sit on the person's hard drive
and backend on the network. Can i do the same append type query to
the network database without risk of corruption etc?

There is never a situation where you should not be set up as you
describe it here in the second paragraph. That is, unsplit databases
simply should not exist. And you should never share a front end,
either.

The first paragraph quoted above suggests that you're trying to use
Access across a WAN or the Internet. This, too, is something you
should never do. The easiest solution is deploying the app on
Windows Terminal Server, with everyone running the app using the
Remote Desktop Client.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

#3

if you want to use Access across a WAN or a VPN, you can use Access
Data Projects and it works GREAT

On Jan 14, 3:32*pm, "David-W-Fenton" <dfas... (AT) dfenton (DOT) com> wrote:

Quote:

pieler8 <captainw... (AT) gmail (DOT) com> wrote innews:65ddee54-0148-4624-9211-6a1db6d98c6b (AT) r29g2000yqj (DOT) googlegroups.co
m:

Certain users who log into the database from other sites are
having performance issues in Access with Speed etc.

Would it be better if I setup a back-end/front-end database
structure here? The front end would sit on the person's hard drive
and backend on the network. Can i do the same append type query to
the network database without risk of corruption etc?

There is never a situation where you should not be set up as you
describe it here in the second paragraph. That is, unsplit databases
simply should not exist. And you should never share a front end,
either.

The first paragraph quoted above suggests that you're trying to use
Access across a WAN or the Internet. This, too, is something you
should never do. The easiest solution is deploying the app on
Windows Terminal Server, with everyone running the app using the
Remote Desktop Client.

--
David W. Fenton * * * * * * * * *http://www.dfenton.com/
contact via website only * *http://www.dfenton.com/DFA/

#4

and of course people have issues with Access speed.

Access speed sucks, that's why nobody in the REAL WORLD would choose
to use Access (Jet) for new applications.

Access doesn't support included indexes for example.. so it will NEVER
be as fast as SQL Server

On Jan 14, 7:45*am, pieler8 <captainw... (AT) gmail (DOT) com> wrote:

Quote:

We're going to have multiple users entering data on a form in Access.
Once the data entry is finished the user clicks a button, goes through
verification, and the data is appended with a query to the main table.
The form is unbound.

Certain users who log into the database from other sites are having
performance issues in Access with Speed etc.

Would it be better if I setup a back-end/front-end database structure
here? The front end would sit on the person's hard drive and backend
on the network. Can i do the same append type query to the network
database without risk of corruption etc?

Thanks for the advice

#5

On Fri, 14 Jan 2011 07:45:28 -0800 (PST), pieler8
<captainweet (AT) gmail (DOT) com> wrote:

Quote:

We're going to have multiple users entering data on a form in Access.
Once the data entry is finished the user clicks a button, goes through
verification, and the data is appended with a query to the main table.
The form is unbound.

Certain users who log into the database from other sites are having
performance issues in Access with Speed etc.

Would it be better if I setup a back-end/front-end database structure
here? The front end would sit on the person's hard drive and backend
on the network. Can i do the same append type query to the network
database without risk of corruption etc?

FE/BE is a good idea but won't make a difference when it comes to
speed on a WAN from other sites. And there is a greatly increased
chance of corruption when updating an Access database from over a WAN.

One solution, as David mentions, is Terminal Services. Another would
be to use SQL Server. SQL Server Express is free however this would
require the IT dept. to at least do the install and ensure that
backups are working.

Do note that Access Data Projects aka ADPs haven't received any
enhancements since Access 2000 and thus aren't recommended.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

#6

Tony Toews wrote:

Quote:

On Fri, 14 Jan 2011 07:45:28 -0800 (PST), pieler8
captainweet (AT) gmail (DOT) com> wrote:

We're going to have multiple users entering data on a form in Access.
Once the data entry is finished the user clicks a button, goes through
verification, and the data is appended with a query to the main table.
The form is unbound.

Certain users who log into the database from other sites are having
performance issues in Access with Speed etc.

Would it be better if I setup a back-end/front-end database structure
here? The front end would sit on the person's hard drive and backend
on the network. Can i do the same append type query to the network
database without risk of corruption etc?

FE/BE is a good idea but won't make a difference when it comes to
speed on a WAN from other sites. And there is a greatly increased
chance of corruption when updating an Access database from over a WAN.

One solution, as David mentions, is Terminal Services. Another would
be to use SQL Server. SQL Server Express is free however this would
require the IT dept. to at least do the install and ensure that
backups are working.

Do note that Access Data Projects aka ADPs haven't received any
enhancements since Access 2000 and thus aren't recommended.

Tony

Is SQL Server Express limited to a small number of users?

#7

On Sat, 15 Jan 2011 17:42:41 -0600, Salad <salad (AT) oilandvinegar (DOT) com>
wrote:

Quote:

Is SQL Server Express limited to a small number of users?

No. MSDE 2000 was throttle to ten connections. Which is not the same
as ten users. One poster stated he had 75 users happily using such a
data.

It is limited to 4 Gb in database file size which is fine for us folks
usually.

Limitations
SQL Server [2008] R2 Express supports 1 physical processor, 1 GB
memory, and 4 GB storage
http://www.microsoft.com/sqlserver/2...s/express.aspx

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

#8

SQL Server 2008 R2 Express is limited to 10 GB.

--

AG
Email: npATadhdataDOTcom

"Tony Toews" <ttoews (AT) telusplanet (DOT) net> wrote

Quote:

On Sat, 15 Jan 2011 17:42:41 -0600, Salad <salad (AT) oilandvinegar (DOT) com
wrote:

Is SQL Server Express limited to a small number of users?

No. MSDE 2000 was throttle to ten connections. Which is not the same
as ten users. One poster stated he had 75 users happily using such a
data.

It is limited to 4 Gb in database file size which is fine for us folks
usually.

Limitations
SQL Server [2008] R2 Express supports 1 physical processor, 1 GB
memory, and 4 GB storage
http://www.microsoft.com/sqlserver/2...s/express.aspx

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/

#9

Tony Toews wrote:

Quote:

On Sat, 15 Jan 2011 17:42:41 -0600, Salad <salad (AT) oilandvinegar (DOT) com
wrote:

Is SQL Server Express limited to a small number of users?

No. MSDE 2000 was throttle to ten connections. Which is not the same
as ten users. One poster stated he had 75 users happily using such a
data.

It is limited to 4 Gb in database file size which is fine for us folks
usually.

Limitations
SQL Server [2008] R2 Express supports 1 physical processor, 1 GB
memory, and 4 GB storage
http://www.microsoft.com/sqlserver/2...s/express.aspx

Tony

Where can I find info on the differentiation between connections and
number of users?

#10

On Sun, 16 Jan 2011 14:23:17 -0600, Salad <salad (AT) oilandvinegar (DOT) com>
wrote:

Quote:

Where can I find info on the differentiation between connections and
number of users?

As far as I know there isn't any differentiation any more. That was
dropped in SQL Server 2003 I think.

Now as far as the connection goes think of the connection only being
present when data is being fetched or sent to SQL Server. So
displaying a form would have a connection for a short time but there's
a lot of time while the user is viewing/interacting with the form that
the connection isn't present.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/