Managing and working with remote databases is always a fun proposition. This is especially true in the wired world of the Internet. Managing your database when it’s located on an ISP’s Web server somewhere over the Internet is especially challenging. You can, of course, use Enterprise Manager to remotely manage a SQL Server database.
But sometimes you need to manage a server and you don’t have Enterprise Manager loaded on the system you’re working on, or for some other reason, you need another tool with a different feature set. Microsoft solved this problem by adding the Visual Database tools to most of its development tools. Even Enterprise Manager in SQL Server 7.0 uses the Visual Database tools for tasks such as table maintenance, queries, and database design.
While working on a Web application recently, I needed to manage SQL Server. As I was starting to move to a system with Enterprise Manager, I wondered whether I could use Access 2000 to complete the management task. I hadn’t used Access 2000 against a remote SQL Server database before then, so I was prepared for an interesting experience.
I fired up Access 2000. In the first dialog box that displayed, I selected Access databases wizards, pages, and projects, then clicked OK. In the next dialog box, I clicked the General tab. The General page displayed four types of action. I selected Project (Existing Database), then clicked OK. The File New Database dialog box appeared next, letting me save the project. I entered the project name and clicked Create.
The next dialog box that appeared let me select the database the project would use. For the server name, I entered the DNS of the database server. Next, I entered my security credentials and selected the database to use from the database list. Clicking OK completed the process.
I then had the existing database open in Access. And guess what? Access 2000 uses the Visual Database tools for many common database tasks. So, Access 2000 feels like other, familiar Microsoft tools that you use to work with a database. I was able to quickly change columns, add new ones, delete a table, and complete other tasks by using the Access interface. I can’t think of a simpler way to work on a remote database.