Access migration to SQL Server

[Teri Welch]

Hello,

We maintain a VB6 front-end application using an Access 2000 database. All
code and forms are in VB6. The program also uses several queries/reports
defined in Access. For corporate reasons we must move to SQL Server right
away. Based on our configuration, we're hoping someone can give us a feel
for what's involved here. Since we're only using Access for the data we're
not sure what an "upsizing" would entail. Can someone point us in the right
direction, tell us the main gotchas, etc. Thanks a lot.

Teri Welch

[John Bell]

Hi

If you are going to remove access completely your access queries will need
to be re-written as stored procedures or views and the reports rewritten in
(say) VB. Alternatively you can use an Access Data Project where most of the
code can remain the same.

The access upgrade wizard will migrate most things, although it can run into
problems if you are using something that is more complex.

I suggest that you create a test system and copy your database and
application onto it. Then you try the upgrade, and you will get a feel for
what is involved and how much can be easily migrated.

John

"Teri Welch" <tr****@yahoo.com> wrote in message
news:xLuwd.5516$jn.1131@lakeread06...

Hello,

We maintain a VB6 front-end application using an Access 2000 database. All
code and forms are in VB6. The program also uses several queries/reports
defined in Access. For corporate reasons we must move to SQL Server right
away. Based on our configuration, we're hoping someone can give us a feel
for what's involved here. Since we're only using Access for the data we're
not sure what an "upsizing" would entail. Can someone point us in the
right
direction, tell us the main gotchas, etc. Thanks a lot.

Teri Welch

[Ryan]

There are a number of tools you can use to upsize the database, but in
my experience, I would tend to do this manually to ensure everything in
the structure is correct.

Your main issue will be with the queries as the syntax can be slightly
different to SQL. It's only subtle, but you would need to address this.

Personally, I would manually create the tables (inc indexes etc...)
then move the data using the import wizard and do any manipulation you
need to get this correctly into the tables. Then, I'd address each
query step by step in VB.

There shouldn't be too many issues (as a general statement). It may be
worthwhile looking at using views and stored procedures as these may
give you additional benefits.

What you will find is that once done, it's probably quicker and much
less likely to have issues with record locking as SQL handles this much
better.

With your reports, I would imagine you will leave these in Access, set
up an ODBC link to your SQL tables in access and call the reports
through VB as you will currently do. All you need to do is swap the
links over and it should be straight forward. You may do this and then
think about leaving the links in for the queries, but I would advise
not to on experience and for simplicity.

I'm sure if there are any specific problems, a number of people on this
NG will help, if not, try...

http://groups.google.com/groups?hl=e...ases.ms-access

Ryan
Teri Welch wrote:

Hello,

We maintain a VB6 front-end application using an Access 2000 database. All code and forms are in VB6. The program also uses several queries/reports defined in Access. For corporate reasons we must move to SQL Server right away. Based on our configuration, we're hoping someone can give us a feel for what's involved here. Since we're only using Access for the data we're not sure what an "upsizing" would entail. Can someone point us in the right direction, tell us the main gotchas, etc. Thanks a lot.

Teri Welch

[Steve Jorgensen]

On Fri, 17 Dec 2004 08:34:55 -0000, "John Bell" <jb************@hotmail.com>
wrote:

Hi

If you are going to remove access completely your access queries will need
to be re-written as stored procedures or views and the reports rewritten in
(say) VB. Alternatively you can use an Access Data Project where most of the
code can remain the same.
I have to say it - bah! There - I said it.

Every single solitary time I've seen someone post on Usenet asking about
upsizing an Access database to SQL Server, the first response says something
about having to convert all your queries to views and stored procedures.
Frankly, that's not at all necessary, and a serious drain on time and money.

Access/DAO mostly does a fine job of translating queries of linked tables into
reasonable server-side SQL for execution, and it creates prepared statements
for parameter queries, so SQL Server won't have to keep recompiling each query
each time it sees it. Furthermore, Access is quite happy doing things like
using a named query parameter to look up a value from a form control and
allowing editing of values from multiple tables in a query with a join.

