I’ve used an Access ADP file ever since 2003 to connect to and manage remote MSSQL databases which are included in certain shared hosting plans. With the latest version of MSSQL, this does not seem to be possible. Is there a known workaround, and if not, is there another program I can use that works the same way?
With Access 2010, I can open the ADP file and connect to the remote database. I can view the list of objects. But that’s it. Sometimes, it will let me view a table in datasheet view. Other times that same table won’t open, but perhaps another one will. I apologize for not having the error message – I will try to recreate this and edit this post. But it has something to do with permissions, and not being able to save design changes. I get the same error in SSMS.
If you look at the history of Access, ADP files, and SQL Server, it seems like MS just never liked the combination. Now, they’ve gotten rid of ADP files altogether. I found that this was a very easy and clear cut way to manage my web databases. So what’s the deal – is it a security issue or it is something else?
Here’s some more optional background reading…
Back around 2003, when hosting providers were just starting to provide databases along with their hosting I would use Access to design queries then use the SQL to copy and paste into the web tools. At some point I stumbled on the ADP file and the connection dialog – entered the login info, and there it was. The one of my hosting companies decided to upgrade to SQL server 2005 and my ADP file would not work. The error I got was something about permissions, and/or I could view, but not save design changes. There was a work-around I found, that involved including the port number along with the server address. Then later, when I upgraded to Office 2007, it worked again without the need to any workaround.
More recently within the last couple years, GoDaddy announced they were going to do some sort of major upgrade to everything – new servers, operating systems, databases, etc. It was up to the user to migrate their databases and site files. The provided instructions that proved to be impossible. The solution required a program – might have been SSMS – to take the database backup file, open it as a text file, then copy and paste it into another window connected to and part of the new database. This was not a big database, but the text file included everything – including the data. I guess it was too much for my clipboard.
Anyway using SSMS, I did figure out how to migrate the database. The backup of the original created a BAK file, and the new one required the type of backup with the SQL extension, that’s why it required this whole migration process and not a simple restore. But I found that SSMS very complicated to use – much harder than it had to be. First, there were two options to migrate a table – either starting from the original and send it, or start from the new one and retrieve it. As I made my way through the configuration screens, I found there was this one option – buried like three levels deep in the dialogs (Advanced buttons, More info buttons, things like that) that if left unchecked, it would basically cause the whole thing to fail. I was lucky enough to find a forum post that mentioned this.
Have I been cheating all these years? I’ve created tables and queries this way and they’ve always worked well. The Dreamweaver database server behaviors have had no trouble access the database objects, and inserting, updating, joining and performing all the usual functions. Do I need to learn SSMS and learn how to create tables using SQL statements and things like this?