I have back-end access database that I want to put in secure place. Is odbc a
good way to connect to it? How to set it up?

-AB-

"Agus Budianto" <(E-Mail Removed)> wrote

> I have back-end access database that I want to
> put in secure place. Is odbc a good way to
> connect to it? How to set it up?

You cannot connect an Access MDB to Jet via ODBC. AFAIK, the same is true
for Access 2007 and ACCDB.

I had a colleague who "turned an Access MDB into a special-case server
database". He created a VB application to run on the server to access the
database and communicate to users via WINSOCK, then for the users, he
created a VB application for their interface that communicated to the VB
server application via WINSOCK. There were a lot of users whose updates
were low-frequency, his database was secure because no users had "access" to
the folder, they could only send updates to the VB server application. It
was non-trivial work, but it didn't require startling breakthroughs nor
climbing mountains never-before-climbed, either.

On the other hand, you can use Access as a client application to any
ODBC-compliant server DB. There's a "limited" copy of SQL Server that comes
with older versions of Access, called MSDE. There is now an SQL Server 2005
Express Edition and an even lighter-weight SQL Server 2005 Compact Edition,
both free, that can be used from Access via ODBC. And, there are at least
two or three Open-Source databases with available ODBC drivers. I've used
none of the free versions, but have, since the days of Access 2.0, worked on
Access clients to various ODBC-compliant server databases from Informix,
Sybase, and Microsoft.

Larry Linson
Microsoft Access MVP

Thanks Larry,
I am starting to download SQL Server 2005 Express Edition


"Larry Linson" wrote:

> "Agus Budianto" <(E-Mail Removed)> wrote
>
> > I have back-end access database that I want to
> > put in secure place. Is odbc a good way to
> > connect to it? How to set it up?
>
> You cannot connect an Access MDB to Jet via ODBC. AFAIK, the same is true
> for Access 2007 and ACCDB.
>
> I had a colleague who "turned an Access MDB into a special-case server
> database". He created a VB application to run on the server to access the
> database and communicate to users via WINSOCK, then for the users, he
> created a VB application for their interface that communicated to the VB
> server application via WINSOCK. There were a lot of users whose updates
> were low-frequency, his database was secure because no users had "access" to
> the folder, they could only send updates to the VB server application. It
> was non-trivial work, but it didn't require startling breakthroughs nor
> climbing mountains never-before-climbed, either.
>
> On the other hand, you can use Access as a client application to any
> ODBC-compliant server DB. There's a "limited" copy of SQL Server that comes
> with older versions of Access, called MSDE. There is now an SQL Server 2005
> Express Edition and an even lighter-weight SQL Server 2005 Compact Edition,
> both free, that can be used from Access via ODBC. And, there are at least
> two or three Open-Source databases with available ODBC drivers. I've used
> none of the free versions, but have, since the days of Access 2.0, worked on
> Access clients to various ODBC-compliant server databases from Informix,
> Sybase, and Microsoft.
>
> Larry Linson
> Microsoft Access MVP
>
>
>

Larry

Please explain to me how MSDE 2.0 is LIMITED

It's not LIMITED
Access MDB is LIMITED to a half dozen users and 25 mb of data

-Aaron






On Mar 29, 9:33 pm, "Larry Linson" <boun...@localhost.not> wrote:
> "Agus Budianto" <AgusBudia...@discussions.microsoft.com> wrote
>
> > I have back-end access database that I want to
> > put in secure place. Is odbc a good way to
> > connect to it? How to set it up?
>
> You cannot connect an Access MDB to Jet via ODBC. AFAIK, the same is true
> for Access 2007 and ACCDB.
>
> I had a colleague who "turned an Access MDB into a special-case server
> database". He created a VB application to run on the server to access the
> database and communicate to users via WINSOCK, then for the users, he
> created a VB application for their interface that communicated to the VB
> server application via WINSOCK. There were a lot of users whose updates
> were low-frequency, his database was secure because no users had "access" to
> the folder, they could only send updates to the VB server application. It
> was non-trivial work, but it didn't require startling breakthroughs nor
> climbing mountains never-before-climbed, either.
>
> On the other hand, you can use Access as a client application to any
> ODBC-compliant server DB. There's a "limited" copy of SQL Server that comes
> with older versions of Access, called MSDE. There is now an SQL Server 2005
> Express Edition and an even lighter-weight SQL Server 2005 Compact Edition,
> both free, that can be used from Access via ODBC. And, there are at least
> two or three Open-Source databases with available ODBC drivers. I've used
> none of the free versions, but have, since the days of Access 2.0, worked on
> Access clients to various ODBC-compliant server databases from Informix,
> Sybase, and Microsoft.
>
> Larry Linson
> Microsoft Access MVP

A a r o n K e m p f wrote:

>Please explain to me how MSDE 2.0 is LIMITED

Throttled to 5 processes and maximum of 2 Gb in size.

>Access MDB is LIMITED to a half dozen users and 25 mb of data

Wrong. I've happily had a database with 25 users and 300 mb of data.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm

sorry dude

a) MSDE 2.0 (SQL 2000) is not throtted to 5 processes. It has a
GOVERNOR, YES. But with MSDE it is a soft governor instead of a hard;
from what I've seen-- MSDE works like a charm for 90% of departmental
projects. SQL Server works great for larger projects. And SQL 2005
Express has NO connection limits.

b) I've had a hundred MDB and MDB-linked SQL Server databases that DO
NOT WORK FOR 25 USERS AND THEY DO NOT WORK FOR A SINGLE USER

c) Access is for babies and chipmunks; it is so 1993 to use MDB, kid







On Mar 30, 7:47 pm, "Tony Toews [MVP]" <tto...@telusplanet.net> wrote:
> A a r o n K e m p f wrote:
>
> >Please explain to me how MSDE 2.0 is LIMITED
>
> Throttled to 5 processes and maximum of 2 Gb in size.
>
> >Access MDB is LIMITED to a half dozen users and 25 mb of data
>
> Wrong. I've happily had a database with 25 users and 300 mb of data.
>
> Tony
> --
> Tony Toews, Microsoft Access MVP
> Please respond only in the newsgroups so that others can
> read the entire thread of messages.
> Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm

yes, MSDE 1.0 had a harder limit to 5 connections.
MSDE 2.0 has a soft limit.

and SQL Server 2005 Express has NO LIMITS.

ADP works _GREAT_ in a production capacity using SQL Server 2005
Express.

MDB is a pain in the ass; I mean-- aren't you tired of forcing a
reboot of a file server just so that you can take a backup?

ROFL

On Mar 30, 7:47 pm, "Tony Toews [MVP]" <tto...@telusplanet.net> wrote:
> A a r o n K e m p f wrote:
>
> >Please explain to me how MSDE 2.0 is LIMITED
>
> Throttled to 5 processes and maximum of 2 Gb in size.
>
> >Access MDB is LIMITED to a half dozen users and 25 mb of data
>
> Wrong. I've happily had a database with 25 users and 300 mb of data.
>
> Tony
> --
> Tony Toews, Microsoft Access MVP
> Please respond only in the newsgroups so that others can
> read the entire thread of messages.
> Microsoft Access Links, Hints, Tips & Accounting Systems athttp://www.granite.ab.ca/accsmstr.htm