upsizing a split access database

[Ray_s]

Hi,

we have an access database split into 2 databases: Q for Queries, and D for
Data.
1 - I upsized D to sql server.then created a Unisque index (identity) on
each sql table.
2 - Created a File type DSN to point to the Sql database.
3 - what are the next steps to do in order to make the queries in the Q
database to work off the sql tables?

Thank you for your help.

[unknown]

You change the ODBC linked tables inside the Q database to point toward the
SQL-Server database instead of pointing toward the older D database. At
this point, if you don't use pessimistic locking in your database, probably
that you will have finished most of the work required for having a working
FE/BE; all that will be left will be probably to make sure that you don't
have any trouble with the Bit field (making sure that they cannot be null
and have a default value of either 0 or 1 should take care of that; a the
only condition that the latest service pack and hotfixe have been applied)
and to add the option dbSeeChanges here and there in your Execute command
(Access will tell you where when you'll try to execute the VBA code).

After that, you might have other things to do like checking the performance
but this is another story.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Ray_s
Hi,
we have an access database split into 2 databases: Q for Queries, and D
for Data.
1 - I upsized D to sql server.then created a Unisque index (identity) on
each sql table.
2 - Created a File type DSN to point to the Sql database.
3 - what are the next steps to do in order to make the queries in the Q
database to work off the sql tables?
Thank you for your help.

[Ray_s]

Sylvain,

Thank you for your reply. my knowledge of Access is limited, and I was not
able to use the DSN with the Q access database to point to the SQL database.
All I was able to do is to create, in the Q database, linked tables (to the
sql database ) ; but each time I click on a query I get an error. showing
that the query is still pointiung to the access .accdb file . Could you
please tell me the few clicks to get this linking (from Q to Sql) to work?

thanks


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>

Post by unknown
You change the ODBC linked tables inside the Q database to point toward
the SQL-Server database instead of pointing toward the older D database.
At this point, if you don't use pessimistic locking in your database,
probably that you will have finished most of the work required for having
a working FE/BE; all that will be left will be probably to make sure that
you don't have any trouble with the Bit field (making sure that they
cannot be null and have a default value of either 0 or 1 should take care
of that; a the only condition that the latest service pack and hotfixe
have been applied) and to add the option dbSeeChanges here and there in
your Execute command (Access will tell you where when you'll try to
execute the VBA code).
After that, you might have other things to do like checking the
performance but this is another story.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Ray_s
Hi,
we have an access database split into 2 databases: Q for Queries, and D
for Data.
1 - I upsized D to sql server.then created a Unisque index (identity) on
each sql table.
2 - Created a File type DSN to point to the Sql database.
3 - what are the next steps to do in order to make the queries in the Q
database to work off the sql tables?
Thank you for your help.

[unknown]

Delete the older ODBC link and recreate them using the same names as before
or use the Linked Table Managers that you will find under Database Tools.
Don't forget to check the "Always prompt for new location" option at the
bottom. If you elect to create a new DSN, don't forget to create a System
DSN and not an User DSN. You might have problem with the authentification
mecanism later if you don't chose the right options. Finally, there are the
newsgroups microsoft.public.access.odbcclientsvr and m.p.a.externaldata if
you have other problems with your ODBC links.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Ray_s
Sylvain,
Thank you for your reply. my knowledge of Access is limited, and I was not
able to use the DSN with the Q access database to point to the SQL
database. All I was able to do is to create, in the Q database, linked
tables (to the sql database ) ; but each time I click on a query I get
an error. showing that the query is still pointiung to the access .accdb
file . Could you please tell me the few clicks to get this linking (from Q
to Sql) to work?
thanks
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>

Post by unknown
You change the ODBC linked tables inside the Q database to point toward
the SQL-Server database instead of pointing toward the older D database.
At this point, if you don't use pessimistic locking in your database,
probably that you will have finished most of the work required for having
a working FE/BE; all that will be left will be probably to make sure that
you don't have any trouble with the Bit field (making sure that they
cannot be null and have a default value of either 0 or 1 should take care
of that; a the only condition that the latest service pack and hotfixe
have been applied) and to add the option dbSeeChanges here and there in
your Execute command (Access will tell you where when you'll try to
execute the VBA code).
After that, you might have other things to do like checking the
performance but this is another story.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Ray_s
Hi,
we have an access database split into 2 databases: Q for Queries, and D
for Data.
1 - I upsized D to sql server.then created a Unisque index (identity) on
each sql table.
2 - Created a File type DSN to point to the Sql database.
3 - what are the next steps to do in order to make the queries in the Q
database to work off the sql tables?
Thank you for your help.

