P
Pete
I have inherited a poorly written Access database that I am about to rewrite
so that data structure has proper referential integrity, is normalised and
follows proper naming conventions. There is a possbility that the back-end
will get migrated to SQL Server in the future to gain better performance, and
more importantly for us, better control with regard to security over who can
view and update what.
I therefore wish to rewrite the front and back end mdbs in such a way that
if the back-end is moved to SQL Server the front end will work with little
(if any) modifications. Ideally I want to create identical tables in SQL
Server and then just link to these. I have only had very limited SQL Server
experience so wish to know what the pitfalls to look out for are.
I have made sure all my tables have autonumber primary keys (as SQL Server
seems to need these when writing updates back).
I have heard of timestamp fields, but am not sure uder what circumstances
these are required.
There is an option in Access to make queries ANSI 92 compliant, presumably I
have to check this. I know SQL uses % instead of * as wildcards. Also yes/no
fields work differently.
Am I being realistic in attempting this? What other 'gotchas' are there?
The reason for not moving to SQL Server yet is licensing costs. Access works
OK as we don't have that many concurrent users, although I much prefer the
security control of SQL Server. I am trying to convince my boss that a move
to SQL Server is justified - I am interested to hear what people think of the
new SQL Reporting Services as I have not seen it yet.
so that data structure has proper referential integrity, is normalised and
follows proper naming conventions. There is a possbility that the back-end
will get migrated to SQL Server in the future to gain better performance, and
more importantly for us, better control with regard to security over who can
view and update what.
I therefore wish to rewrite the front and back end mdbs in such a way that
if the back-end is moved to SQL Server the front end will work with little
(if any) modifications. Ideally I want to create identical tables in SQL
Server and then just link to these. I have only had very limited SQL Server
experience so wish to know what the pitfalls to look out for are.
I have made sure all my tables have autonumber primary keys (as SQL Server
seems to need these when writing updates back).
I have heard of timestamp fields, but am not sure uder what circumstances
these are required.
There is an option in Access to make queries ANSI 92 compliant, presumably I
have to check this. I know SQL uses % instead of * as wildcards. Also yes/no
fields work differently.
Am I being realistic in attempting this? What other 'gotchas' are there?
The reason for not moving to SQL Server yet is licensing costs. Access works
OK as we don't have that many concurrent users, although I much prefer the
security control of SQL Server. I am trying to convince my boss that a move
to SQL Server is justified - I am interested to hear what people think of the
new SQL Reporting Services as I have not seen it yet.