M
mttmwsn
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?
How easy is this on a scale of 1 to 10?
A
Anthos
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
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
T
Tom van Stiphout
On Mon, 05 Nov 2007 22:37:48 -0800, Anthos
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.
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.
A
Anthos
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
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
A
Albert D. Kallal
Tom van Stiphout said:
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.
M
mttmwsn
Anthony seems to be indicating an MDB or ACCDB with linked tables.
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 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?
A
Albert D. Kallal
Anthos said:
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.
R
Rick Brandt
Well, the linked tables part does, but using an MDB with ODBC does not mean that
you have to link directly to tables.
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.
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.
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 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.
you have to link directly to tables.
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.
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.
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 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.
A
Anthos
The Demonstartion was done by Armen Stein an MS Access MVP and was a
agregation query straight out of the northwinds Database.
A
Anthos
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)
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)
D
David W. Fenton
My understanding was that this was an ADO problem, not an ADP
problem. That is, ADO "guesses" what you want to do by rewriting
your queries to use the source tables directly. The benefit is that
some non-updatable recordsets are then writable, but by attempting
to bypass the layers of security you've put in place (e.g., with
views).
D
David W. Fenton
Where's the security risk? The whole point of a VPN *is* security,
that you can offer LAN-level access to people who are outside your
local LAN/WAN without compromising security. Are you sure you
understood what your IT people told you? Or are they, perhaps,
completely incompetent?
D
David W. Fenton
Well, sure. There are plenty of things that are not as
well-optimized in Jet as they are in SQL Server. One example would
be IN clause criteria, which with Jet is rather unreliable in its
use of indexes (it doesn't always use the indexes on both sides),
whereas SQL Server manages very well in every case I've encountered.
But you could likewise find examples of operations that Jet
optimizes better than SQL Server. And my bet is that Jet is faster
in far more cases than not when the comparison is to SQL Server
running on the same machine.
A
Albert D. Kallal
Anthos said:
Well, that kind of makes the case here!!!
There is also some types of joins that sql server will do better then jet.
On a straight read of data...JET is usually faster. But, as your example
shows, when you get into more complex aggregate quires..then sql is going to
be a benefit.....
My point was that as a general rule, on your local machine, JET usually
gives better performance then sql server since no network is at play here.
The instant you introduce networks, then sql server will MOST certainly show
benefits in speed and performance, not to mention reliability issues.
Regardless, I think today with such ease and availability of sql server, if
one is design an application in ms-access, and you think you need sql
server, then by all means go for it. Today we have many tools available in
which to use sql server. It is most easy to use and develop with a local
copy of sql server running these days, so you point is will taken.
A
Armen Stein
Microsoft recommends the timestamps in KB article 280730. This
article is for Access 2000, but the issue remains in later versions
too.
http://support.microsoft.com/kb/280730/EN-US/
We've solved show-stopper record locking issues by merely adding a
timestamp to the table. It doesn't even have to be included in
queries or form recordsources.
We include a timestamp in every SQL Server table as a standard.
Armen Stein
Microsoft Access MVP
www.JStreetTech.com
A
Armen Stein
Hi guys,
My speed demo involved subqueries. In one case the exact same query
took about 20 seconds via a linked table, but was almost instant using
a passthrough query. Here's the query (show top 10% of customers by
total sales):
SELECT *
FROM Customers
WHERE (((Customers.CustomerID) In (SELECT TOP 10 PERCENT
Customers.CustomerID
FROM (Customers INNER JOIN Orders ON Customers.CustomerID =
Orders.CustomerID) INNER JOIN [Order Details] ON Orders.OrderID =
[Order Details].OrderID
GROUP BY Customers.CustomerID
ORDER BY Sum([Order Details].[UnitPrice]*[Quantity]*[Discount])
DESC)))
My point was that linked tables don't always allow the query to be
fully processed on the SQL Server side, so passthrough queries can be
a lot faster. In both cases the data was in SQL Server running on my
same notebook, not in Jet.
Armen Stein
Microsoft Access MVP
www.JStreetTech.com
D
David W. Fenton
Not always. It will often perform as well as a networked Jet back
end, but it isn't a huge increase in all cases. Again, it depends on
what you're doing and how much data has to be pulled across the wire
by Jet. If your Jet app is well-designed, it will already be pretty
efficient, and you won't see as many cases where there's an
improvement with SQL Server.
A
Anthos
Sorry to have misquoted you ther Armen 
BTW, I enjoyed the weekend completely, thanks for the presentations.
BTW, I enjoyed the weekend completely, thanks for the presentations.
A
Armen Stein
Sorry to have misquoted you ther Armen
BTW, I enjoyed the weekend completely, thanks for the presentations.
No problem Anthony, I enjoyed meeting you!
Armen Stein
Microsoft Access MVP
www.JStreetTech.com
T
Tony Toews [MVP]
mttmwsn said:
Now this doesn't make sense. You can route any TCP/IP traffic you
want over a VPN. I, for example, route all my incoming and outgoing
email, ports 110 and 25, via a VPN to my email server. There's no
reason why you couldn't route the SQL Server traffic on port 1433 over
a VPN. In fact that's recommended by many folks so as to no leave the
SQL Server open to the Internet.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/