If you convert all these things to views and stored procedures, you have to
figure out how to do things like bind forms to parameterized stored
procedures, and implement extra code and forms for editing that were never
needed before, etc. It's a huge, complex, error prone exercise that forces
you to add complexity and remove convinience in the UI, all for basically no
return on investment.

Now, you're going to say that sometimes Access does not do a good job of
producing the server-side SQL. Yup, that's true, so in -those- cases, using a
view and/or a stored procedure can be very beneficial. You're also going to
say that if you have multiple systems accessing the same back-end, you want to
make the database interactions consistent and centralize business logic. I
say, yes, but again, do that incrementally as needed for specific cases. If
you really need to centralize a -lot- of bunsiness logic, T-SQL is not a great
language for it anyway, and you probably should think about a 3-tier system
and dump the Access UI altogether.
The access upgrade wizard will migrate most things, although it can run into
problems if you are using something that is more complex.
Here's a case where I do recommend doing it the "hard way. I've never seen
the upsizing wizard do a particularly good job. For each table, you should be
deciding what should be your clustered index, whether you need a TIMESTAMP
field, checking for NULL in Boolean fields, etc.
I suggest that you create a test system and copy your database and
application onto it. Then you try the upgrade, and you will get a feel for
what is involved and how much can be easily migrated.

An excellent suggestion - I agree.

[John Bell]

Hi Steve

I don't disagree with anything you said, but you seemed to have missed the
caveat in the first sentence, i.e. remove access all together and you don't
really have any choice.

John

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:kd********************************@4ax.com...

On Fri, 17 Dec 2004 08:34:55 -0000, "John Bell"
<jb************@hotmail.com>
wrote:

Hi

If you are going to remove access completely your access queries will need
to be re-written as stored procedures or views and the reports rewritten
in
(say) VB. Alternatively you can use an Access Data Project where most of
the
code can remain the same.

I have to say it - bah! There - I said it.

Every single solitary time I've seen someone post on Usenet asking about
upsizing an Access database to SQL Server, the first response says
something
about having to convert all your queries to views and stored procedures.
Frankly, that's not at all necessary, and a serious drain on time and
money.

Access/DAO mostly does a fine job of translating queries of linked tables
into
reasonable server-side SQL for execution, and it creates prepared
statements
for parameter queries, so SQL Server won't have to keep recompiling each
query
each time it sees it. Furthermore, Access is quite happy doing things
like
using a named query parameter to look up a value from a form control and
allowing editing of values from multiple tables in a query with a join.

If you convert all these things to views and stored procedures, you have
to
figure out how to do things like bind forms to parameterized stored
procedures, and implement extra code and forms for editing that were never
needed before, etc. It's a huge, complex, error prone exercise that
forces
you to add complexity and remove convinience in the UI, all for basically
no
return on investment.

Now, you're going to say that sometimes Access does not do a good job of
producing the server-side SQL. Yup, that's true, so in -those- cases,
using a
view and/or a stored procedure can be very beneficial. You're also going
to
say that if you have multiple systems accessing the same back-end, you
want to
make the database interactions consistent and centralize business logic.
I
say, yes, but again, do that incrementally as needed for specific cases.
If
you really need to centralize a -lot- of bunsiness logic, T-SQL is not a
great
language for it anyway, and you probably should think about a 3-tier
system
and dump the Access UI altogether.

The access upgrade wizard will migrate most things, although it can run
into
problems if you are using something that is more complex.

Here's a case where I do recommend doing it the "hard way. I've never
seen
the upsizing wizard do a particularly good job. For each table, you
should be
deciding what should be your clustered index, whether you need a TIMESTAMP
field, checking for NULL in Boolean fields, etc.

