Anybody want an Access Server?

[uphid]

I've been tossing around an idea here in the shop for a while, and I am
curious if anyone else out there would be interested.

I am thinking of creating a server implementation for Access MDBs.
It's a fairly straight-forward idea. The server would be a program
that would listen for SQL commands, such as SELECTs or INSERTs to be
sent to it (over TCP/IP). In turn, it would run the SQL against the
local database and return either records (for SELECT) or execution
information (for INSERT, UPDATE, DELETE).

I think that I could also implement a "virtual" recordset object on the
client end, which would retreive, allow you to edit, translate to
UPDATE SQL, and fire it back to the server. This would let you work
with the object fairly easily and transparently.

I haven't built it yet, but I am thinking about it. Would anybody else
get any use out of it? I know that I am somewhat reinventing a "real"
database, but in many cases, Access could extend it's usefulness with a
bit more connectivity (and hopefully a bit more corruption-resistance).
Since I'm one of those die-hard Access guys, I know that I would like
it.

Any thoughts?

-Ben

[Alan Webb]

Uphid,
You want to write a what? Isn't that what SQL Server & MSDE is? Along with
operating systems, a good relational database engine is one of the tougher
bits of coding to take on. I am firmly in the "try very hard to buy before
you decide to build" camp. I'd be interested just to see if what you write
has enough bang for the buck to persuade me to use your server instead of
Microsoft's VB.Net & MSDE.

--
Alan Webb
kn*******@SPAMh otmail.com
"It's not IT, it's IS
<up***@hotmail. com> wrote in message
news:11******** *************@g 14g2000cwa.goog legroups.com...

I've been tossing around an idea here in the shop for a while, and I am
curious if anyone else out there would be interested.

I am thinking of creating a server implementation for Access MDBs.
It's a fairly straight-forward idea. The server would be a program
that would listen for SQL commands, such as SELECTs or INSERTs to be
sent to it (over TCP/IP). In turn, it would run the SQL against the
local database and return either records (for SELECT) or execution
information (for INSERT, UPDATE, DELETE).

I think that I could also implement a "virtual" recordset object on the
client end, which would retreive, allow you to edit, translate to
UPDATE SQL, and fire it back to the server. This would let you work
with the object fairly easily and transparently.

I haven't built it yet, but I am thinking about it. Would anybody else
get any use out of it? I know that I am somewhat reinventing a "real"
database, but in many cases, Access could extend it's usefulness with a
bit more connectivity (and hopefully a bit more corruption-resistance).
Since I'm one of those die-hard Access guys, I know that I would like
it.

Any thoughts?

-Ben

[Steve Jorgensen]

Well, I do understand the temptation, and I had that thought myself once, but
the only reason I can really see for doing it is as a learning exercise.

Remember that JET and MS SQL Server already share a lot of logic, but MS SQL
Server also has the light MDSE version, and MSDE can do some modest
multi-threading, while JET cannot, making it much more suitable as a server
for multiple clients.

On 29 Mar 2005 13:56:37 -0800, up***@hotmail.c om wrote:

I've been tossing around an idea here in the shop for a while, and I am
curious if anyone else out there would be interested.

I am thinking of creating a server implementation for Access MDBs.
It's a fairly straight-forward idea. The server would be a program
that would listen for SQL commands, such as SELECTs or INSERTs to be
sent to it (over TCP/IP). In turn, it would run the SQL against the
local database and return either records (for SELECT) or execution
information (for INSERT, UPDATE, DELETE).

I think that I could also implement a "virtual" recordset object on the
client end, which would retreive, allow you to edit, translate to
UPDATE SQL, and fire it back to the server. This would let you work
with the object fairly easily and transparently.

I haven't built it yet, but I am thinking about it. Would anybody else
get any use out of it? I know that I am somewhat reinventing a "real"
database, but in many cases, Access could extend it's usefulness with a
bit more connectivity (and hopefully a bit more corruption-resistance).
Since I'm one of those die-hard Access guys, I know that I would like
it.

Any thoughts?

-Ben

[rkc]

up***@hotmail.c om wrote:

I've been tossing around an idea here in the shop for a while, and I am
curious if anyone else out there would be interested.

I am thinking of creating a server implementation for Access MDBs.
It's a fairly straight-forward idea. The server would be a program
that would listen for SQL commands, such as SELECTs or INSERTs to be
sent to it (over TCP/IP). In turn, it would run the SQL against the
local database and return either records (for SELECT) or execution
information (for INSERT, UPDATE, DELETE).

I think that I could also implement a "virtual" recordset object on the
client end, which would retreive, allow you to edit, translate to
UPDATE SQL, and fire it back to the server. This would let you work
with the object fairly easily and transparently.

I haven't built it yet, but I am thinking about it. Would anybody else
get any use out of it? I know that I am somewhat reinventing a "real"
database, but in many cases, Access could extend it's usefulness with a
bit more connectivity (and hopefully a bit more corruption-resistance).
Since I'm one of those die-hard Access guys, I know that I would like
it.

Any thoughts?

Someone else who used to post to this ng (wolfsomethingo rother) told of
building a middle ware tcp/ip link to a Jet database using VB6. Didn't
raise to much excitement back then.

[uphid]

I may try making a quick "proof of concept" version, just to see what
happens. Although it has inherent limitations, it still may have uses.

