Remotely managing Access2K
helveticus
for a specific site..
What tools could I use to remotely manage the Access 2K DB? Would a
simple ODBC connection be adequate? I read some posts warning against
ADP, (plus I don't want to re-write extra code.). If ODBC is ok, I
presume the remote DB could be managed via a local client
application. Are there any networking issues to be aware of which
possibly could play havoc? Thanks for any pointers.
![Mark Rae [MVP]'s profile photo](https://lh3.googleusercontent.com/a/default-user=s40-c)
Mark Rae [MVP]
news:228e4432-2cea-497f...@m36g2000hse.googlegroups.com...
First things first: mainstream support for Access 2000 was retired almost
two years ago. Although the product is currently under extended support,
this is also set to end next year, after which you will be on your own if
anything happens. In addition, an extended support contract is extremely
expensive - you do have one, don't you...?
Secondly, ODBC is very old technology these days, having been superseded by
OleDb many years ago. What do you mean by "remotely manage"? Is it just
data, or will you be modifying the database schema, taking backups etc...?
Thirdly, the Jet engine is not really suitable for web applications,
especially those with lots of concurrent connections:
http://en.wikipedia.org/wiki/Microsoft_Jet_Database_Engine
Is there any reason that you can't use SQL Server Express Edition?
http://msdn.microsoft.com/en-us/express/aa718378.aspx
--
Mark Rae
ASP.NET MVP
http://www.markrae.net
helveticus
using A2K as the front-end. However, I find nothing beats Access for
designing front-end applications in short notice.
In the present case, I would like to capitalize on the DB manager I
wrote in A2K. It allows me to do basic CRUD operations on all relevant
SQL Express tables (no database schema modifications) plus specific
processing. I don't want to replicate this work in the web's Admin
section. Ideally, the info should be download/uploaded and processed
locally. Assuming I upgrade to ACC2003/ACC2007, would this kind of
"remote DB management" be possible via OleDB for instance? TIA.
Mark Rae [MVP]
> Thanks for the reply. I have been working with A2K and SQL Express,
> using A2K as the front-end. However, I find nothing beats Access for
> designing front-end applications in short notice.
Er, OK... I'm puzzled as to why you would want to use Access as a front-end
to SQL Server Express when it has its own:
http://www.microsoft.com/downloads/details.aspx?FamilyID=c243a5ae-4bd1-4e3d-94b8-5a0f62bf7796&displaylang=en
> In the present case, I would like to capitalize on the DB manager I
> wrote in A2K. It allows me to do basic CRUD operations on all relevant
> SQL Express tables (no database schema modifications) plus specific
> processing.
Er, so are you now saying that the RDBMS you're using is SQL Server
Express...? If so, I can't understand what you need Access for...
George Ter-Saakov
SQL Express tables from MS Access..
Which is OK.
Just use SQL Express from your ASP.NET application and continue to use MS
Access as a front end for desktop users
Do not use ASP.NET in way so it's hitting MS Access linked tables. There is
no really point in that (except having problems :)
George.
"Mark Rae [MVP]" <ma...@markNOSPAMrae.net> wrote in message
news:%23jKvIss...@TK2MSFTNGP06.phx.gbl...
helveticus
George got the point: I am linking up via ODBC to SQL Express tables
and would like to keeping doing so even when the DB is moved over to a
ISP host. This works fine on my local machine and was hoping that this
kind of setup would also work in such a remote configuration.
Could you please elaborate on " Do not use ASP.NET in way so it's
hitting MS Access linked tables." ? Does this refer to locking, etc.?
TIA
Mark Rae [MVP]
> I am linking up via ODBC to SQL Express tables
So, what did you mean in your OP by "What tools could I use to remotely
manage the Access 2K DB?"
helveticus
A2K as a back-end on the site to avoid SQL Server charges. It seems
however that one cannot use an ODBC connection to link up to a remote
JET backend database file such as A2K, since it is not a socket based
system accessible via TCP/IP. Furthermore, as you pointed out, using
A2K as a DB would be risky since MS has dropped the product.
From the posts I have read, I have come to the conclusion that the
best approach would be to use SQL Server on the site, link up to this
DB via ODBC from a local Access front-end client to "manage" tables
(ie. simple edit/insert/delete, no schema alteration). For "heavy
artillery" operations, I would use SQL Server Management.
Is ODBC reliable? Some posts suggest that ODBC are vulnerable to
connection break ups, etc
Mark Rae [MVP]
> My original idea was to use A2K as a back-end on the site to avoid
> SQL Server charges.
SQL Server Express Edition is *completely* free.
> From the posts I have read, I have come to the conclusion that the
> best approach would be to use SQL Server on the site, link up to this
> DB via ODBC from a local Access front-end client to "manage" tables
> (ie. simple edit/insert/delete, no schema alteration). For "heavy
> artillery" operations, I would use SQL Server Management.
As I have already mentioned, SQL Server Express Edition already has its own
management software, so there's really no need to use anything else to
manage it. That's what it's for...
> Is ODBC reliable? Some posts suggest that ODBC are vulnerable to
> connection break ups, etc
If you use SQL Server Express Management then you don't need to worry about
ODBC at all...
sloan
Yeah dude (to the OP).
Please heed this advice. The sooner you make the break and better off you
will be.
Sql Server Express Edition is FREE.
Scratching your back by putting your arm between your legs is the equivalent
of trying to use ODBC/Access.
I'm not trying to be mean, just trying to firmly let you know that it would
be much wiser to heed this advice.
"Mark Rae [MVP]" <ma...@markNOSPAMrae.net> wrote in message
news:%23KEm$rytIH...@TK2MSFTNGP02.phx.gbl...
George Ter-Saakov
do not hit your MS Access mdb file which has only linked tables.
There is no problem with ODBC working over network whatsoever. Then only
thing it's as reliable as network is. So may be it's not good idea to run it
over wireless network,,,
George.
"helveticus" <msch...@gmail.com> wrote in message
news:8336819c-df73-461b...@m73g2000hsh.googlegroups.com...
George Ter-Saakov
It's management in terms of an applicaiton that updates tables, insert
records and does all kind of business logic needed to be done when something
happens...
The Sql's managment software does not do all that...
PS: I do find MS Acceess the best tool to quickly create a nice application
with reports and such..
George.
"sloan" <sl...@ipass.net> wrote in message
news:eQj9ow1t...@TK2MSFTNGP03.phx.gbl...
Mark Rae [MVP]
news:%233ZT081...@TK2MSFTNGP06.phx.gbl...
> My guess it's not management aka (creating tables, backing up....)
> It's management in terms of an applicaiton that updates tables, insert
> records and does all kind of business logic needed to be done when
> something happens...
> The Sql's managment software does not do all that...
Do you mean things like triggers, stored procedures, exception handling, CLR
& .NET integration, UDTs, native XML, XQuery etc...?
> PS: I do find MS Acceess the best tool to quickly create a nice
> application with reports and such..
Me too. For the desktop. But Access is totally unsuited to this sort of web
usage. For one thing, you'd need to restrict any queries to
SQL-pass-through, otherwise Access will bring the entire superset of tables
included in the query across the wire to the desktop so that the Jet engine
can process them