1

We have moved our SQL Server instance to a new hosting company. Months later (after the overlapping migration period), front office have dug up an old adp file (old-school access data project) which connects to our (now non-existing) SQL Server. I need to change the connection string inside.

The file won't open, and crashes Access when I try. I have searched in vain and only come across: "Hold down SHIFT while opening the file". This doesn't solve it.

Has anyone experienced this and has a solution?

Hansjp
  • 66
  • 1
  • 6
  • What version of Access are you currently using, and, if you know, what version of Access was the ADP created? – Johnny Bones Jun 30 '17 at 13:41
  • We are currently using Access 2010 and the ADP was created to 2007 (I am pretty sure - could have been 2003), but migrated (successfully) to 2010. – Hansjp Jul 03 '17 at 16:57
  • Try adding an entry in your hosts file pointing the old name to the new server's IP. See if that at least gets you into the ADP to change the name... – Barett Jul 24 '17 at 20:07
  • @Barett thanks. I didn't think of that, but yes that might've worked. As I answered my own questions on July 6th with the notion that I now simply give up and delete the file, I can't test whether it will work. – Hansjp Jul 31 '17 at 14:14

3 Answers3

2

Well, I'll just go ahead and leave an answer since I might forget to check back later.

You can only open an ADP file in Access 2010 or earlier. Starting with Access 2013, ADP files are unsupported a unrecognized by Access. You will need to get ahold of a 3rd party converter to convert the ADP to ACCDB.

So, if you have a version of 2010 or earlier, you would just open the file as you normally would.

To change the SQL Server, just follow these steps:

  1. Click the Microsoft Office Button Office button image , and then click Open.

  2. In the Open dialog box, select and open the Access project.

    • A Microsoft Office Access Security Notice dialog box might appear. If you trust the source of the database, click Open to continue.

  3. Click the Microsoft Office Button Office button image , point to Server, and then click Connection.

    • The Data Link Properties dialog box appears.

  4. In the dialog box, enter the required information for the following:

    • Server name

    • Information needed to log on to the server

    • Database name

  5. Click Test Connection to verify that the settings are correct and that the connection succeeds. If the connection does not succeed, Access displays an error message describing the problem. Click OK to close the message, check your settings in the Data Link Properties dialog box, and then click Test Connection again. Also, you might need to check with the SQL Server database administrator for the database you are connecting to, to make sure your settings are correct or that the computer is available.

  6. After the Microsoft Data Link dialog box displays the message Test connection succeeded, click OK to close the dialog box, and then click OK to close the Data Link Properties dialog box.

Johnny Bones
  • 8,786
  • 7
  • 52
  • 117
  • Thanks for the suggestion. Yes, I have Access 2010. But no Office Button. Don't know if it makes a difference. Can't open the program though. That's what I ask about...: How to _circumvent_ the pre-opening check of connection I suspect is happening. – Hansjp Jul 03 '17 at 17:00
0

Well, I am going to close this question. Issue still exists (as such), but we are simply deleting the adp file now and build a replacement from scratch. I have now tried everything I can come to think of, including renaming the file extension to .mdb or .accdb, and unplugging the pc from any network connection while opening.

Every attempt ends in the same: Access does not respond, and I have to force-kill the application. I don't even know if the issue is what I suspect: the connection string being invalid due to a change of server names. But I can't think of any other explanation.

Hansjp
  • 66
  • 1
  • 6
0

I know this an ugly old issue, but maybe others suffer from open-up such old projects as well right now and stumble upon this one here.

I too was unable to change the connection but managed to extract the source code from the ADP. In my case, this was enough to get the most valuable information back.

https://github.com/Riffer/officeparser/tree/access-data-project

please have a look at the original, too - I only had to make a small change to include the source code from a form. Maybe others are willing to contribute and could implement an extraction method to get the form definitions back as well, I don't know.

Riffer
  • 31
  • 5