Upsizing Access 2000 to SQL

[Geekette]

To make my application run faster and provide greater security, I would like
to migrate my backend Access 2000.mdb tables to SQL while using Access
Project as the front end. My Access (.mdb) database is currently split. The
backend tables are on a server and the front-end application is local to 5
user workstations.

My questions are:

Q1: For confirmation...after the migration process I should be able to add,
edit or delete data in my .adp forms the same way I did in the forms before
the migration from my .mdb right? Or will I need INSERT, DELETE, UPDATE
statements behind my form objects to do these tasks?

Q2: I've used Visual Basic functions and event procedures throughout the
Access.mdb application, will this be affected once converted to .adp?

Q3: Instead of converting my queries into views or stored procedures, could
I not create a separate .mdb file that points/links to my .adp file and run
my queries/report from there? Only one staff member requires this
functionality so the network traffic would be minimized. That way I’d
strictly use the .adp file for data entry. What are your thoughts on this?
Some tables contain over a million records and most queries are multiple
tables based with various join conditions.

Otherwise…

If I must convert my queries to views or stored procedures, are there a
tools to help me with this process? Or do you have recommendations on books
or software I could purchase to ease the process? I am not as SQL savvy as I
use to be.

[unknown]

Post by Geekette
To make my application run faster and provide greater security, I would like
to migrate my backend Access 2000.mdb tables to SQL while using Access
Project as the front end. My Access (.mdb) database is currently split. The
backend tables are on a server and the front-end application is local to 5
user workstations.
Q1: For confirmation...after the migration process I should be able to add,
edit or delete data in my .adp forms the same way I did in the forms before
the migration from my .mdb right? Or will I need INSERT, DELETE, UPDATE
statements behind my form objects to do these tasks?

Yes if your updates are not multi-step; ie., you can design your forms and
subforms in such a way that only one table is updated each time.
Theoritically, it's possible to use multi-step with ADO and ADP (ADP is
based on ADO) but personnally, I think that the only thing that you will
probably get out of the multi-step feature of ADO will be bugs.

Post by Geekette
Q2: I've used Visual Basic functions and event procedures throughout the
Access.mdb application, will this be affected once converted to .adp?

Probably yes unless you are making call to DAO in your code. If yes, these
will have to be translated to ADO or you will have to create a dummy DAO
database pointing toward the SQL-Server Database.

Post by Geekette
Q3: Instead of converting my queries into views or stored procedures, could
I not create a separate .mdb file that points/links to my .adp file and run
my queries/report from there?

Yes for the final part of your sentence. Many people are calling ADP
reports from a MDB file in order to not have to use passthrough queries with
reports because you can't use a passthrough query for a sub-report.
However, even if you call an ADP module from a MDB file, this ADP module
must still be a standard and ordinary ADP module; so the answer to the first
part of your sentence is No: you still have to convert them if you want to
use them from an ADP module/form/report.

Post by Geekette
Only one staff member
requires this
functionality so the network traffic would be minimized.

Not sure if converting your application to an ADP is the best option here if
your problem is only one staff member. Probably that you would want to use
a Remote Desktop instead. Much more easier to install and this will cost
you a lot less than designing an ADP project.

You should also give a closer look to your current MDB application. Maybe
they are some major flaws there that everyone will gain to have them solved.

Post by Geekette
That way I'd
strictly use the .adp file for data entry. What are your thoughts on this?
Some tables contain over a million records and most queries are multiple
tables based with various join conditions.

No problem. Nothing forbid you to use both MDB files with ODBC linked
tables and ADP projects against the same SQL-Server Database. In fact, you
can even trow other applications with other technologies such as .NET and/or
Classic ASP into the mix.

Post by Geekette
Otherwise.
If I must convert my queries to views or stored procedures, are there a
tools to help me with this process? Or do you have recommendations on books
or software I could purchase to ease the process? I am not as SQL savvy as I
use to be.

