SQL Sever based Access issues ?

[David]

I've been told my system (already under development) has
to have its tables linked to SQL Server.
Does anyone know if there are guidelines on how this
should be done?
I understand there are some considerations regarding the
format of columns and how updates are performed, that
require the Access code to be different.
I've been told there may be problems with date fields
defined as date/time in Access.
Any advice appreciated.
- David

 

[Tim Ferguson]

I've been told my system (already under development) has
to have its tables linked to SQL Server.

Does anyone know if there are guidelines on how this
should be done?

There are loads of papers on the MS knowledge base: try searching on "MSDE"
or "Access upsizing". It's better, in my view, to design from the ground up
as a ADP file rather than converting an MDB -- see below.

You can install MSDE to develop and test against, and then roll it out to
the full SQL server when finished. It can be quite fun! Get used to views
and stored procedures rather than queries, and use triggers to solve
difficult update problems.

I understand there are some considerations regarding the
format of columns and how updates are performed, that
require the Access code to be different.

There are subtle differences in data types, but nothing that would
generally break a robust Jet database. You have to get used to a slightly
different SQL syntax: for example, the wildcards are "%" and "_" rather
than "*" and "?"; all strings are delimited by single quotes and never
double ones; there is absolutely no VBA in queries, but you do get a much
richer SQL environment; and so on and so on.

I've been told there may be problems with date fields
defined as date/time in Access.

Not AFAIK.

For specific problems, try the m.p.a.adpsqlserver newsgroup.

HTH


Tim F

 

[David]

Thanks for your valuable input.
Are there any issues with Access memo fields that you know
of.
- David

 

[Kevin3NF]

An Access memo field is most closely related to a text datatype (which is
different than the Access "text" datatype). If the memo contents are under
8000 bytes, you can use a varchar datatype.

--
Kevin Hill
President
3NF Consulting

www.3nf-inc.com/NewsGroups.htm