Might be outgrowing Access but daunted by SQL Server

"Cargo Culture" Banana (Republic) wrote:
david wrote:
Banana,

Access/Jet is built on top of the Windows Database primatives.

But what exactly is a 'Windows Database primitives'? I've googled this
phrase and didn't find anything useful. I'd like to know what you mean
by that phrase.

You clearly and carefully presented your case that the DLL's used for
data access in Access are file based. I agree. david, in mentioning
native windows database primatives, seems to suggest that because the
files themselves are served (via SMB), there is some similarity to how
the information contained in a SQL Server file is served. It's an
interesting concept, to be sure, but if I haven't misunderstood
david's point, it doesn't really serve :-) to limn the difference
between the two data access methods. The DLL's used by Access are
still file-based, as we understand the term. The SQL Server service
is still Client/Server, as we understand the term.

James A. Fortune

"Tony Toews [MVP]" wrote in
:

http://weblogs.asp.net/kencox/archiv...e-282-06-for-w
indows-azure-usage-charges-on-an-msdn-subscription.aspx and read
the comments.

No, thanks. I'll wait for the hype and then for somebody to cut
through it and explain what it's all about. If nobody can do that
clearly, I'll ignore it, the same way I've ignored any number of
other buzzword-driven "innovations."

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

=?Utf-8?B?Sm9uMjI=?= wrote in
:

