Thanks, Lyle, for a very thorough explanation. Most of my work has been
relatively light on calculation and heavy on entry, edit, keeping, and
retrieving the data -- what I call "bookkeeping", though it's not classic
bookkeeping chores, just "keeping books on data".
Larry
Lyle, do you find those operations useful in the "normal business
databases"
for which Access is so well-suited,
I confess that the majority of "my" applications are not normal
business databases; I would describe them as "algorithmic" databases,
where calculations are primary and the collection and entry of data
are secondary. I started in this business when I was member of a team
negotiating the first collective agreement for a educational system.
The other side said, "We won't implement that because its cost CANNOT
be calculated." I said, "I can calculate its cost." (The conversation
may have been a little longer and a little harsher.) That night I did,
using Lotus and its macro language. A few days later I talked this
over with a friend from Stelco and he gave me pirated copies of DBase
III. I was hooked. (But I bought DBase III right away as the pirating
part never appealed to me.)
or just when using an ADP as a front-end
to MS SQL Server? I don't seem to recall, in using Access since its
inception, a _need_ to "get a table/query as a string".
I find GetString and the Split function especially useful in quickly
translating a recordset into a multi-dimensional array. Arrays are
very much faster than recordsets for recursive, intense calculations.
Another use is mentioned below.
Most of my clients,
even those with MS SQL Server, did not want any vital applications created
that would limit their ability to change the server back end if their
company decided to do so. Thus, ADO's were unacceptable to those clients.
I don't understand this. After we set the Connection once for each
user (with code or the Connection Dialog) this code grabs each user's
connection from the registry and connects to the db. Thus, each user
can have a different backend, and, of course, there would be no
limiting changing the server backend.
Public Function BaseConnect()
Dim ConnectionString$
Dim ProjectName$
ProjectName = Split(CurrentProject.Name, ".")(0)
ConnectionString$ = _
GetSetting(ProjectName, "Startup", "BaseConnectionString")
With CurrentProject
If .IsConnected Then
If .BaseConnectionString <> ConnectionString Then _
SaveSetting ProjectName, _
"Startup",
"BaseConnectionString", .BaseConnectionString
Else
If Len(ConnectionString) > 0 Then _
.OpenConnection ConnectionString
End If
End With
End Function
(This code is great for application delivery. On my development
machine it connects to my server, and on the program machines it
connects to their servers, with no fuss.)
Refresh my memory: didn't you write that you, for good reason, stopped
using
ADPs?
Yes, I did. The reason was security and is directly related to the
subject of this thread. If I give you permissions on the server, but
control what you can do through my ADP application, there is a big
security problem, IMO. If you create a new ADP, the Connection dialog
will show, maybe even suggest is not too strong a word, the server and
database for which you have permissions. When you click Connect you
now can use those permissions without any control from my application.
All the tables and other objects show up in the DB window. OUCH! I
think this model is fundamentally flawed. But, I felt better when Rick
Brandt suggested the same pitfall existed with ODBC connections. It
seems that it does. But it may be much more unlikely that an MDB-ODBC
user will create a new ADP than an ADP-OLEDB user.
The solution is application roles as per this thread. The application
connection has permissions, not the user who has only login
permissions for the server, but nothing else. When he creates a new
ADP he can see or use nothing. His db window is blank and he can do
nothing, not even with code. But it's "application connection" that is
the killer here. It's the Connection that fills the role and has the
permissions, not the application as we might think. Access in general
and ADPs particularly are entirely undisciplined about connections. We
may think that there is just CurrentProject.Connection or
CodeProject.Connection or CurrentProject.AccessConnection etc existing
and we should because that's all we see. But I discovered, much to my
chagrin, that Access opens new connections for many things, including
ListBoxes and ComboBoxes. Once when demo-ing an application for ten
users, the dba who was monitoring the server used some quite
demonstrative language about "more than 100 connections" showing on
his screen, Yes, 100 connections for ten users. Each of those
connections must have application roles enabled properly and enabling
them is a pain. Even that would be OK if these connections, and when
and how they are created were documented. TTBOMK they are not
documented (maybe because they are unknown) and their creation seems
to be erratic. That is, a connection that was not required for a
listbox on Tuesday, may be required on Wednesday. It's difficult to
program around weekdays. I did complete a major application as ADP
with approles. But the hours piled up, maybe twice or even three times
what would have been required normally. For list and combo boxes I
used strings, created with ... guess what ... the GetString function!
Right now I am working on an ADP. Why? because there are only a few
users and the application gives them total control of all their data,
so a new ADP giving them total control of all their data is redundant,
and I hope, harmless. And the users are very sophisticated and know
the responsibility for any bad behavior on their part is theirs alone.
I'll fix it, if asked, but they will pay.
Addendum: ADPs provide a very simple way of interacting with Internet
Enabled SQL Servers. I could send you a less than one meg ADP, and you
and I could both work on an SQL DB in South Africa, anywhere in the
world, WITH all the protections of locking etc, and the beautiful
Access reports available to us. All you need is Access and an Internet
connection. This is amazingly powerful and universally ignored. Oh
well, it's also lucrative, especially when I'm the only one doing it.
Security? It requires a USERID and a Password. They can be encrypted.
The server has super Security software and hardware surrounding it.
Can it be broken? Probably. Will it be? I have a database on my
(rented) server. The challenge for two years has been, break in, and
in the table called Dog, create a new record and enter your name
there. So far, the table is bare.
And so, the poor dog has none.
. Lyle's opinions regarding Access and ADP's are based on