The best tool is still your ten little fingers. Don't expect to succeed
into this without having to acquire some knowledge about ADO, ADP and
SQL-Server. Also, the newsgroup for ADP is
microsoft.public.access.adp.sqlserver .

Finally, think twice about what you have already in your hands before
thinking about something else. If you don't know what you're doing; ask
someone who knows. There is no shame in not beeing a database specialist.

[Geekette]

Q1: No the updates are not multi-step. Thanks.

Q3: I mentioned that existing reports are used by one staff person. I
currently have 5 staff using the database for data entry. However, 20 –30 new
users throughout the organization have requested read-only or read-write
permission on the same database which is another reason for the upgrade. I’m
a self-taught Access database developer of 15 years but I admit I have no
programming experience with SQL and have never used Access Project. I have
one ORACLE table currently ODBC linked to the database so I know how to do
that part. After a ton of reading I figure that converting to .adp shouldn’t
be difficult, I understand the concept of the migration steps and conversion
needs. In your opinion, based on what I’ve stated, do you think it is
necessary to convert to .adp? Or should I continue to use .mdb…I’ve read that
.mdb will slow down queries and reports .ADP is faster…is that correct?

I agree with your statement about acquiring more knowledge. This is all very
new to me so I appreciate the direction. I’ve picked out 5 SQL courses to
take in the upcoming months and I’ll be using MS Support to assist me with my
questions. Once the back-end is on SQL, I will create new front-end forms and
reports to suit the needs or each department.

Post by unknown

Post by Geekette
To make my application run faster and provide greater security, I would like
to migrate my backend Access 2000.mdb tables to SQL while using Access
Project as the front end. My Access (.mdb) database is currently split. The
backend tables are on a server and the front-end application is local to 5
user workstations.
Q1: For confirmation...after the migration process I should be able to add,
edit or delete data in my .adp forms the same way I did in the forms before
the migration from my .mdb right? Or will I need INSERT, DELETE, UPDATE
statements behind my form objects to do these tasks?

Yes if your updates are not multi-step; ie., you can design your forms and
subforms in such a way that only one table is updated each time.
Theoritically, it's possible to use multi-step with ADO and ADP (ADP is
based on ADO) but personnally, I think that the only thing that you will
probably get out of the multi-step feature of ADO will be bugs.

Post by Geekette
Q2: I've used Visual Basic functions and event procedures throughout the
Access.mdb application, will this be affected once converted to .adp?

Probably yes unless you are making call to DAO in your code. If yes, these
will have to be translated to ADO or you will have to create a dummy DAO
database pointing toward the SQL-Server Database.

Post by Geekette
Q3: Instead of converting my queries into views or stored procedures, could
I not create a separate .mdb file that points/links to my .adp file and run
my queries/report from there?

Yes for the final part of your sentence. Many people are calling ADP
reports from a MDB file in order to not have to use passthrough queries with
reports because you can't use a passthrough query for a sub-report.
However, even if you call an ADP module from a MDB file, this ADP module
must still be a standard and ordinary ADP module; so the answer to the first
part of your sentence is No: you still have to convert them if you want to
use them from an ADP module/form/report.

Post by Geekette
Only one staff member
requires this
functionality so the network traffic would be minimized.

Not sure if converting your application to an ADP is the best option here if
your problem is only one staff member. Probably that you would want to use
a Remote Desktop instead. Much more easier to install and this will cost
you a lot less than designing an ADP project.
You should also give a closer look to your current MDB application. Maybe
they are some major flaws there that everyone will gain to have them solved.

Post by Geekette
That way I'd
strictly use the .adp file for data entry. What are your thoughts on this?
Some tables contain over a million records and most queries are multiple
tables based with various join conditions.

No problem. Nothing forbid you to use both MDB files with ODBC linked
tables and ADP projects against the same SQL-Server Database. In fact, you
can even trow other applications with other technologies such as .NET and/or
Classic ASP into the mix.