Now, if I have two users who were logged on to a TS RemoteApp
session for instance (or whatever other Terminal Services method
we'd use) both with this Access Database open and they both
started a new Quote at nearly the same time (say within 2 minutes
of eachother), given that the Quote form might take 5 minutes to
fill out and print to pdf, what you're saying is that my two users
could potentially create a quote each and send their quotes off to
their prospective customers and had both used the same Quote
Number that was automatically generated for them when they went to
a new record on the Quotes form?

No, not at all. Using a Terminal Server is a great way to *avoid*
the problem. What I said was that if users are disconnected, you'd
have to use some other method of creating a unique sequence, either
pre-allocating blocks of numbers to particular workstations/people,
or incorporating the source workstation/person into the sequence
(either within a single field, or, better, in a pair of fields with
a unique index).

Any multi-user app that generates its own sequence numbers should
already be avoiding the problem you describe by choosing the next
number in the sequence and saving it immediately so that the next
user doesn't collide. As long as all users are adding records to a
shared back end, your only concern is locking the process while
you're generating the sequence number. The usual way to do this is
to have a single-record table where you update it to the last-used
sequence number. This is more reliable and more efficient than
locking the table you're storing the sequence number in.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

=?Utf-8?B?Sm9uMjI=?= wrote in
:

A number of the areas of my database (quotes, jobs and
designs to start with) work on an identification system that gives
each record (that is to say each Quote or each Job) a unique
"Quote Number" or "Job Number" etc. Now before you jump down my
throat, I know that this is a basic principal of Access databases
and that it already has a built in unique identifying system of
it's own (Autonumber). But for various reasons, we have elected to
use our own 'code' to uniquely identify our records.

Dunno why you're getting defensive here -- any sequence that is
being exposed to the users *should* be generated as you're doing. An
Autonumber should never be exposed to the users, regardless of
what's going on behind the scenes, so from what I can tell, you're
certainly doing things correctly already.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

Banana Banana@Republic wrote in news:4B8BBFA1.4090001@Republic:

While replication may be implemented differently depending on what
you use for replicating (Jet Replication? SQL Server Replication?
Sharepoint?* Other?), you may be required to add fields necessary
for the replication to track changes made to this record. But that
doesn't mean you can't continue to use the code as you have right
now.

SQL Server allows the allocation of identify value ranges to
particular instances of the replicated database. This allows you to
control what numbers get assigned at the database engine level,
which is quite useful. On the other hand, it's an identity key, and
perhaps subject to the same issues as Autonumbers (I'm not sure how
SQL Server's identity data type works, to be honest).

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

=?Utf-8?B?Sm9uMjI=?= wrote in
:

(I do know about Primary Keys too. Forgot to mention that in that
last post. However I don't know how they function when the
database is in a shared state/environment.)

I assumed you've already addressed the shared environment, and it's
the disconnected environment that would be problematic.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

"David W. Fenton" wrote:

http://weblogs.asp.net/kencox/archiv...e-282-06-for-w
indows-azure-usage-charges-on-an-msdn-subscription.aspx and read
the comments.

No, thanks. I'll wait for the hype and then for somebody to cut
through it and explain what it's all about. If nobody can do that
clearly, I'll ignore it, the same way I've ignored any number of
other buzzword-driven "innovations."

Actually that link and the comments cut through the hype and warn you about the
perils of how expensive it oculd get just for test data.

Tony
--
Tony Toews, Microsoft Access MVP
Tony's Main MS Access pages - http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
For a convenient utility to keep your users FEs and other files
updated see http://www.autofeupdater.com/
Granite Fleet Manager http://www.granitefleet.com/

Jon -
I am no IT guru either but am in a very similar situation to you and have a
completely free / no learning curve suggestion for you.
It will probably have all the experts throwing their hands up in horror as
it is simplicity in itself and no doubt all kinds of holes can be picked in
it. However, the bottom line is that it works for us, and works very very
well indeed. Let me say sincerely that I have the utmost respect for all the
amazing depth and breadth of advice offered to you in this post and I have
benefited tremendously from replies given to my many queries over the last
few years, but sometimes one simply wants - well, simplicity. So, back to
basics:
We have a total of 6 users on an Access 2003 mdb that I have gradually put
together over the last 4 years. It runs on a fairly basic HP box (server)
that also runs Exchange and stores everybody's other workfiles. The mdb now
handles every aspect of the company's business and the owner is thrilled with
the control and instant information that it gives him.
It is NOT split - I didn't do that at the outset and despite thinking about
it a number of times, have never got round to it. Despite this we never
suffer from conflicts or record locking. If it ain't bust, don't fix it.
We have 3 users who regularly work remotely, from home, and 2 who travel
frequently around the world and access the system every day whilst away. We
use nothing more than LogMeIn, which in its basic form is totally free, and
even for the Pro version is a mere handful of dollars. The response time is
perfectly acceptable and the users love it.
The whole setup works wonderfully for us and I often wonder why one might
need anything more sophisticated (= expensive and time-consuming to learn and
maintain).
I rest my case.
CW


"Jon22" wrote:

I'm close to completing the consolidation of various small Access databases
and a couple of Excel spreadsheets that my little company uses (5 staff) into
an all encompasing Access database and I was planning on splitting the
database when I was finished to allow simaltaneous use of it by staff on our
small office network. However it is becoming more and more obvious to me that
I am going to need to give staff access to this new database from remote
locations (outside of our office network). Two staff members are regularly
overseas, one is mainly on the road with her laptop etc etc. I've been
playing around with trying to link tables from remote locations (ftp path of
the database on our Network Storage Device at the office) and needless to say
- that don't work. So I've spent the last 10 hours downloading, installing
and trying to get my head around Microsoft SQL Server 2008 Express and how it
might help me out. It all started when I came across a very encouraging
article on keeping your Access database as your front end application and
linking to SQL Server database tables. I'm finding it MUCH more complicated
than I had hoped.

Can anyone suggest a simpler way of achieving remote access to my database?
I have at my disposal a Network Storage Device which has remote access
capabilities (this is where the database is currently stored), our website
which it could be stored on, or I'd be happy to use my quite powerful
workstation at the office as some kind of server. You may have guessed by now
that I'm no IT guru by any stretch so please bare this in mind with any
suggestions.

"a a r o n . k e m p f @ g m a i l . c o m" wrote

I'm having trouble finding info on linking to tables
on a SQL Server database from a computer outside
the LAN.

dude you can't run Linked Tables outside the LAN.

I've been running ODBC-linked tables for servers on WANs since the days of
Access 2.0. That includes, but isn't limited to, Microsoft SQL Server -- it
works with any ODBC-compliant server.

These people are just outright lying to you.

This claim is not true. Mr. Kempf compounds his lack of knowledge on the
subject of Access with frequent false statements that he knows to be false.

It works best to move to Access Data Projects.

ADPs are no longer recommended by the Access product group. Though ADPs are
still supported and have not been officially "deprecated", I'd certainly
advise you to seriously consider before launching a new product using one.
ADPs only support MS SQL Server,
so if you may ever have to support a different server DB, they are not
appropriate.

You can safely disregard Mr. Kempf's comments, even if no one has refuted
the specifics. His useful contributions to this newsgroup, over all the time
he's been posting here, have been minimal.

Larry Linson
Microsoft Office Access MVP

uh, are you kidding me?

If OP wanted to use the application in disconnected state, I would
really not want to entertain the idea of running SQL Server Express
on
every client machine. That would be too much administration and
training.

I know of lots and lots and lots of companies that put SQL Server
Express on laptops, desktops, etc

It's drop dead simple to replicate one database from a central server
(probably SQL Standard) to 10 or 100 desktops / laptops.

-Aaron





On Feb 27, 7:19*am, Banana Banana@Republic wrote:
David W. Fenton wrote:
But if you're contemplating allowing disconnected use, you'll need
the SQL Server running on the laptops and you'll probably need to
implement SQL Server replication in order to keep the laptop
databases synchronized with the central one. Replication is not a
minor issue, and if you can avoid it, you really should.

If OP wanted to use the application in disconnected state, I would
really not want to entertain the idea of running SQL Server Express on
every client machine. That would be too much administration and training.

I'd daresay it'd be easier to just use Access local tables and write
scripts to synchronize when the laptop comes home. I also understand
that SQL Server supports replication with linked server, but I'm not
sure how well SQL Server handles synchronizing with non-SQL Server
sources or whether it'll be practical in this use, though.

I agree that replication is difficult no matter how you set it up.

In general, backing up the live SQL Server files is not going to
give you a reliable backup. It's just like backing up an MDB/ACCDB
that is open by a user -- you may or may not get a valid file out of
it (it's probably even less likely with the SQL Server files, I
would think). SQL Server has a backup agent to take care of backups
for you, but one of the disadvantages of SQL Server Express 2008 is
that the backup agent is not included! Some backup software is able
to talk directly to the SQL Server and get a backup file, but if
that is dependent on the agent, it won't work with SQL Server
Express.

That's why I had qualified my statement as "even then that is not
strictly necessary." Perhaps I should have had used "is not actually
necessary", because in most practical backup usage, we'd be dealing with
.bak files, not .mdf or .ldf files.