Sql – From SQL Server to MS Access 2007

ms-accessms-access-2007sql server

Could you recommend a good resource on learning MS Access from the perspective of a developer with a good background in SQL Server.

Also some best practice tips would be welcome, in areas such as

multi-user access support
security; both the database and the front end Access interface (Forms,Reports)
deployment

Basically if you have experience in both SQL Server and MS Access development what best practice advice would you give to your fellow developer with SQL Server experience that needs to develop an application in MS Access (both the database and the front end).

The application in question will be hosted on the server and shared by multiple users (10 to 20). It needs to be written exclusively in MS Access 2007. Data to be accessed with the use of Forms and Reports (no direct access to the tables). There will be 3 different user types – which implies different access rights. All within an Intranet set-up.

I realise that the question is quite broad but I am hopping to use the answers as a good starting point and I will appreciate any input on this

Thank you

EDIT

Thank you for you answers so far. Just wanted to clarify that in this particular scenario, MS Access is the only option

Best Answer

Unfortunately Access suffers from bad press and hearsay, much of it very out-of-date indeed.

There are some useful notes here:

MS Access 2003 - Good book on learning advanced VBA

Is MS Access (JET) suitable for multiuser access?

Setting up an MS-Access DB for multi-user access

https://stackoverflow.com/questions/469799/what-are-the-appropriate-uses-for-ms-access

There are a number of other useful threads within the ms-access tag, written by people with a fuller experience of Access than seems to be evinced in some of the comments.

I would imagine the main difficulty for someone with SQL-server experience will be adjusting to the more limited Jet/Ace SQL.

Syntax:

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example:

ALTER TABLE SomeTable
        ADD SomeCol Bit NULL --Or NOT NULL.
 CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is autogenerated.
    DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Notes:

Optional Constraint Name:
If you leave out CONSTRAINT D_SomeTable_SomeCol then SQL Server will autogenerate
a Default-Contraint with a funny Name like: DF__SomeTa__SomeC__4FB7FEF6

Optional With-Values Statement:
The WITH VALUES is only needed when your Column is Nullable
and you want the Default Value used for Existing Records.
If your Column is NOT NULL, then it will automatically use the Default Value
for all Existing Records, whether you specify WITH VALUES or not.

How Inserts work with a Default-Constraint:
If you insert a Record into SomeTable and do not Specify SomeCol's value, then it will Default to 0.
If you insert a Record and Specify SomeCol's value as NULL (and your column allows nulls),
then the Default-Constraint will not be used and NULL will be inserted as the Value.

Notes were based on everyone's great feedback below.
Special Thanks to:
@Yatrix, @WalterStabosz, @YahooSerious, and @StackMan for their Comments.

Sql – How to return only the Date from a SQL Server DateTime datatype

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, @your_date))

for example

SELECT DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))

gives me

2008-09-22 00:00:00.000

Pros:

No varchar<->datetime conversions required
No need to think about locale

Best Answer

Related Solutions

Sql – Add a column with a default value to an existing table in SQL Server

Syntax:

Example:

Notes:

Sql – How to return only the Date from a SQL Server DateTime datatype

Related Topic