Post by Geekette
Otherwise.
If I must convert my queries to views or stored procedures, are there a
tools to help me with this process? Or do you have recommendations on books
or software I could purchase to ease the process? I am not as SQL savvy as I
use to be.

The best tool is still your ten little fingers. Don't expect to succeed
into this without having to acquire some knowledge about ADO, ADP and
SQL-Server. Also, the newsgroup for ADP is
microsoft.public.access.adp.sqlserver .
Finally, think twice about what you have already in your hands before
thinking about something else. If you don't know what you're doing; ask
someone who knows. There is no shame in not beeing a database specialist.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

[unknown]

Post by Geekette
Q1: No the updates are not multi-step. Thanks.
Q3: I mentioned that existing reports are used by one staff person. I
currently have 5 staff using the database for data entry. However, 20 -30
new
users throughout the organization have requested read-only or read-write
permission on the same database which is another reason for the upgrade. I'm
a self-taught Access database developer of 15 years but I admit I have no
programming experience with SQL and have never used Access Project. I have
one ORACLE table currently ODBC linked to the database so I know how to do
that part. After a ton of reading I figure that converting to .adp shouldn't
be difficult, I understand the concept of the migration steps and conversion
needs. In your opinion, based on what I've stated, do you think it is
necessary to convert to .adp? Or should I continue to use .mdb.I've read
that
.mdb will slow down queries and reports .ADP is faster.is that correct?

It's impossible for me to tell how much load your 30 new users will put on
the network. It all depends on the type of work that they will be doing.
You'll have to make your own tests and I cannot give you any garantee at all
that using a MDB file or an ADP project will suit your needs.

ADP is faster but it's also more complicated than simply using ODBC linked
tables. Start with a MDB file to see how things are going and at the same
time, start learning and making tests with ADP. You can also take a look at
other technologies such as .NET or using an Intranet.

Even with a MDB file, you can solve much of your speed problem by using
Terminal Server (TS) or Thinsoft to do multiple desktop remoting. Using
remoting is the simpler solution and it always works; however, using it will
cost you some $$$. Also, with 30 users, it will require a powerful server.

Post by Geekette
I agree with your statement about acquiring more knowledge. This is all very
new to me so I appreciate the direction. I've picked out 5 SQL courses to
take in the upcoming months and I'll be using MS Support to assist me with
my
questions. Once the back-end is on SQL, I will create new front-end forms and
reports to suit the needs or each department.

You're welcome.

[Mary Chipman [MSFT]]

Here are some additional resources that may help you decide on the
right path to take when migrating to SQL Server. --Mary

TechEd Online Panel:
Go to http://msdn.microsoft.com/en-us/events/teched/cc676818.aspx and
search for:
"Are we there yet? Successfully navigating the bumpy road from Access
to SQL Server"

Microsoft Access or SQL Server 2005: What's Right in Your
Organization?
http://www.microsoft.com/sql/solutions/migration/access/sql-or-access.mspx

Optimizing Microsoft Office Access Applications Linked to SQL Server
http://msdn.microsoft.com/en-us/library/bb188204.aspx

What are the main differences between Access and SQL Server?
http://sqlserver2000.databases.aspfaq.com/what-are-the-main-differences-between-access-and-sql-server.html

"The Best of Both Worlds--Access MDBs and SQL Server"
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp

SQL Server Migration Assistant for Access (SSMA for Access)
http://www.microsoft.com/sql/solutions/migration/access/default.mspx

FMS Upsizing Center
http://www.fmsinc.com/Consulting/sqlupsizedocs.aspx

Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/dp/0672319446

On Fri, 24 Oct 2008 08:58:08 -0700, Geekette

