A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » General Discussion
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Access and SQL



 
 
Thread Tools Display Modes
  #1  
Old August 2nd, 2004, 01:46 PM
AHopper
external usenet poster
 
Posts: n/a
Default Access and SQL

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

  #2  
Old August 2nd, 2004, 03:02 PM
Van T. Dinh
external usenet poster
 
Posts: n/a
Default Access and SQL

See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)


"AHopper" wrote in message
...
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.




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



  #3  
Old August 2nd, 2004, 03:13 PM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default Access and SQL

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."

--
Good luck

Jeff Boyce
Access MVP

  #4  
Old August 2nd, 2004, 03:50 PM
AHopper
external usenet poster
 
Posts: n/a
Default Access and SQL

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
-----Original Message-----
See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)


"AHopper" wrote in

message
...
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.




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



.

  #5  
Old August 2nd, 2004, 03:50 PM
AHopper
external usenet poster
 
Posts: n/a
Default Access and SQL

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
-----Original Message-----
See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)


"AHopper" wrote in

message
...
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.




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



.

  #6  
Old August 2nd, 2004, 03:50 PM
AHopper
external usenet poster
 
Posts: n/a
Default Access and SQL

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
-----Original Message-----
See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)


"AHopper" wrote in

message
...
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.




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



.

  #7  
Old August 2nd, 2004, 03:50 PM
AHopper
external usenet poster
 
Posts: n/a
Default Access and SQL

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
-----Original Message-----
See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)


"AHopper" wrote in

message
...
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.




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



.

  #8  
Old August 2nd, 2004, 03:50 PM
AHopper
external usenet poster
 
Posts: n/a
Default Access and SQL

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
-----Original Message-----
See comments in-line.

--
HTH
Van T. Dinh
MVP (Access)


"AHopper" wrote in

message
...
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.




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



.

  #9  
Old August 2nd, 2004, 06:27 PM
Albert D. Kallal
external usenet poster
 
Posts: n/a
Default Access and SQL

"AHopper" wrote in message
...

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...19&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


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

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


  #10  
Old August 2nd, 2004, 06:57 PM
Tony Toews
external usenet poster
 
Posts: n/a
Default Access and SQL

"Van T. Dinh" wrote:

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
 




Thread Tools
Display Modes

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

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Function isn't available in expressions in query expression Reiner Harmgardt General Discussion 4 July 21st, 2004 09:30 AM
SQL Server 2000 Stored Procedures to MS Access 2000 Queries CS General Discussion 4 July 15th, 2004 03:27 AM
TRIGGER a VIEW with SQL for a LINKED SERVER (ACCESS DB) youssef General Discussion 10 July 13th, 2004 12:00 AM
Problem accessing SQL Server in upsizing wizard Edward General Discussion 1 July 5th, 2004 06:00 AM
Access 2000 .adp and SQL 2000 Wayne H. New Users 0 May 3rd, 2004 08:28 PM


All times are GMT +1. The time now is 11:30 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.