Re: Tools to remotely manage Access 2K

"helveticus" <msch.prv@xxxxxxxxx> wrote in message
news:a3c4fb3d-186a-4168-8945-62869c9a2a36@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
For a site, I plan on using ASP.NET 3.5 tied to Access 2K db because
of licensing reasons.

What tools could I use to remotely manage this DB ? Could it be
accessed via a simple ODBC connection?

No you can not use an ODBC connection over the net to a jet backend mdb
database file. The reason for this is jet is not a socket based system like
the SQL server, and therefore you can't connect over a tc/ip connecton
*unless* you use windows networking. If you look closely at any odbc
connection strings, they **always** resolve to an absolute full file path
location sitting on a computer.

That means the software accessing data from that file must be able to
physically see the file sitting like a regular windows file in a computer
directory. You can no more use ODBC to connect to word, PowerPoint either.
In other words you must be able to physically open the file like any other
power powert file, a word document, or a plain Jane windows file. There's no
database server when using JET, but just the fact that you are opening a
file. You are simply using the windows file system to open the file.

What alternatives do exist (I
have read some posts warning against ADP)?

An ADP will not work in this case unless you are using SQL server (adp ONLY
works with sql server). On the other hand, if you're using SQL server, then
you can connect you mdb front end now via odbc. unless I completely misread
your question, you said that you're not using SQL server at all here, and
are only going to be using jet. (is absolute critical that you have this
issue clarified here).

If ODBC is adequate, I
presume a simple Access client app that resides on my local PC will be
ok. Are there any networking issues to be aware of? Thanks for any
pointers.

MS access in fact does not use ODBC to connect to the back end (unless
you're talking about linked tables to SQL server). Note that you can write
code on your local machine that uses a odbc driver, but you're **still**
physically using the windows file system to open and access that jet file in
all cases. Jet does not run as a separate thread or process from your code
that you useing to develop your software with. It becomes part of the actual
code library that you're using.

Your only choice is to manage this file are:

1)
You use DDL commands (drop table, create table, alter table etc.). You thus
simply send commands via whatever programming language you use. There is a
DDL the language reference in the help, at least there is for access 2003
(however, keep in mind that you're not really using MS access anymore,
you're using what's called the jet engine -- it is simple to realize this
issue because in fact you're not installing MS access on the web server, are
you?). Anyway, virtually any of the processes of creating tables, deleting
tables, and adding fields can be done via SQL ddl commands. that's how you
manage the database.

2)
Simply FTP a copy of the mdb file down to local computer, make the
modifications, and then upload (ftp) it back your web server.

Note that before you grab a copy of this mdb file, you'll have to go into
your website management system, and stop the web server, or at least force
it to close the mdb file because it could be likely that the web server has
that file opened and in use at that point in time. Just like on your regular
computer, you can't really make a copy of mdb file while it is open and use
by other people (in this case likely the web server has the file open). To
be honest I have on many occasions copied the file while it is in use by
others. However what you most certainly can NOT do is copy the mdb filed
back up to the server and overwrite the existing mdb file while it is use by
others. At the very least I suspect you have to delete the mdb file on the
server first if you're not given management tools to stop or de-attach the
web server from that mdb file that is open.


--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@xxxxxxx


.

Relevant Pages

  • Re: SQL Server extremely slow
    ... terms of what is meant by a dis-connected ado recordset. ... table in a mdb file could be considered disconnected from the server ... Well, ok, but keep in mind the disk drive is on sql server! ... 10 reocrds from the server via odbc does not produce more ...
    (comp.databases.ms-access)
  • Linked server to ODBC DSN
    ... Server, and want to create a linked server to this same ODBC DSN: ... Provider = "Microsoft OLE DB Provider for ODBC Driver" ... Then i connect to the SQL Server using QA, and try to run my query (that ...
    (microsoft.public.sqlserver.server)
  • Re: Converting from ADP to MDB/ODBC linked tables
    ... I can tell you that linking tables using ODBC is not the same as ... SQL syntax of your database engine (in your case, SQL Server). ... Access query cannot be rendered into a query which can be executed by the ... Does anybody have any experience converting an ADP file to an MDB file ...
    (microsoft.public.access.adp.sqlserver)
  • Re: DAO vs ADO
    ... > to re-write it at least a little but can I do it in DAO? ... > tables were on a SQL Server! ... ODBC, one table could be on the corporate server, and the other ODBC might ...
    (microsoft.public.access.conversion)
  • Re: error setting up ODBC for remote sqlexpress database
    ... We were able to get it to work with the ODBC native client driver. ... Server could be stopped in the client. ... is to install Management Studio for SQL Server 2005 Express ...
    (microsoft.public.sqlserver.odbc)