Access and SQL

[AHopper]

I have a Access database split front end Mde and backend
mdb in a network environment, with about 30 workstations
that can run it using Access runtime. Most of these are
data entry at a low level of input.

The front end is presently 62.3 MB and the back end is
39.6 MB.
Recently, I had a conversation that left me with some
concerns. I have not had problems to this point but the
individual I was talking to said that I would likely soon
start having difficulty because of the number of users and
the size of the database.

Questions:
1. Is this true?
2. What size is critical?
3. What steps should I take to protect myself from trouble?

SQL
Recently we have installed a SQL Server for another
database we use. I originally chose Access knowing that it
could be moved to SQL. I am in the process of learning
SQL. I have had someone in the Microsoft training field
indicate that changing to SQL is not necessary and is or
can be complicated.

Questions:
1. Is it difficult to change the database to SQL?
2. Should I keep Access as the front end with the SQL
backend?
3. Will the change require a lot of programming? (Someone
suggested that front end design should be done in Visual
Basic.)?
4. How can I prepare for this move?

I would appreciate very much any insight into what is
involved with this process.

If I should post these questions on a different site
please let me know.

Thank you in advance,
Allan

 

[Van T. Dinh]

See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)

I have a Access database split front end Mde and backend
mdb in a network environment, with about 30 workstations
that can run it using Access runtime. Most of these are
data entry at a low level of input.

The front end is presently 62.3 MB and the back end is
39.6 MB.
Recently, I had a conversation that left me with some
concerns. I have not had problems to this point but the
individual I was talking to said that I would likely soon
start having difficulty because of the number of users and
the size of the database.

Questions:
1. Is this true? Not true for a well-designed / implemented database.

It depends on how well the database is designed and implemented.

For users, Larry Linson regularly reports that he has JET databases that run
well with 70 users. Personally, I look to move to SQL Server at about 25-30
regular users (using the database > 4 hours during a normal working day).
However, I move to SQL Server because of the back-up facilities on SQL
Server (full & incremental back-up as well as transaction log / transaction
log back-up) rather than difficulties with JET Back-End.

OTOH, 2 or 3 users may be too much for a badly-designed database!

See next for size.

2. What size is critical? same as 1

The biggest JET Back-End I have had is about 130 MB (compacted) and I didn't
have any problem. However, my intention is to upsize to SQL Server at
about 250-300 MB, simply because I think if it is that big storing valid
data, it is important enough to have proper database / transaction log
back-up procedure.

3. What steps should I take to protect myself from trouble?

Regularly compact and back up the Back-End. Back up daily if you can.
(IMHO, back-up / BU procedure is a weak point for Access / JET)

SQL
Recently we have installed a SQL Server for another
database we use. I originally chose Access knowing that it
could be moved to SQL. I am in the process of learning
SQL. I have had someone in the Microsoft training field
indicate that changing to SQL is not necessary and is or
can be complicated.

Questions:
1. Is it difficult to change the database to SQL?

Personally, I didn't find it is difficult to upsize from JET to SQL Server.
Tony Toews has some info on his Web site:

http://www.granite.ab.ca

I think there are some white papaers on Microsoft Web site also.

2. Should I keep Access as the front end with the SQL
backend?

That's what I did. Use Access database (MDB / MDE) Front-End with
ODBC-linked Tables from SQL Server, NOT ADP.

3. Will the change require a lot of programming? (Someone
suggested that front end design should be done in Visual
Basic.)?

Some mods required as SQL Server Identity Field behave differently from JET
AutoNumber Field. Also be careful of SQL Server BIT Field vs JET Boolean
Field. If you use Recordsets, you will need to modify some arguments when
opening a Recordset (dbSeeChanges option, etc ...).

If you are presently use DAO, you may want to rewrite code to use ADO when
time permitted (DAO works fine but some experts recommend ADO for
efficiency).

Visual Basic: only if you have 2 or 3 months to waste!

4. How can I prepare for this move?

