Might be outgrowing Access but daunted by SQL Server

[Larry Linson]

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

 

[a a r o n _ k e m p f]

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

 

[a a r o n _ k e m p f]

Windows Term Services licensing is what, 20 grand?

SQL Server is free for all practical purposes

 

[a a r o n _ k e m p f]

WTS sucks a big fat one

and no, moving to SQL isn't expensive.. Everyone's been telling you to
move to SQL for the past decade.

It's your own fault you didnt' listen.

 

[a a r o n _ k e m p f]

yes, I use Linked Servers literally 100 times a day, every day,
without fail

I wonder if
the linked server solution would enable it (so that you'd be running
SQL Server on the remote laptop only in order to do the
synchronization of the MDB linked "server").

 

[a a r o n _ k e m p f]

David;

I'm a certified MCITP: DBA SQL 2005, and I'm getting my SQL 2008
certificate on 4/8/2010.

Thanks anyways, asshole

Just because you're an Access crybaby, doesn't mean that I don't know
SQL Server 10x better than you know Access.

 

[a a r o n _ k e m p f]

Larry;

I call hogwash on your bullshit.

I've ran Access linked tables on WAN, It runs like utter crap dude.

Compare that to ADP, dude

Copy 10 records.

Join 10 records against 10 wan records.. it'll probably take you about
4 hours, because linked tables are just a bunch of maintenance BS that
don't need to be done.

keep everything in one place- on a database server where it belongs-

Thanks

-Aaron

 

[a a r o n _ k e m p f]

Sharepoint is great.
but MS mis-managed it, removing key functionality from WSS 3.0
(ability to link to Excel spreadsheets)

and I'll never ever forgive them for that.

It's a cute product.

But it doesn't begin to address the needs to share reports, etc.. so
it's pretty damn pointless.

Maybe 10 years from now, when MS buys the remnants of AOL, then maybe
they'll understand how to make simple powerful web tools.

until that day-- sharepoint is just like jet - 10 layers of bullshit
between me and sql server- that aren't necessary or beneficial.

Thanks

-Aaron

 

[David W. Fenton]

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).

The scenario you outline will not work in more than 1 out of 1000
cases. It is just wrong.

It's also much harder to update the application part of an unsplit
database, so it must be a pretty trivial app if that's working for
you.

There is really nothing hard about managing a split app and it is
much, much safer and more reliable. I've never once put an unsplit
app into production use with multiple users, and that includes the
very first one I ever did, back in 1996. I don't know exactly where
I got the information that I needed to split it, but that was the
way I designed the whole thing. This was back in the days of Access
2, Windows for Workgroups 3.1 and 10BaseT networking. I was blown
away by Access's ability to cache data (particular for a stupidly
designed combo box that had hundreds of records -- first time you
used it was slow, after that, totally fast).

It just doesn't seem like an issue to me.

 

[David W. Fenton]

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.

So far as I can recall, they are not >0.

 

[Jon22]

Thank you all very much for your advice, especially those who have taken the
time to follow this issue right through with me and who have outlined their
advice so comprehensively.

At this stage, for the sake of a couple of hours work, I think I'd be silly
not to at least do a bit of testing of the functionality of the database as
it is now (designed entirely in Access) with some of the tables linked to an
SQL Server database (all going well I'll then investigate dsn / dsn-less /
VPN connections etc), with the view to look at Terminal Services / RDP
options if it becomes apparent that too much rewriting is needed.

My thought process being, I can install SQL Server on my own workstation for
free (Express with SQL Server Management Studio), but to do the TS option
properly, we'd need to purchase a dedicated server with (unfamiliar) server
software plus CALs.

My business partner has given me a lot of time and breathing room to get our
internal data and process management all consolidated (he's one of the ones
who needs regular overseas access to the database) and I'd like to be able
say to him "OK, all done. Just do this, this and this and you'll be in. And
it cost us virtually nothing more than my time."

We're nearly 7 years old and so far, I have managed to keep the IT entirely
under my control. I have concerns about outsourcing IT as I feel it's a path
that, once started down, would be very difficult to come back from.

I will ask any new questions I might have relating to this matter in a new
post.

Cheers,
Jon.

 

[a a r o n . k e m p f]

David;

Go play with your deprecated baby sized jet databases, kid

-Aaron

 

[Armen Stein]

An
Autonumber should never be exposed to the users,

Hi David,

I agree with this statement if you add the word "almost" before
"never".

We do have apps where we expose the AutoNumber/Identity key to the
user as a read-only "reference" number. They know that there may be
gaps, and that it is merely a unique identifier to be sure that two
people are looking at the correct record. It's especially handy in
scenarios where there are many similar-looking transactions, for
example.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[David W. Fenton]