Post by Geekette
Q1: No the updates are not multi-step. Thanks.
Q3: I mentioned that existing reports are used by one staff person. I
currently have 5 staff using the database for data entry. However, 20 –30 new
users throughout the organization have requested read-only or read-write
permission on the same database which is another reason for the upgrade. I’m
a self-taught Access database developer of 15 years but I admit I have no
programming experience with SQL and have never used Access Project. I have
one ORACLE table currently ODBC linked to the database so I know how to do
that part. After a ton of reading I figure that converting to .adp shouldn’t
be difficult, I understand the concept of the migration steps and conversion
needs. In your opinion, based on what I’ve stated, do you think it is
necessary to convert to .adp? Or should I continue to use .mdb…I’ve read that
.mdb will slow down queries and reports .ADP is faster…is that correct?
I agree with your statement about acquiring more knowledge. This is all very
new to me so I appreciate the direction. I’ve picked out 5 SQL courses to
take in the upcoming months and I’ll be using MS Support to assist me with my
questions. Once the back-end is on SQL, I will create new front-end forms and
reports to suit the needs or each department.

Post by unknown

Post by Geekette
To make my application run faster and provide greater security, I would like
to migrate my backend Access 2000.mdb tables to SQL while using Access
Project as the front end. My Access (.mdb) database is currently split. The
backend tables are on a server and the front-end application is local to 5
user workstations.
Q1: For confirmation...after the migration process I should be able to add,
edit or delete data in my .adp forms the same way I did in the forms before
the migration from my .mdb right? Or will I need INSERT, DELETE, UPDATE
statements behind my form objects to do these tasks?

Yes if your updates are not multi-step; ie., you can design your forms and
subforms in such a way that only one table is updated each time.
Theoritically, it's possible to use multi-step with ADO and ADP (ADP is
based on ADO) but personnally, I think that the only thing that you will
probably get out of the multi-step feature of ADO will be bugs.

Post by Geekette
Q2: I've used Visual Basic functions and event procedures throughout the
Access.mdb application, will this be affected once converted to .adp?

Probably yes unless you are making call to DAO in your code. If yes, these
will have to be translated to ADO or you will have to create a dummy DAO
database pointing toward the SQL-Server Database.

Post by Geekette
Q3: Instead of converting my queries into views or stored procedures, could
I not create a separate .mdb file that points/links to my .adp file and run
my queries/report from there?

Yes for the final part of your sentence. Many people are calling ADP
reports from a MDB file in order to not have to use passthrough queries with
reports because you can't use a passthrough query for a sub-report.
However, even if you call an ADP module from a MDB file, this ADP module
must still be a standard and ordinary ADP module; so the answer to the first
part of your sentence is No: you still have to convert them if you want to
use them from an ADP module/form/report.

Post by Geekette
Only one staff member
requires this
functionality so the network traffic would be minimized.

Not sure if converting your application to an ADP is the best option here if
your problem is only one staff member. Probably that you would want to use
a Remote Desktop instead. Much more easier to install and this will cost
you a lot less than designing an ADP project.
You should also give a closer look to your current MDB application. Maybe
they are some major flaws there that everyone will gain to have them solved.

Post by Geekette
That way I'd
strictly use the .adp file for data entry. What are your thoughts on this?
Some tables contain over a million records and most queries are multiple
tables based with various join conditions.

No problem. Nothing forbid you to use both MDB files with ODBC linked
tables and ADP projects against the same SQL-Server Database. In fact, you
can even trow other applications with other technologies such as .NET and/or
Classic ASP into the mix.

Post by Geekette
Otherwise.
If I must convert my queries to views or stored procedures, are there a
tools to help me with this process? Or do you have recommendations on books
or software I could purchase to ease the process? I am not as SQL savvy as I
use to be.

The best tool is still your ten little fingers. Don't expect to succeed
into this without having to acquire some knowledge about ADO, ADP and
SQL-Server. Also, the newsgroup for ADP is
microsoft.public.access.adp.sqlserver .
Finally, think twice about what you have already in your hands before
thinking about something else. If you don't know what you're doing; ask
someone who knows. There is no shame in not beeing a database specialist.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)