For example, I am really not a fan of using MSDE in shrinkwrapped
applications. I *like* having a simple system. It works well with
customers who really don't want to understand how databases work. It's
simple. It's reliable (enough) in most cases. On simple lan
applications for a few users, the throughput isn't usually an issue.

I had another thought.... I could keep something similar to SQL
Server's transaction log. Picture that... Access with a replayable
transaction log.

I don't know that I would have the time to refine it into a polished
app; but it would be a great starting point for people who need it. I
think it would fill the void for people who need database connectivity
over a shaky 802.11 network. That has always been a problem for
Access; and in theory, this would need even less bandwidth than
standard SQL traffic. I could possibly even write it to deal with
constantly dropping connections.

The biggest problem I get these days are customers who want to share
the "backend" mdb over file-sharing on a shaky 802.11 that they set up
themselves. This may solve that nicely, don't you think?

While I'm on a tangent, it might even be fun to implement deflate
compression on the traffic itself, just to reduce the load further.
This might be especially helpful if someone asks for a large recordset,
like a SELECT *.

-Ben

[Alan Webb]

Ben,
Go get drunk instead. You'll feel better for a while and it will do less
damage to your life than a project like this. The efford needed to make a
complex task like relational databases simple is considerable. Access is in
its tenth or eleventh version and it still has its critics, you among them,
aparantly. PostGRE SQL and MySQL have been around for at least a couple
decades and I'd guess they have their own set of bugs and detractors. But
.. . . sure, write a "proof of concept version". This should be
entertaining. Just don't ask me to pay for it.

--
Alan Webb
kn*******@SPAMh otmail.com
"It's not IT, it's IS

<up***@hotmail. com> wrote in message
news:11******** **************@ g14g2000cwa.goo glegroups.com.. .

I may try making a quick "proof of concept" version, just to see what
happens. Although it has inherent limitations, it still may have uses.

[uphid]

I think you may have missed exactly what I was planning...

I don't plan on writing a database system at all. I plan on writing a
simple TCP/IP listener that will throw queries against an MDB through
DAO. I certainly don't have the time or inclination to write a whole
DBMS. All that I really want to do is create a "loose" connectivity
structure for Access, which I sure wish that it could do.

Anyway, other than the things I do for employer, I release everything
under a Creative Commons license. I try to share. A lot of people
certainly shared with me when I was learning.

-Ben

[Mike Preston]

On 30 Mar 2005 09:25:16 -0800, up***@hotmail.c om wrote:

I think you may have missed exactly what I was planning...

I don't plan on writing a database system at all. I plan on writing a
simple TCP/IP listener that will throw queries against an MDB through
DAO. I certainly don't have the time or inclination to write a whole
DBMS. All that I really want to do is create a "loose" connectivity
structure for Access, which I sure wish that it could do.

Anyway, other than the things I do for employer, I release everything
under a Creative Commons license. I try to share. A lot of people
certainly shared with me when I was learning.

Don't let anybody get you down. It sounds very interesting and who
knows where it might lead?

mike

[David W. Fenton]

mb******@pacbel l.net.invalid (Mike Preston) wrote in
news:42******** ********@news.I NDIVIDUAL.NET:

On 30 Mar 2005 09:25:16 -0800, up***@hotmail.c om wrote:

I think you may have missed exactly what I was planning...

I don't plan on writing a database system at all. I plan on
writing a simple TCP/IP listener that will throw queries against
an MDB through DAO. I certainly don't have the time or
inclination to write a whole DBMS. All that I really want to do
is create a "loose" connectivity structure for Access, which I
sure wish that it could do.

Anyway, other than the things I do for employer, I release
everything under a Creative Commons license. I try to share. A
lot of people certainly shared with me when I was learning.

Don't let anybody get you down. It sounds very interesting and
who knows where it might lead?

I second that.

It's also not the first time someone has talked about this. The last
time it was discussed in this forum, the preferred method was via
COM objects on the server that you'd talk from your client
applications, and they'd talk to a process that was the only one
that interfaced with your Jet DB.

This would be a way to provide transaction logging for a Jet DB, by
the way.

The hard part is that an Access front end wouldn't really be able to
talk to these COM objects, except as unbound data sources (i.e.,
your forms would all have to be unbound).

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[Larry Linson]

"David W. Fenton" <dX********@bwa y.net.invalid> wrote

It's also not the first time someone
has talked about this. The last
time it was discussed in this forum, the
preferred method was via COM objects
on the server that you'd talk from your
client applications, and they'd talk to a
process that was the only one
that interfaced with your Jet DB.

As a matter of fact, Drew Wutka, who has posted here as "cryingwolf ",
implemented something similar with classic VB and Jet, using WinSock for
communication between the VB app on the users' machines and the VB app on
the server. But it was NOT 'generic'... it was very application specific. On
the other hand, the 255-user limit was not an issue, because all retrieval
and update was done by the one application on the server.

"Very application specific" is a _lot_ easier to design and implement than
doing the same to put a "general-purpose" front end on Jet. Creating a
"semi-server" with Access and Jet could be interesting.

But, certainly, none of my clients would have wanted to pay for my doing so
as part of my contract work. For those, it would have been far more
cost-effective to obtain a server DB that didn't have to be built on-site by
expensive (ha!) contract labor.

In this case, I am in agreement with Alan -- MSDE would work nicely if you
don't have a great many users; if you have a larger user audience, full MS
SQL Server, or one of the open-source DBs he mentions, would work.

Larry Linson
Microsoft Access MVP