Grab Mary Chipman's "Microsoft Access Developer's Guide to SQL Server"

http://www.amazon.com/exec/obidos/ASIN/0672319446

and read it as much as you can.

 

[Jeff Boyce]

Allan

The following in-line responses are JOPO (just one person's opinions) and
observations...

The front end is presently 62.3 MB and the back end is
39.6 MB.

This seems rather large for a front-end. Have you recently checked the
original .mdb from which the .mde was created? Was it compiled and repaired
before being made into an .mde?

Recently, I had a conversation that left me with some
concerns. I have not had problems to this point but the
individual I was talking to said that I would likely soon
start having difficulty because of the number of users and
the size of the database.

Questions:
1. Is this true?

Scan the tablesdbdesign newsgroup for more info, but I suspect you'll find
that truth is relative. And didn't you say that you have not had problems?
Are you planning to add signifiantly more users?

2. What size is critical?

What use is being made? Are all 30 of your current users doing data
entry/transaction processing, or are they doing lookup? If you're expecting
growth, what kind?

3. What steps should I take to protect myself from trouble?

Is your network rock solid? At what speed does it run? Do you have backups
of EVERYTHING!? Do you periodically compact and repair the back-end data
(when no one is logged on and AFTER you've taken a backup)?

SQL
Recently we have installed a SQL Server for another
database we use. I originally chose Access knowing that it
could be moved to SQL. I am in the process of learning
SQL. I have had someone in the Microsoft training field
indicate that changing to SQL is not necessary and is or
can be complicated.

If your current approach is working without issue and you anticipate no
growth, why change to SQL Server? 1). More robust back-end. 2). Faster
response. 3). Keep your SQL DBAs employed.

Questions:
1. Is it difficult to change the database to SQL?

Migrating your back-end data to SQL Server can be relatively painless.
Ensuring that it's gotten there, in the correct form (data types), and with
proper permissions, is less painless.

2. Should I keep Access as the front end with the SQL
backend?

Access makes a fine front-end for a variety of back-end sources, including
SQL Server.

3. Will the change require a lot of programming? (Someone
suggested that front end design should be done in Visual
Basic.)?

