Like the subject says, I want to use Access as a front-end for MSSQL Server.
How easy is this on a scale of 1 to 10?

Easy to do, very...

Easy to make work wonderfully, however, there are a couple of tricks
you need to be aware of

1) Add a timestamp to every table (thanks to JStreet for this tip) as
it will help prevent locked table issues
2) use Pass through queries as often as possible.

they are probably the 2 biggest things to watch out for.

Hope that helps,
Kind Regards
Anthony Moore

IT Excellence

On Mon, 05 Nov 2007 22:37:48 -0800, Anthos
<(E-Mail Removed)> wrote:

Isn't it true that timestamp columns only help if tables don't have
PKs? Which they should?

Anthony seems to be indicating an MDB or ACCDB with linked tables.
That works, but requires table access for your users. In
security-conscious environments average users don't have access to
tables and this solution would not be an option.
I also hate that this solution requires an ODBC connection to the
database. ODBC was hot in the early nineties, and has been in
maintenance mode since the late 90's.
Access Data Project (ADP) comes to mind as it was specifically
designed to create access applications with a sql back-end. The
complexity level goes up because you now have to know T-SQL to create
views and stored procedures. It works with ADO as the data layer,
another technology that is in maintenance mode since the early 00's,
and importantly it appears that MSFT has lost interest in it with
Access 2007. Especially the A2007 runtime is unusable with ADP as it
stands today.


>Easy to do, very...
>
>Easy to make work wonderfully, however, there are a couple of tricks
>you need to be aware of
>
>1) Add a timestamp to every table (thanks to JStreet for this tip) as
>it will help prevent locked table issues
>2) use Pass through queries as often as possible.
>
>they are probably the 2 biggest things to watch out for.
>
>Hope that helps,
>Kind Regards
>Anthony Moore
>
>IT Excellence

Thanks for your post Tom,
However, I disagree with your previous comment on Primary Keys, as I
have explained below
Even if your table has primary keys, it may still think that the value
has changed on floating point values (as these don't calculate to be
exactly the same each time) and as a result, it may think that the
record was changed by someone else.

In actual fact, if your table in SQL doesn't have a Primary Key, it
will be Read Only.
The time stamp field type in SQL will help resolve any issues with
Locked Records.

I was at a Conference on the weekend that showed the difference
between using MS Access as the backend for the data, and using MS SQL.
The difference in timing was incredible, instantanious for pass
through queries, in a standard MS Access application, compared to 20+
seconds for the exact same query when the query was being run from MS
Access.

(The SQL server was also running on the local machine, so it wasn't
that the server had more resources to do the calculations)

Sure, this solution does require an ODBC connection, but there are
ways to handle this, and this is a very effective way to develop MS
Access Applications (and personally, it is the way that I use)


Regards,
Anthony Moore
IT Excellence

"Tom van Stiphout" <(E-Mail Removed)> wrote in message
news(E-Mail Removed)...
> On Mon, 05 Nov 2007 22:37:48 -0800, Anthos
> <(E-Mail Removed)> wrote:
>
> Isn't it true that timestamp columns only help if tables don't have
> PKs? Which they should?
>

Actually, it good idea to have both. ms-access actually has to "test"
against each field to determine record changes...if there is a timestamp
field...it uses that.

I seen some sub-form act VERY flaky with sql server, and including timestamp
fields seemed to fix the problem.

So, as recommending...I do suggest having both pk, and a timestamp for all
of the tables when using sql server.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)

> Anthony seems to be indicating an MDB or ACCDB with linked tables.
> That works, but requires table access for your users. In
> security-conscious environments average users don't have access to
> tables and this solution would not be an option.
> I also hate that this solution requires an ODBC connection to the
> database. ODBC was hot in the early nineties, and has been in
> maintenance mode since the late 90's.
> Access Data Project (ADP) comes to mind as it was specifically
> designed to create access applications with a sql back-end

