Opening 2003 ADP in 2007 fails even with newly defined SQL Serverconnection

[mdupris]

I have an ADP file created in 2003 hitting a SQL Server database and
working fine.When I open it in Access 2007 on a different machine,
same network, I go through the Office button/Server/Connection
sequence to establish the connection to the server and database. All
proceeds normally and the "Test connection" button confirms the
connection succeeded. However when I then go back to the project and,
for instance, open a form, I get the "Access was not able to perform
this operation because the project is not connected to a SQL Server
database" message. Re-opening the "Data Link Properties" dialog using
the above sequence shows everything
blanked out. The titlebar of the Access app shows "(disconnected)"
throughout the process. Anyone have any idea what's going on here? I'm
more than baffled. Thanks!

= M =

 

[Sylvain Lafontaine]

I suppose that you must have some sort of VBA code that is resetting the
connection string. What happens if you open a blank ADP project?

 

[mdupris]

Thanks for the response. Setting up a blank ADP linking into the
server is no problem. It works just as expected. There's no VBA
involved in setting connections either -- I've done it all through
menus and just left it that way. What's interesting (if that's the
right word) is that the "Data Link Properties" is blank as soon as I
re-open it after setting, and testing, the connection. The title bar
also continues to say "disconnected" throughout. It's like the setting
never "takes". Ever heard of anything like that?

= M =

 

[Sylvain Lafontaine]

Search for any call to ConnectionOpen or ConnectionClose (single words, no
point) in your code.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)



Thanks for the response. Setting up a blank ADP linking into the
server is no problem. It works just as expected. There's no VBA
involved in setting connections either -- I've done it all through
menus and just left it that way. What's interesting (if that's the
right word) is that the "Data Link Properties" is blank as soon as I
re-open it after setting, and testing, the connection. The title bar
also continues to say "disconnected" throughout. It's like the setting
never "takes". Ever heard of anything like that?

= M =

 

[Sylvain Lafontaine]

Sorry, it's OpenConnection and CloseConnection, not ConnectionOpen or
ConnectionClose.

 

[xshen]

Have you solved the exact problem that haunts me too?

 

[Sylvain Lafontaine]

Have you solved the exact problem that haunts me too?

Huh?

 

[xshen]

My problem is that the adp file can be opened on other computers without any
problems, but not open on one specific computer (when the adp file is open,
the title bar shows "disconnected"). When I check Server/Connection menu
item, it said "Access was not able to perform this operation because the
project is not connected to a SQL Server database" message.

I also tried to open Control panel/Administrative Tools/Data Sources (ODBC),
it said "ODBC installer DLL (odbccp32.dll) is not installed". When I tried to
install it, it said the package is already included in the Windows XP.

I don't know what the real problem is. Any idea? Thanks!

 

[Sylvain Lafontaine]

A variety of reasons could block the connection between this client's
machine and the server, the first one beeing the port 1433 (it's the default
port) beeing blocked by a firewall. A second possibility is that you are
using an Alias but forgot to set it up on this particular machine. There are
other possibilities as well.

In your case, checking the ODBC Data Sources is of no use because ADP use
OLEDB, not ODBC.

The first thing to do would be to check with a *blank* ADP project (to make
sure that you don't any startup code changing the connection) and test for
the possibility of a firewall. After that, you could try using either the
Named Pipes protocotol or TCP/IP by putting np: or tcp: before the name of
the server. For tcp:; you can also add ,1433 after the name of the server;
for example:

sqlcmd -S tcp:NameOfTheServer\sqlexpress,1433


If you are using dynamically allocated port on the sql-server, make any
necessary adjustement, including checking that the port 1434 (beside the
port 1433) is not blocked as well. The use of an alias for the name of the
server could also help you in solving your connection problem. Here are
some more references for helping you on this topic but I didn't check them
recently:

http://blogs.msdn.com/sql_protocols...e-the-target-machine-actively-refused-it.aspx

http://blogs.msdn.com/sqlexpress/archive/2005/05/05/415084.aspx

http://blogs.msdn.com/sqlexpress/archive/2004/07/23/192044.aspx

http://msdn2.microsoft.com/en-us/library/ms345318.aspx

http://www.datamasker.com/SSE2005_NetworkCfg.htm

 

[mdupris]

My apologies for the delay in responding to many of the ideas here.
I've been traveling and out of touch (in a lot of ways). Unfortunately
none of these ideas has made a difference and the problem remains. I'm
still unable to reliably establish a connection between and ADP file
even a new ADP file, and a named SQL Server. In reverse chronological
order:

Notes to Sylvain 15 Aug):
- Creating a new, blank ADP file results in the same behavior (the
connection wizard 'works' and 'Test Connection' successed but no
actual connection is made, or saved and Access shows "Disconnected" in
its title bar
- Adding the tcp: prefix for the server name, with out without the ",
1433" extension -- a trick I'd never heard of -- also failed in the
same way.
- The "np:" prefix failed with a "Data Link Error" (as I'd
expected, given the supported network protocols here).
- It doesn't seem to be a firewall or port connection. Other ADP
files running on the same machine connecting to the same SQL Server
box have no problem.

Notes xshen (26 July)
- Interesting variant of the problem. I get no complaints about
*.dll files, though, and again some ADP files on my machine work fine,
some don't.

Notes on Sylvain (26 July)
- Disabling all the VBA in the project makes no difference.

Any other ideas floating around out there??

= M =

 

[Sylvain Lafontaine]

Hum, you have said in a previous answer that setting up a blank ADP file was
able to connect without any problem and worked well but now you said that it
doesn't work; this make it hard to distinguish what's going on here.

My opinion is that either the account that you are using cannot connect to
the remote database or that this account map to a different user that is not
dbo or that there is still a firewall blocking something. If you connect
with a different program such as Enterprise Manager (EM) or SQL-Server
Management Studio (SSMS), what are you seeing? What is the owner of the
objects in the database? What are the permissions?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)



My apologies for the delay in responding to many of the ideas here.
I've been traveling and out of touch (in a lot of ways). Unfortunately
none of these ideas has made a difference and the problem remains. I'm
still unable to reliably establish a connection between and ADP file
even a new ADP file, and a named SQL Server. In reverse chronological
order:

Notes to Sylvain 15 Aug):
- Creating a new, blank ADP file results in the same behavior (the
connection wizard 'works' and 'Test Connection' successed but no
actual connection is made, or saved and Access shows "Disconnected" in
its title bar
- Adding the tcp: prefix for the server name, with out without the ",
1433" extension -- a trick I'd never heard of -- also failed in the
same way.
- The "np:" prefix failed with a "Data Link Error" (as I'd
expected, given the supported network protocols here).
- It doesn't seem to be a firewall or port connection. Other ADP
files running on the same machine connecting to the same SQL Server
box have no problem.

Notes xshen (26 July)
- Interesting variant of the problem. I get no complaints about
*.dll files, though, and again some ADP files on my machine work fine,
some don't.

Notes on Sylvain (26 July)
- Disabling all the VBA in the project makes no difference.

Any other ideas floating around out there??

= M =

 

[xshen]

In my case, same adp files connecting to the same remote SQL server can work
on other computers but not on a specific one on which the files started with
troubles after I made a cleanup of viruses (some files deleted or some
settings modified). I also tried to open ports of 1433 and 1434, but no
changes of the problem.

 

[Sylvain Lafontaine]

If you had problems with viruses on this computer, the first thing to do
would be to make a complete reinstallation of Windows on it.

Also, do not forget to decompile the ADP file when you copy it to another
machine.