Undoubtedly! Some of the queries you now use for forms and reports will
"dim the lights" once you move the back-end data to SQL Server. This is
because you have used Access-specific functions in your queries (I'm
guessing here) that SQL Server won't understand. This will require Access
to download all the table info from SQL Server -- your network gurus will
HATE you! The work you'll need to do is modify your queries to use SQL
Server-only functionality (check into SQL Server "views", and "stored
procedures") as a first step. This will return the minimum data set. THEN
you can build a second query on the first, and add in your Access-specific
functions.

4. How can I prepare for this move?

If you decide you have to move, plan on considerable re-development and
testing time. One of the great books on topic is by Chipman and Baron:
"Microsoft Access Developer's Guide to SQL Server."

 

[AHopper]

Van,
Thank you for your valuable information.

How does the change to SQL impact forms and reports? Will
code in the events of the form remain the same. (For
example an After Update event that contains DLookup, DSum,
DCount etc. that look at the backend and give information
to the end user through a MsgBox with options on how to
proceed.)

Thanks again,

Allan

 

[AHopper]

Van,
Thank you for your valuable information.

How does the change to SQL impact forms and reports? Will
code in the events of the form remain the same. (For
example an After Update event that contains DLookup, DSum,
DCount etc. that look at the backend and give information
to the end user through a MsgBox with options on how to
proceed.)

Thanks again,

Allan

 

[AHopper]

Van,
Thank you for your valuable information.

How does the change to SQL impact forms and reports? Will
code in the events of the form remain the same. (For
example an After Update event that contains DLookup, DSum,
DCount etc. that look at the backend and give information
to the end user through a MsgBox with options on how to
proceed.)

Thanks again,

Allan

 

[AHopper]

Van,
Thank you for your valuable information.

How does the change to SQL impact forms and reports? Will
code in the events of the form remain the same. (For
example an After Update event that contains DLookup, DSum,
DCount etc. that look at the backend and give information
to the end user through a MsgBox with options on how to
proceed.)

Thanks again,

Allan

 

[AHopper]

Van,
Thank you for your valuable information.

How does the change to SQL impact forms and reports? Will
code in the events of the form remain the same. (For
example an After Update event that contains DLookup, DSum,
DCount etc. that look at the backend and give information
to the end user through a MsgBox with options on how to
proceed.)

Thanks again,

Allan

 

[Albert D. Kallal]

I have a Access database split front end Mde and backend
mdb in a network environment, with about 30 workstations
that can run it using Access runtime. Most of these are
data entry at a low level of input.

The front end is presently 62.3 MB and the back end is
39.6 MB.

Jeff also zeroed in on this. That sounds very large.

I have a database with the following stats:

total lines of code = 27369
number of Forms = 162
number of Reports = 73
number of modules = 23
number of Queries = 181

The number of tables is 55, but then since it is split then that don't
matter.

In access 2003, the above as a mdb is 8 megs in size. When I create a mde
for the front end (and you REALLY should use a mde for the front end), the
file size drops down to 6.7 megs. In fact, I can actually zip the whole
thing on to ONE floppy disk with WinZip.

Considering the number of forms, reports and code in the above, and the mde
is only in the 6 megs range, I would serious look into your front end size.
It is certainly possible that your application is 10 times the size as
above, but then we would be talking about an application with 1600 forms,
and 1/4 million lines of code.

I would likely soon
start having difficulty because of the number of users and
the size of the database.

Questions:
1. Is this true?

When you say 30 workstations. Do you mean as a general rule you have 30
users working at the same time? ms-access will certainly handle that many
users, but I would certainly be moving things to sql server with that many
users.

2. What size is critical?

By size, do you mean database size, or number of users? You can have a VERY
small database, but with 60 users, you are pushing things. We see some
people complaining in this news group that 1, or 2 users is too slow. So,
the number of users is often going to be based on how well the application
and the network and the workstations are setup. I mean, if access is too
slow for some people with 1 user...how you going to get 30 users?

I think the real important thing is ask how much work gets lost if the
database is damaged? (likely, you are talking about 1 day to the previous
backup). With 3 users, and loss of a 1/2 day might not be too bad. You loose
the work of 30 people, and you are talking about a month of work being lost.
It becomes VERY easy to justify moving the back end data to sql server when
you reach 30 users.

I have had someone in the Microsoft training field
indicate that changing to SQL is not necessary and is or
can be complicated.

Necessary and complication are two separate issues. sql server is FAR FAR
easier to learn then is ms-access. Sql server does not have much of a
programming language (t-sql). Sql server has no forms design package. Sql
server has no reports design package. In fact, about all you an do with sql
server is create some tables, and create some queries. How hard can that be?
Since all that sql server is a box with some tables and sql, then you are
not talking about much. If your sql skills are quite good from using
ms-access, then using sql server is nothing really compared to ms-access.

Questions:
1. Is it difficult to change the database to SQL?

You mean sql server (you already been using tons of sql in ms-access.
Ms-access uses sql everywhere anyway). No, it is not hard, but you do have
to learn some of the ins and outs of doing this.

2. Should I keep Access as the front end with the SQL
backend?

As mentioned, sql server cannot create forms, sql cannot create reports. In
fact, sql server can't create any of the UI. This is why I said that sql
server is easy to learn, as it don' do very much. So, you have to write the
front end in something. That front end can be c++, VB, or ms-access.

ms-access just like c++ is simply a development tool (it is not a database).
So, if you already have a application written and running in ms-access, then
it seems to me to be a good idea to continue to keep the application (why
re-write it? What development tool did you have in mind?).

3. Will the change require a lot of programming?

Much will depend on how good your designs are now. However, 90% or more of
your code will work as is.

(Someone
suggested that front end design should be done in Visual
Basic.)?

Why? ms-access uses the same compiler and actually even using the SAME
programming language as VB. I see little, or no advantage of re-writing your
application to VB.

4. How can I prepare for this move?

Well, start learning and playing with sql server. You do know that a free
version of sql server is included on the office cd for use with ms-access?
(the last 3 versions 2000, 2002, 2003 have included the desktop version sql
server for ms-access). So, sql server is given away for free.

You can also read some articles:

http://support.microsoft.com/default.aspx?scid=kb;en-us;175619&Product=acc

ACC2000: "Access 2000 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=241743

ACC2002: "Access 2002 Upsizing Tools" White Paper Available in Download
Center
http://support.microsoft.com/?id=294407

ACC2000: Optimizing for Client/Server Performance (odbc)
http://support.microsoft.com/?id=208858

ACC: "Upsizing to Microsoft SQL Server" White Paper Available in Download
Center (a95, and a97)
http://support.microsoft.com/?id=175619

HOW TO: Convert an Access Database to SQL Server (a97,a2000)
http://support.microsoft.com/?id=237980

ACC: Choosing Database Tools White Paper Available in Download Cente

The Choose.exe file contains a document called "Choosing the Right Database
Tools" that discusses Microsoft's database products: Microsoft Access,
Microsoft FoxPro, Microsoft SQL Server, Microsoft Visual Basic, and Open
Database Connectivity (ODBC). Use this document to decide which database
tool is right for you.

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

ACC: Tips for Optimizing Queries on Attached SQL Tables
http://support.microsoft.com/?id=99321

 

[Tony Toews]

Personally, I didn't find it is difficult to upsize from JET to SQL Server.
Tony Toews has some info on his Web site:
http://www.granite.ab.ca

Random Thoughts on SQL Server Upsizing from Microsoft Access Tips
http://www.granite.ab.ca/access/sqlserverupsizing.htm

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

 

[Robert Blackwell]

http://tinyurl.com/53drx

 

[A Hopper]

Jeff,
The front end Mde is presently 25.2 MB, 62.3 is the Mdb
fron end I am using for developing.

There is a mix of users at the present time. 20 users
would input data prehaps 5 at a time for 5 to 10 minutes
at a time, throughout the day. There are 5 users
designated to edit errors, delete records using editing
forms. There are 4 users who basically run reports as they
need them. There is one person who can access the back end
to do major deletions etc..

The front end and back end are backed up daily with our
network backup (tape backup). At this point I am still
able to put a copy of the back end and front end on a CD
and I do this approximately every week as a second backup.

At the present time the system is used 24 hours 7 days a
week, so compacting of the back end is done every three or
four weeks

There is still more development that needs to be done on
the front end, and more tables that need to be added.
However, I am not anticipating any more users being added.

Presently I am not having any problems that I am aware of.

Thanks for your help

Allan

 

[A Hopper]

Jeff,
The front end Mde is presently 25.2 MB, 62.3 is the Mdb
fron end I am using for developing.

There is a mix of users at the present time. 20 users
would input data prehaps 5 at a time for 5 to 10 minutes
at a time, throughout the day. There are 5 users
designated to edit errors, delete records using editing
forms. There are 4 users who basically run reports as they
need them. There is one person who can access the back end
to do major deletions etc..

The front end and back end are backed up daily with our
network backup (tape backup). At this point I am still
able to put a copy of the back end and front end on a CD
and I do this approximately every week as a second backup.

At the present time the system is used 24 hours 7 days a
week, so compacting of the back end is done every three or
four weeks

There is still more development that needs to be done on
the front end, and more tables that need to be added.
However, I am not anticipating any more users being added.

Presently I am not having any problems that I am aware of.

Thanks for your help

Allan

 

[A Hopper]

Jeff,
The front end Mde is presently 25.2 MB, 62.3 is the Mdb
fron end I am using for developing.

There is a mix of users at the present time. 20 users
would input data prehaps 5 at a time for 5 to 10 minutes
at a time, throughout the day. There are 5 users
designated to edit errors, delete records using editing
forms. There are 4 users who basically run reports as they
need them. There is one person who can access the back end
to do major deletions etc..

The front end and back end are backed up daily with our
network backup (tape backup). At this point I am still
able to put a copy of the back end and front end on a CD
and I do this approximately every week as a second backup.

At the present time the system is used 24 hours 7 days a
week, so compacting of the back end is done every three or
four weeks

There is still more development that needs to be done on
the front end, and more tables that need to be added.
However, I am not anticipating any more users being added.

Presently I am not having any problems that I am aware of.

Thanks for your help

Allan

 

[A Hopper]

Jeff,
The front end Mde is presently 25.2 MB, 62.3 is the Mdb
fron end I am using for developing.

There is a mix of users at the present time. 20 users
would input data prehaps 5 at a time for 5 to 10 minutes
at a time, throughout the day. There are 5 users
designated to edit errors, delete records using editing
forms. There are 4 users who basically run reports as they
need them. There is one person who can access the back end
to do major deletions etc..

The front end and back end are backed up daily with our
network backup (tape backup). At this point I am still
able to put a copy of the back end and front end on a CD
and I do this approximately every week as a second backup.

At the present time the system is used 24 hours 7 days a
week, so compacting of the back end is done every three or
four weeks

There is still more development that needs to be done on
the front end, and more tables that need to be added.
However, I am not anticipating any more users being added.

Presently I am not having any problems that I am aware of.

Thanks for your help

Allan

 

[A Hopper]

Albert
My Mde front end is 25.2 MB, 62.3 MB is the front end Mdb
I use for developing.
Is there a report that gives the information on the lines
of code? Do you just count the forms ,reports or is there
a report for that?

There are 20 workstations were data is entered throughout
the day, 5 employees at a time 5 to 10 minutes per
employee. There are 5 users designated to edit, delete
records. Editing is approximately three to 5 an hour.
There are 5 users who run reports and 1 user who can
delete or edit directly in the back end database.


Thank you for the information. Lived in Calgary for 8
years. Love Alberta.

Allan

 

[Van T. Dinh]

Domain Aggregate functions can be used as you are still using linked Tables.
It will be more efficient if you re-code to use the power of the server,
though.

The advantage with this method (Access MDB/MDE database vs Access ADP/ADE)
is that you can use most existing codes and then modify them LATER to take
advantage of the SQL Server SPs and UDFs rather than having to do everything
in one go.

I agree with other re the big Front-End, though. I have a very big
Front-End but it is still only about 28 MB (MDB format) and 15 MB (MDE
format) as I put a lot of comments in code.

 

[Albert D. Kallal]

Albert
My Mde front end is 25.2 MB, 62.3 MB is the front end Mdb
I use for developing.
Is there a report that gives the information on the lines
of code? Do you just count the forms ,reports or is there
a report for that?

I used to do the above by hand. If you scroll down to the very bottom of the
following temp web page of mine, you can download a small piece of code that
gives you the counts I talked about. You will find it here:

http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html

The lines of code count is likely not too important..but how many forms you
have is likely the real issue here.

Lived in Calgary for 8 years. Love Alberta.

Summer = yes...winter = no!

 

[AHopper]

Albert
Used your code and here are the results. This is the mdb
front end.

LinesOfCode
lines of code in Modules = 17549
Lines of code in forms = 85672
Lines of code in reports = 9612
total lines of code = 112833
number of Forms = 231
number of Reports = 100
number of tables (total) = 129
number of tables (user) = 121
number of modules = 5
number of Queries = 59

Love those mountains.

Allan

 

[A Hopper]

Jeff,
Ran some code provided by Albert Kallal and here are the
results.

LinesOfCode
lines of code in Modules = 17549
Lines of code in forms = 85672
Lines of code in reports = 9612
total lines of code = 112833
number of Forms = 231
number of Reports = 100
number of tables (total) = 129
number of tables (user) = 121
number of modules = 5
number of Queries = 59

Allan