I suggest that you create a test system and copy your database and
application onto it. Then you try the upgrade, and you will get a feel for
what is involved and how much can be easily migrated.

An excellent suggestion - I agree.

[Steve Jorgensen]

Yup - I'd say my knee was a bit quick on the jerk there <g>.

On Fri, 17 Dec 2004 17:05:26 -0000, "John Bell" <jb************@hotmail.com>
wrote:

Hi Steve

I don't disagree with anything you said, but you seemed to have missed the
caveat in the first sentence, i.e. remove access all together and you don't
really have any choice.

John

"Steve Jorgensen" <no****@nospam.nospam> wrote in message
news:kd********************************@4ax.com.. .

On Fri, 17 Dec 2004 08:34:55 -0000, "John Bell"
<jb************@hotmail.com>
wrote:

Hi

If you are going to remove access completely your access queries will need
to be re-written as stored procedures or views and the reports rewritten
in
(say) VB. Alternatively you can use an Access Data Project where most of
the
code can remain the same.

I have to say it - bah! There - I said it.

Every single solitary time I've seen someone post on Usenet asking about
upsizing an Access database to SQL Server, the first response says
something
about having to convert all your queries to views and stored procedures.
Frankly, that's not at all necessary, and a serious drain on time and
money.

Access/DAO mostly does a fine job of translating queries of linked tables
into
reasonable server-side SQL for execution, and it creates prepared
statements
for parameter queries, so SQL Server won't have to keep recompiling each
query
each time it sees it. Furthermore, Access is quite happy doing things
like
using a named query parameter to look up a value from a form control and
allowing editing of values from multiple tables in a query with a join.

If you convert all these things to views and stored procedures, you have
to
figure out how to do things like bind forms to parameterized stored
procedures, and implement extra code and forms for editing that were never
needed before, etc. It's a huge, complex, error prone exercise that
forces
you to add complexity and remove convinience in the UI, all for basically
no
return on investment.

Now, you're going to say that sometimes Access does not do a good job of
producing the server-side SQL. Yup, that's true, so in -those- cases,
using a
view and/or a stored procedure can be very beneficial. You're also going
to
say that if you have multiple systems accessing the same back-end, you
want to
make the database interactions consistent and centralize business logic.
I
say, yes, but again, do that incrementally as needed for specific cases.
If
you really need to centralize a -lot- of bunsiness logic, T-SQL is not a
great
language for it anyway, and you probably should think about a 3-tier
system
and dump the Access UI altogether.

The access upgrade wizard will migrate most things, although it can run
into
problems if you are using something that is more complex.

Here's a case where I do recommend doing it the "hard way. I've never
seen
the upsizing wizard do a particularly good job. For each table, you
should be
deciding what should be your clustered index, whether you need a TIMESTAMP
field, checking for NULL in Boolean fields, etc.

I suggest that you create a test system and copy your database and
application onto it. Then you try the upgrade, and you will get a feel for
what is involved and how much can be easily migrated.

An excellent suggestion - I agree.

[Teri Welch]

Thanks for the replies from everyone.

1. How can we convert MS-Access import specs to SQL Server. We import many
data files from outside sources -- in both ASCII and Excel formats -- and
make heavy use of import specs within VB6 programs by invoking the
TransferText and TransferSpreadsheet functions.

2. Regarding reports, must we leave them in Access? We are inclined to
remove Access completely from the picture. Maybe that is too drastic and you
would advocate otherwise. But we question the performance of using Access
reports and the necessity to have Access open everytime a report is invoked
from within VB6. Plus, we have to keep Access installed. How can we create
the reports natively within SQL Server.

Thanks,
Teri Welch
"Ryan" <ry********@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

There are a number of tools you can use to upsize the database, but in
my experience, I would tend to do this manually to ensure everything in
the structure is correct.

Your main issue will be with the queries as the syntax can be slightly
different to SQL. It's only subtle, but you would need to address this.