My thought process being, I can install SQL Server on my own
workstation for free (Express with SQL Server Management Studio),

That's not going to tell you anything about what performance will be
like over the Internet.

but to do the TS option
properly, we'd need to purchase a dedicated server with
(unfamiliar) server software plus CALs.

Do you have a Windows server? If so, you can test WTS with one of
the administrative logons.

 

[Larry Linson]

Go play with your deprecated baby sized jet databases, kid

Jet was never officially deprecated, but it didn't get a lot of attention
for a while. Then the product team gave it some attention, and improved it
along with creating a descendant (the ACE database engine) in Access 2007.
At about this time, they ceased recommending ADP as the preferred Access
front-end for SQL Server, and now recommend MDB with ODBC for that purpose.

Some people just can't seem to get the story right... either they are
ignorant or they choose to disseminate false information.

Larry Linson
Microsoft Office Access MVP

 

[David W. Fenton]

"a a r o n . k e m p f @ g m a i l . c o m" <[email protected]>
wrote in
:

Go play with your deprecated baby sized jet databases, kid

We will, we will.

But at least we actually know something about it that is factually
true. You promote SQL Server for everything, yet, you don't seem to
know the first thing about it given how often you post factually
challenged information.

(I know you're going to ask for examples, and I'll just say read the
followups to any post you make about SQL Server and you'll have your
answer)

 

[David W. Fenton]

Jet was never officially deprecated,

This is true and false. Many people point to some MSDN articles that
have the roadmap for MS's database technologies and it deprecates
Jet. But they fail to notice that the article was written to guide
developers transitioning from MS's old development tools (from the
VB6 era) to the .NET family of development tools. It's obvious that
one really oughtn't use Jet/ACE for .NET development (though perhaps
the 64-bit ACE will make that no longer a vulnerability), and that's
all the articles "deprecating" Jet were about. They were deprecating
Jet for a particular purpose, not for all uses.

The proof is in the pudding of course, with A2007 having shown that
MS has no intention whatsoever to deprecate or replace Jet at all.
Even if they are hoping that Sharepoint will take over all small
business usages, they still need Jet/ACE as local database engine
for managing disconnected data. But having it also brings with it
Jet's historical ability to work with the widest variety of data
sources of any database tool out there. And do it well, with good
default performance and a host of tools to make it easy.

but it didn't get a lot of attention
for a while. Then the product team gave it some attention, and
improved it along with creating a descendant (the ACE database
engine) in Access 2007. At about this time, they ceased
recommending ADP as the preferred Access front-end for SQL Server,
and now recommend MDB with ODBC for that purpose.

It will be interesting to see what comes of the current efforts to
work with SQL Server developers to make the version of Access after
2010 more versatile and SQL Server-friendly.

Some people just can't seem to get the story right... either they
are ignorant or they choose to disseminate false information.

I can't quite understand Aaron's actions as anything but those of a
person who is at some level mentally disturbed. But I am not a
doctor, so I could be wrong on that.

 

[De Jager]

Well thats troubling. 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. The basic
breakdown of our code system is: Single letter representing the type of
entry
(eg "Q" for a quote) followed by the curent year in "yy" format (eg "10"
for
current entries) then a dash ("-") then a three digit number representing
the
individual record for that entry type and year. So for example, "J09-154"
would be the identifying value for the 154th Job that we did in the year
2009. This record would be located in my table named "Jobs" and the value
of
the [JobNumber] field in this record would be "J09-154".

So on my form named "JobEntry", the expression in the 'Default' property
of
my [JobNumber] field for new records would look like this = "J" &
Format(Date(),"yy") & "-" &
Format((Mid(DMax("[JobNumber]","[Jobs]"),5,3)+1),"000").

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?

Er, that's a solved problem. It has to be, since replication is so
widespread.

It would require changing your database to account for it, but it's
completely solvable.

It will be an issue not matter what solution you choose if you're
providing disconnected editing of the data.

 

[a a r o n . k e m p f]

uh, the extra overhead from maintaining, developing and administering
linked tables is just flat out unnecessary work

Access Data Projects allow you to keep all your information, queries
and logic - in one place - where it belongs - on a database server
(SQL Server 2005 is most likely candidate these days)



re:
There is really nothing hard about managing a split app and it is
much, much safer and more reliable.

 

[a a r o n . k e m p f]

Larry;

The only reccomendation I've ever seen from MS says something about
'reccomending ADP for performance reasons.. ESPECIALLY FOR REPORTING'

So I don't have any clue WTF you're talking about, jackass!

-Aaron