[Ray_s]

connecting to the sql tables works fine: double clicking on the linked
tables (from the Q database) opens the sql tables.
the issue remains with the queries. I am not sure how to make the queries
point to the sql database.
Linked Table Manager just creates / sets linked tables - which works fine -
but does not seem to have any effect on the queries.
it is too bad that there is no a Microsoft docuement that tells - in
details - how to upsize a split access database; or I could not find such
document.

Thank you again.

"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>

Post by unknown
Delete the older ODBC link and recreate them using the same names as
before or use the Linked Table Managers that you will find under Database
Tools. Don't forget to check the "Always prompt for new location" option
at the bottom. If you elect to create a new DSN, don't forget to create a
System DSN and not an User DSN. You might have problem with the
authentification mecanism later if you don't chose the right options.
Finally, there are the newsgroups microsoft.public.access.odbcclientsvr
and m.p.a.externaldata if you have other problems with your ODBC links.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Ray_s
Sylvain,
Thank you for your reply. my knowledge of Access is limited, and I was
not able to use the DSN with the Q access database to point to the SQL
database. All I was able to do is to create, in the Q database, linked
tables (to the sql database ) ; but each time I click on a query I get
an error. showing that the query is still pointiung to the access .accdb
file . Could you please tell me the few clicks to get this linking (from
Q to Sql) to work?
thanks
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>

Post by unknown
You change the ODBC linked tables inside the Q database to point toward
the SQL-Server database instead of pointing toward the older D database.
At this point, if you don't use pessimistic locking in your database,
probably that you will have finished most of the work required for
having a working FE/BE; all that will be left will be probably to make
sure that you don't have any trouble with the Bit field (making sure
that they cannot be null and have a default value of either 0 or 1
should take care of that; a the only condition that the latest service
pack and hotfixe have been applied) and to add the option dbSeeChanges
here and there in your Execute command (Access will tell you where when
you'll try to execute the VBA code).
After that, you might have other things to do like checking the
performance but this is another story.
--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

Post by Ray_s
Hi,
we have an access database split into 2 databases: Q for Queries, and D
for Data.
1 - I upsized D to sql server.then created a Unisque index (identity)
on each sql table.
2 - Created a File type DSN to point to the Sql database.
3 - what are the next steps to do in order to make the queries in the Q
database to work off the sql tables?
Thank you for your help.

[unknown]

Post by Ray_s
connecting to the sql tables works fine: double clicking on the linked
tables (from the Q database) opens the sql tables.
the issue remains with the queries. I am not sure how to make the queries
point to the sql database.
Linked Table Manager just creates / sets linked tables - which works
fine - but does not seem to have any effect on the queries.
it is too bad that there is no a Microsoft docuement that tells - in
details - how to upsize a split access database; or I could not find such
document.
Thank you again.

Excerpt for passthrough queries (and you're not supposed to have any because
you have just upsized this database); you don't have to do anything with the
queries. They should work fine if you have used the same names for the new
linked tables as the older tables/linked tables. If the names are
different, rename them (locally) or change the query in order to use the new
names.

It's the names of the links as they are defined locally that you must use;
not the names of the tables as they are named on the server.

[Armen Stein]

On Thu, 23 Oct 2008 14:44:14 -0400, "Sylvain Lafontaine" <sylvain aei

Post by unknown
At
this point, if you don't use pessimistic locking in your database, probably
that you will have finished most of the work required for having a working
FE/BE;

Yes, probably. There are a few things to check though:

- make sure that you aren't retrieving the value of an Identity
(Autonumber in Access) after the .Add method in a recordset. When the
table is in SQL Server, the key isn't generated until after the
.Update method. So if you're doing this, you'll need to use the
.LastModified bookmark to get the newly created key. By the way, this
method works for Access tables too, so there's no harm in using it
regardless of the backend database.

- make sure that the upsizing placed a RowVersion (aka Timestamp)
field into every table. You don't need to reference it anywhere - it
just needs to exist. This at least speeds up updates, and at best can
prevent update failures.

Then you'll need to check performance throughout your application.
Moving to SQL Server will speed up some areas, but can slow down
others.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com