ADP file with latest version of MSSQL remote database - possible?

Other SQL Server Topics
1

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?

2

SSMS does have a GUI to do a lot of the work, but you are doing yourself a disservice if you continue to use a GUI. You need to bite the bullet and start learning how to create queries by hand. The GUIs do a terrible job and often are very inefficient.

3

Thank you for your response and your candor. Actually I did get to the point where I could write out at least the basic SELECT statements using the basic SELECT, FROM, WHERE and ORDER BY. I even - with a lot of help - was successful with a few sub-queries. One thing that always gave me trouble were the various JOINs. Most of what I do is pretty basic though.

So with all the words in my post, I did fail to emphasize that what I depended on Access and the ADP file I used for for design operations other than creating view/queries. Such as - plain old tables. Creating a new table and setting all the column parameters, It was also very easy to make a copy of a table, with or without the data included. And, importing data to a new or existing table was also very easy using the ADP file.

Would you also say that it's better to use an SQL statement to create tables than via a GUI? I have done this before but it was a copy/paste thing, but I understood it well enough to edit it to meet the requirements.

Last question - and thank you again - what about mySWL along with phpmyAdmin? Does this have the same inefficiencies as GUIs for MSSQL? I've found that phpMyAdmin gives you enough to get you started but requires more of the user for queries and other things

4

You can definitely use the GUI to create tables.

To copy a table with data, you can use SELECT INTO syntax. To copy it without data, use SELECT INTO syntax with WHERE 1=0. But that only gives you the table and not any constraints, etc. You can use the GUI to generate the rest of the dependent objects.

I have no experience with MySQL or phpmyAdmin, so I can't comment on that.

I do everything in a query window. If I can't remember the syntax, I look up the command via a quick google search, click on the MSDN page, go to the end of the document where the examples are, and then copy/paste the example that most closely fits what I am trying to do. Then I edit it as needed.

5

Thanks for the tip for SELECT
INTO, that’s a new one for me.

As far as using the ADP file
GUI or not, it looks like that just won’t work any longer. As I think I
mentioned in my overly long initial post, it will connect to the database and
display all the objects. But the most it will allow me to do is perhaps open
some tables – for the others, I get a permissions error. But I get the same
error using SSMS. This tells me that it’s either the version of SQL server, or
it’s the config that Godaddy has set up being that it’s a shared server.

On a side note, their “new” Windows platforms
not only have the newest version of SQL server, but use the Plesk control
panel. That has some weird configurations – when I migrated the site, I saw
that many pages that used includes didn’t work. After investigation, I saw that
the default setting for relative paths was disabled. To manage the database,
the offer lite version of some web GUI which doesn’t even allow creating
tables. I will have to see if at least I can use the query window.