Managing MS SQL Server via MS Access

[helveticus]

I designed an application with ASP:NET 3.5 and SQL Server Express and
plan on hosting the DB in MS SQL Server 2005 (??). In the design
configuration, all SQL Express tables are directly managed (i.e.
updating, inserting, querying, etc. ) by MS Access via ODBC. This
works very well on my local application.

I still use Office 2K (works fine for my current needs ;=) ) and
wonder if a remote MS SQL Server could be managed with the same ease
via say ODBC/ADP. Does this still make sense? (I tried SQL Server
Management Studio Express, but feel that MS Access provides far
better convenience.) Should I upgrade MS Access or resort to ad hoc MS
SQL Server DB management tools (which ones)? What are the best
options?

Basically, I' m looking for something similar to phpMyAdmin in PHP/
MySQL land. II really enjoy using Access and would like to stick to it
as much as possible. TIA for any pointers.

 

[AG]

Have you tried and Access ADP?

 

[helveticus]

Thanks for the reply. I have some problems connecting to SQL Express
via ADP (probably due to some TCP/IP addressing issues) and therefore
did never try ADP. Assuming I use ADP, would this allow me to easily
leverage MS SQL Server SPROC's (stored procedures) and handle the
results with conventional Access queries?

I know that Access does not scale. However, for a low traffic site,
would an Access 2K DB be ok? I saw estimates varying between 5 and 20
concurrent users. (Is it closer to 5 or 20?) The DB would not be
solicited too much: nearly all pages are cached. Sorry if the latter
question is beyond the scope of this forum.

 

[a a r o n . k e m p f]

ADP is _JUST_ like phpmyadmin for SQL Server.

you build queries in ADP-- and they show up in SQL Server.
That is all you want right?

-Aaron

 

[Guest]

ODBC runs a bit slower remote, but it doesn't stop working.
However, most SQL Servers are not directly exposed to the
interenet: most people have firewalls and the ports are closed.

When you say remote, do you mean like from home? You
would probably need a VPN to get through the firewall.

When I say slower, I don't mean unusable for admin, just
not as crisp as most user applications.

(david)

 

[helveticus]

Thanks, David.

I'll check with the ISP to see if I can access SQL Server via VPN.
This would save me lots of time. I don't want to add extra admin pages
if the site can be managed from a local front-end client such as
Access just to check on status fields, update content, etc.

Besides Access, what kind of front-end clients would you suggest (SQL
Management seems fairly heavy and does not provide the convenience
Access excels at.)?

 

[Guest]

Don't know of any 'good like Access' Admin tools, but
you can get used to using the arcane magic of the standard
tools. If it was too easy, everyone would do it.

(david)