Access front-end, PostgreSQL back-end?

[Peter]

Has anyone tried this? Any problems? Any advantages over an Access
front-end with SQL Server back-end?

Just curious. I'm checking out some different back-end alternatives to
Access .mdb / .accdb.

Thanks,

Peter De Baets
http://www.peterssoftware.com

 

[aaron.kempf]

Access / SQL works much better.

ADP -- there is nothing that can compete with this combo.
Honestly-- why postgres?

Use Postgres _THROUGH_ SQL Server if you must.
You can link to Postgres via linked servers.. and then it would be
quite interesting.

Or again-- just use ADP and SQL Server.

SQL Server is the same price as postgres; and it's obviously faster

-Aaron

 

[Arvin Meyer [MVP]]

Access works fine with most back-ends including Postgre. The only advantage,
if you can call it that, is that Postgre works on a variety of operating
systems. That said, I believe that SQL-Server is the premier server based
DBMS and I would spec it over any other on a Windows server.

 

[David W. Fenton]

Access works fine with most back-ends including Postgre. The only
advantage, if you can call it that, is that Postgre works on a
variety of operating systems. That said, I believe that SQL-Server
is the premier server based DBMS and I would spec it over any
other on a Windows server.

It's very expensive in terms of licensing costs.

 

[pddxxx]

Thanks to all for your responses.

I can't determine from looking at the Microsoft site where the
licensing costs would kick in, assuming you're starting out with SQL
Server Express, the free version. Is it when you need a second
processor for your database server?

At what point would my fictional SQL Server Express client need to
plop down some money for the full version of SQL Server?

 

[George]

That is a subjective question. A generic answer would be along the lines of
"when your organization reaches the point it needs functionality available
in the full version, but not in the Express version." That could mean
different things in different organizations, but here is a comparison chart
that might offer some clues.

http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx

Again, you'll need to evaulate each of those comparisons in light of what
they might mean in your organization, or to your client.

For example, you mention a second processor. Note that the Express version
supports a single CPU, but the more relevant question would be "when do you
NEED a second processor?" Another factor is the 4 GB size limit on the
Express version. How long will it take for you to bump into that limitation.

My guess is, though, that by the time you or your client reach the point
where you're bumping into such limitations, you'll also have enough
experience to make a more informed decision, so not to worry too much about
it at this point.

 

[aaron.kempf]

Yah; I'm not so sure about that one David-- I'm begining to sense that
you're quite skeptical of SQL Server.
Do you work in a mainframe shop or something?

There is nothing about _SQL_SERVER_ that is 'very expensive'.

Comparing terminal service licenses to SQL Server licenses-- seems
like a good place to start.

Anyone using MDB that reccomends term server licenses-- is obviously
not paying attention to licensing costs. Term Services licensing.. is
more expensive than SQL Server. _RIGHT_?

Sounds to me, David-- like instead of complaining about 'very
expensive software licenses' you should just worry about spending your
software licenses in the _BEST_ way.

I dare to say that _FREE_ SQL Server Express is more than enough
database for anything that you want to do.

ADP work _PERFECTLY_ over wireless, vpn, WAN, LAN.. and even the
public internet.
Yes-- you can build an ADP and email it to a friend; and it still
works.

And it's -FAST-.

Now.. how is it that SQL _COSTS_ money, David?
Compared to MDB? It's a _BARGAIN_.

Anyone that complains about the 'very expensive' costs of SQL Server
has never seen:
Analysis Services
Reporting Services.

I mean seriously here, kid-- Get out into the real world, before
trying to scare people away from SQL Server (without reason, again--
it's _FREE_).

Or .. if you're really too much of a cheapskate to know a good value
when you see one-- then skimp and use the workgroup edition. But that
grand you save is not worth the time it takes me to write this
sentence. Because Analysis Services is the best thing to hit the db
world.. since uh.. well E.F. Codd.

www.olapreport.com/market.htm - SQL Server has SLAUGHTERED the
competition for the past decade.

So-- before you run around-- talking about the so called 'very
expensive' price.
Why don't you consider that there are _FIVE_ different editions of SQL
Server.

Enterprise Edition
Standard Editon
Workgroup Editon
Express Edition
Compact Edition (Fitting with the resource constraints of today's
mobile devices, SQL Server Compact's small embedded footprint is under
2mb in size).

_WOW_.

Maybe you were looking at the Enterprise Edition-- I don't know. I
know first hand that SQL Server is cheaper than anything else on the
market. And the free solutions are _BY_FAR_ cheaper than Access
databases.

The bottom line is that SQL Server Express Edition works _PERFECTLY_
on a nice dual core.. or quad core machine.
Does Access utilize dual core??

You will not be able to challenge the resources of SQL Server Express
machine if you follow best practices such as
Select * from Sys.Dm_Db_Missing_index_details

Yes- that is correct. SQL Server complains when you are missing
indexes.

With Access; it is literally impossible to determine a set of optimal
indexes.

SQL Server has 10 times the indexing capabilities of Access.

No-- SQL Server is _NOT_ 'very expensive in terms of licensing costs'.
It is a much much much much much better product. And it is the same
price as MS Access.

I just militantly disagree with that statement.

Sorry

There is nothing about _FREE_ that is 'very expensive'.

-Aaron

 

[Arvin Meyer [MVP]]

Postgre is free, so is the SQL-Express, but the real cost, as I'm sure you
are aware of, is not in licensing. The TCO depends upon lots of factors
including developer availability and salary, cost of deployment, speed of
operation, and a host of other expenses. If you have 5000 users connecting
to a SQL-Server over an ASP web connection, the cost per user is negligible.

I've connected to Postgre databases with Access front ends and it always has
taken me longer in development time than SQL-Server. Perhaps that's because
I have much more experience with SQL-Server, so my experience may not be
universal. If I had to choose, I'd prefer MySQL to Postgre. Still,
SQL-Server has been my backend of choice for server based DBMS running on
Windows. I really like the amount of development support that I get from
Microsoft.

 

[David W. Fenton]

Postgre is free, so is the SQL-Express

But SQL Server Express is *not* SQL Server.

When does one need to upgrade from SQL Server Express to SQL Server?
I'm going to encounter this very question this fall, when I upsize a
client from a Jet back end to SQL Server (the app will be run on
Windows Terminal Server). The user population is, for now, small,
but could grow (though never to a large number like 100), and I've
suggested we start with Express and determine if and when we
eventually need the full package. The licensing costs really are
quite steep if you can't get SBS (and they can't, since SBS doesn't
support WTS beyond the 2 admin logons).

 

[aaron.kempf]

Again-- Terminal Services is just plain fucking stupid. Planning your
network around terminal services is laughable.

Move to ADP. Plan your business around SQL Server. Grow some balls.

-Aaron

 

[aaron.kempf]

and I'm just really not positive that anyone that knows what they are
doing-- could challenge a SQL Server express machine.. that had a
decent dual / quad core proc and at least.. I'd reccomend 3gb ram with
SQL Server express.

(1gb for the SQL Server, another 2gb for Windows)

Honestly- I can throw together a box at the local computer store.. for
$500.. IN MY SLEEP. That will never be challenged-- performance wise.

(assuming you use the built in indexing tools)

-Aaron