I would like to use ADP because I don't like the idea of linked tables
either. I want to do an ADP, but my IT department is telling me that I
can't use a VPN to connect our users, who love Access, to SQL Server because
of security. What options are available to me in this area? What about a
thin client, can that open up a secure socket for Access to use?

"Anthos" <(E-Mail Removed)> wrote in message

>
> (The SQL server was also running on the local machine, so it wasn't
> that the server had more resources to do the calculations)

That is a strange result. when you compare SQL server and jet on the same
machine jet is useally about 40% faster. And, often is as much as two times
faster.

Remember with jet you have a program that reads data directly from a file on
the disk drive. With SQL server you have a socket based i/p connection that
you must communicate through.

As a general rule with the exception of some text based searching type
options that are available in SQL server, jet will usealy perform
considerably better than SQL server on the same hardware and the same
machine.

I suspect the demonstration was one of those cases where the person was not
really good with JET. I would love to see what they were doing in that demo.
I'm still very skeptical of the results.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
(E-Mail Removed)

mttmwsn wrote:
> > Anthony seems to be indicating an MDB or ACCDB with linked tables.
> > That works, but requires table access for your users.

Well, the linked tables part does, but using an MDB with ODBC does not mean that
you have to link directly to tables.

> In
> > security-conscious environments average users don't have access to
> > tables and this solution would not be an option.

You seem to be saying that to be security conscious that all access must be via
views and stored procedures. That is ONE way to deal with security, but it is
certainly not the only way or even the method used most of the time. One can
grant exactly the amount of table access that you want each individual user to
have from zero to full. That granularity of security should meet the needs of
anyone setting up a database.

> > I also hate that this solution requires an ODBC connection to the
> > database. ODBC was hot in the early nineties, and has been in
> > maintenance mode since the late 90's.

Internal MS experts have changed the tune and now recommend ODBC and linked
tables over ADP with OLEDB. Arguing against things that work just because of
their vintage is not sound reasoning.

> > Access Data Project (ADP) comes to mind as it was specifically
> > designed to create access applications with a sql back-end

You have to grant equivelant table access with ADPs (possibly more) then you do
with linked tables (according to other posts I have read in these groups).

> I would like to use ADP because I don't like the idea of linked tables
> either. I want to do an ADP, but my IT department is telling me
> that I can't use a VPN to connect our users, who love Access, to SQL
> Server because of security. What options are available to me in this
> area? What about a thin client, can that open up a secure socket for
> Access to use?

I don't think that the choice between using a VPN or direct port access to SQL
Server has anything at all to do with the choice between MDB/ADP. The issue is
identical in both cases.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

On Nov 8, 7:40 pm, "Albert D. Kallal" <PleaseNOOOsPAMmkal...@msn.com>
wrote:
> "Anthos" <anth...@itexcellence.com.au> wrote in message
>
> > (The SQL server was also running on the local machine, so it wasn't
> > that the server had more resources to do the calculations)
>
> That is a strange result. when you compare SQL server and jet on the same
> machine jet is useally about 40% faster. And, often is as much as two times
> faster.
>
> Remember with jet you have a program that reads data directly from a file on
> the disk drive. With SQL server you have a socket based i/p connection that
> you must communicate through.
>
> As a general rule with the exception of some text based searching type
> options that are available in SQL server, jet will usealy perform
> considerably better than SQL server on the same hardware and the same
> machine.
>
> I suspect the demonstration was one of those cases where the person was not
> really good with JET. I would love to see what they were doing in that demo.
> I'm still very skeptical of the results.
>
> --
> Albert D. Kallal (Access MVP)
> Edmonton, Alberta Canada
> pleaseNOOSpamKal...@msn.com

The Demonstartion was done by Armen Stein an MS Access MVP and was a
agregation query straight out of the northwinds Database.

Depending on your database,
It is possible to configure MS Access to talk to Sharepoint, and to
take data offline and sync it again when it comes back online.

However, I would recommend a very concise look at your database before
doing this method.
(this also wouldn't require access via VPN)