Personally, I would manually create the tables (inc indexes etc...)
then move the data using the import wizard and do any manipulation you
need to get this correctly into the tables. Then, I'd address each
query step by step in VB.

There shouldn't be too many issues (as a general statement). It may be
worthwhile looking at using views and stored procedures as these may
give you additional benefits.

What you will find is that once done, it's probably quicker and much
less likely to have issues with record locking as SQL handles this much
better.

With your reports, I would imagine you will leave these in Access, set
up an ODBC link to your SQL tables in access and call the reports
through VB as you will currently do. All you need to do is swap the
links over and it should be straight forward. You may do this and then
think about leaving the links in for the queries, but I would advise
not to on experience and for simplicity.

I'm sure if there are any specific problems, a number of people on this
NG will help, if not, try...

http://groups.google.com/groups?hl=e...ases.ms-access
Ryan
Teri Welch wrote:

Hello,

We maintain a VB6 front-end application using an Access 2000

database. All

code and forms are in VB6. The program also uses several

queries/reports

defined in Access. For corporate reasons we must move to SQL Server

right

away. Based on our configuration, we're hoping someone can give us a

feel

for what's involved here. Since we're only using Access for the data

we're

not sure what an "upsizing" would entail. Can someone point us in the

right

direction, tell us the main gotchas, etc. Thanks a lot.

Teri Welch

[John Bell]

Hi Teri

"Teri Welch" <tr****@yahoo.com> wrote in message
news:s1hyd.8482$jn.4225@lakeread06...

Thanks for the replies from everyone.

1. How can we convert MS-Access import specs to SQL Server. We import many
data files from outside sources -- in both ASCII and Excel formats -- and
make heavy use of import specs within VB6 programs by invoking the
TransferText and TransferSpreadsheet functions.
You can use DTS to import from many different locations.
2. Regarding reports, must we leave them in Access? We are inclined to
remove Access completely from the picture. Maybe that is too drastic and
you
would advocate otherwise. But we question the performance of using Access
reports and the necessity to have Access open everytime a report is
invoked
from within VB6. Plus, we have to keep Access installed. How can we create
the reports natively within SQL Server.
Reporting services is provided with SQL server, but this is probably not the
type of reporting you require. There may be some third party VB/ActiveX
component that will do a similar job as your current requirements.

Thanks,
Teri Welch

John

"Ryan" <ry********@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

There are a number of tools you can use to upsize the database, but in
my experience, I would tend to do this manually to ensure everything in
the structure is correct.

Your main issue will be with the queries as the syntax can be slightly
different to SQL. It's only subtle, but you would need to address this.

Personally, I would manually create the tables (inc indexes etc...)
then move the data using the import wizard and do any manipulation you
need to get this correctly into the tables. Then, I'd address each
query step by step in VB.

There shouldn't be too many issues (as a general statement). It may be
worthwhile looking at using views and stored procedures as these may
give you additional benefits.

What you will find is that once done, it's probably quicker and much
less likely to have issues with record locking as SQL handles this much
better.

With your reports, I would imagine you will leave these in Access, set
up an ODBC link to your SQL tables in access and call the reports
through VB as you will currently do. All you need to do is swap the
links over and it should be straight forward. You may do this and then
think about leaving the links in for the queries, but I would advise
not to on experience and for simplicity.

I'm sure if there are any specific problems, a number of people on this
NG will help, if not, try...

http://groups.google.com/groups?hl=e...ases.ms-access

Ryan
Teri Welch wrote:

> Hello,
>
> We maintain a VB6 front-end application using an Access 2000

database. All

> code and forms are in VB6. The program also uses several

queries/reports

> defined in Access. For corporate reasons we must move to SQL Server

right

> away. Based on our configuration, we're hoping someone can give us a

feel

> for what's involved here. Since we're only using Access for the data

we're

> not sure what an "upsizing" would entail. Can someone point us in the

right

> direction, tell us the main gotchas, etc. Thanks a lot.
>
> Teri Welch