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

Discussion in 'Microsoft Access ADP SQL Server' started by mdupris@hotmail.com, Jul 26, 2008.

  1. Guest

    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 =
     
    , Jul 26, 2008
    #1
    1. Advertisements

  2. Re: Opening 2003 ADP in 2007 fails even with newly defined SQL Server connection

    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?

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


    <> wrote in message
    news:...
    >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, Jul 26, 2008
    #2
    1. Advertisements

  3. Guest

    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 =


    On Jul 25, 7:07 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
    blanks, no spam please)> wrote:
    > 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?
    >
    > --
    > Sylvain Lafontaine, ing.
    > MVP - Technologies Virtual-PC
    > E-mail: sylvain aei ca (fill the blanks, no spam please)
    >
    > <> wrote in message
    >
    > news:...
    >
    > >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 =
     
    , Jul 26, 2008
    #3
  4. Re: Opening 2003 ADP in 2007 fails even with newly defined SQL Server connection

    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)


    <> wrote in message
    news:...

    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 =


    On Jul 25, 7:07 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
    blanks, no spam please)> wrote:
    > 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?
    >
    > --
    > Sylvain Lafontaine, ing.
    > MVP - Technologies Virtual-PC
    > E-mail: sylvain aei ca (fill the blanks, no spam please)
    >
    > <> wrote in message
    >
    > news:...
    >
    > >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, Jul 26, 2008
    #4
  5. Re: Opening 2003 ADP in 2007 fails even with newly defined SQL Server connection

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

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


    "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
    wrote in message news:u16$...
    > 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)
    >
    >
    > <> wrote in message
    > news:...
    >
    > 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 =
    >
    >
    > On Jul 25, 7:07 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
    > blanks, no spam please)> wrote:
    >> 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?
    >>
    >> --
    >> Sylvain Lafontaine, ing.
    >> MVP - Technologies Virtual-PC
    >> E-mail: sylvain aei ca (fill the blanks, no spam please)
    >>
    >> <> wrote in message
    >>
    >> news:...
    >>
    >> >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, Jul 26, 2008
    #5
  6. xshen Guest

    Re: Opening 2003 ADP in 2007 fails even with newly defined SQL Ser

    Have you solved the exact problem that haunts me too?

    "Sylvain Lafontaine" wrote:

    > Sorry, it's OpenConnection and CloseConnection, not ConnectionOpen or
    > ConnectionClose.
    >
    > --
    > Sylvain Lafontaine, ing.
    > MVP - Technologies Virtual-PC
    > E-mail: sylvain aei ca (fill the blanks, no spam please)
    >
    >
    > "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
    > wrote in message news:u16$...
    > > 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)
    > >
    > >
    > > <> wrote in message
    > > news:...
    > >
    > > 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 =
    > >
    > >
    > > On Jul 25, 7:07 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
    > > blanks, no spam please)> wrote:
    > >> 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?
    > >>
    > >> --
    > >> Sylvain Lafontaine, ing.
    > >> MVP - Technologies Virtual-PC
    > >> E-mail: sylvain aei ca (fill the blanks, no spam please)
    > >>
    > >> <> wrote in message
    > >>
    > >> news:...
    > >>
    > >> >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 =

    > >
    > >

    >
    >
    >
     
    xshen, Aug 14, 2008
    #6
  7. Re: Opening 2003 ADP in 2007 fails even with newly defined SQL Ser

    > Have you solved the exact problem that haunts me too?

    Huh?

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


    "xshen" <> wrote in message
    news:...
    > Have you solved the exact problem that haunts me too?
    >
    > "Sylvain Lafontaine" wrote:
    >
    >> Sorry, it's OpenConnection and CloseConnection, not ConnectionOpen or
    >> ConnectionClose.
    >>
    >> --
    >> Sylvain Lafontaine, ing.
    >> MVP - Technologies Virtual-PC
    >> E-mail: sylvain aei ca (fill the blanks, no spam please)
    >>
    >>
    >> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
    >> wrote in message news:u16$...
    >> > 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)
    >> >
    >> >
    >> > <> wrote in message
    >> > news:...
    >> >
    >> > 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 =
    >> >
    >> >
    >> > On Jul 25, 7:07 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
    >> > blanks, no spam please)> wrote:
    >> >> 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?
    >> >>
    >> >> --
    >> >> Sylvain Lafontaine, ing.
    >> >> MVP - Technologies Virtual-PC
    >> >> E-mail: sylvain aei ca (fill the blanks, no spam please)
    >> >>
    >> >> <> wrote in message
    >> >>
    >> >> news:...
    >> >>
    >> >> >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, Aug 14, 2008
    #7
  8. xshen Guest

    Re: Opening 2003 ADP in 2007 fails even with newly defined SQL Ser

    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" wrote:

    > > Have you solved the exact problem that haunts me too?

    >
    > Huh?
    >
    > --
    > Sylvain Lafontaine, ing.
    > MVP - Technologies Virtual-PC
    > E-mail: sylvain aei ca (fill the blanks, no spam please)
    >
    >
    > "xshen" <> wrote in message
    > news:...
    > > Have you solved the exact problem that haunts me too?
    > >
    > > "Sylvain Lafontaine" wrote:
    > >
    > >> Sorry, it's OpenConnection and CloseConnection, not ConnectionOpen or
    > >> ConnectionClose.
    > >>
    > >> --
    > >> Sylvain Lafontaine, ing.
    > >> MVP - Technologies Virtual-PC
    > >> E-mail: sylvain aei ca (fill the blanks, no spam please)
    > >>
    > >>
    > >> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
    > >> wrote in message news:u16$...
    > >> > 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)
    > >> >
    > >> >
    > >> > <> wrote in message
    > >> > news:...
    > >> >
    > >> > 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 =
    > >> >
    > >> >
    > >> > On Jul 25, 7:07 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
    > >> > blanks, no spam please)> wrote:
    > >> >> 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?
    > >> >>
    > >> >> --
    > >> >> Sylvain Lafontaine, ing.
    > >> >> MVP - Technologies Virtual-PC
    > >> >> E-mail: sylvain aei ca (fill the blanks, no spam please)
    > >> >>
    > >> >> <> wrote in message
    > >> >>
    > >> >> news:...
    > >> >>
    > >> >> >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 =
    > >> >
    > >> >
    > >>
    > >>
    > >>

    >
    >
    >
     
    xshen, Aug 15, 2008
    #8
  9. Re: Opening 2003 ADP in 2007 fails even with newly defined SQL Ser

    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

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


    "xshen" <> wrote in message
    news:...
    > 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" wrote:
    >
    >> > Have you solved the exact problem that haunts me too?

    >>
    >> Huh?
    >>
    >> --
    >> Sylvain Lafontaine, ing.
    >> MVP - Technologies Virtual-PC
    >> E-mail: sylvain aei ca (fill the blanks, no spam please)
    >>
    >>
    >> "xshen" <> wrote in message
    >> news:...
    >> > Have you solved the exact problem that haunts me too?
    >> >
    >> > "Sylvain Lafontaine" wrote:
    >> >
    >> >> Sorry, it's OpenConnection and CloseConnection, not ConnectionOpen or
    >> >> ConnectionClose.
    >> >>
    >> >> --
    >> >> Sylvain Lafontaine, ing.
    >> >> MVP - Technologies Virtual-PC
    >> >> E-mail: sylvain aei ca (fill the blanks, no spam please)
    >> >>
    >> >>
    >> >> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
    >> >> please)>
    >> >> wrote in message news:u16$...
    >> >> > 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)
    >> >> >
    >> >> >
    >> >> > <> wrote in message
    >> >> > news:...
    >> >> >
    >> >> > 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 =
    >> >> >
    >> >> >
    >> >> > On Jul 25, 7:07 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
    >> >> > blanks, no spam please)> wrote:
    >> >> >> 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?
    >> >> >>
    >> >> >> --
    >> >> >> Sylvain Lafontaine, ing.
    >> >> >> MVP - Technologies Virtual-PC
    >> >> >> E-mail: sylvain aei ca (fill the blanks, no spam please)
    >> >> >>
    >> >> >> <> wrote in message
    >> >> >>
    >> >> >> news:...
    >> >> >>
    >> >> >> >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, Aug 15, 2008
    #9
  10. Guest

    Re: Opening 2003 ADP in 2007 fails even with newly defined SQL Ser

    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 =


    On Aug 15, 9:22 am, "Sylvain Lafontaine" <sylvain aei ca (fill the
    blanks, no spam please)> wrote:
    > 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/archive/2007/07/19/tcp-provider-n...
    >
    > 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
    >
    > --
    > Sylvain Lafontaine, ing.
    > MVP - Technologies Virtual-PC
    > E-mail: sylvain aei ca (fill the blanks, no spam please)
    >
    > "xshen" <> wrote in message
    >
    > news:...
    >
    > > 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" wrote:

    >
    > >> > Have you solved the exact problem that haunts me too?

    >
    > >> Huh?

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

    >
    > >> "xshen" <> wrote in message
    > >>news:...
    > >> > Have you solved the exact problem that haunts me too?

    >
    > >> > "Sylvain Lafontaine" wrote:

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

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

    >
    > >> >> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
    > >> >> please)>
    > >> >> wrote in messagenews:u16$...
    > >> >> > 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)

    >
    > >> >> > <> wrote in message
    > >> >> >news:..com...

    >
    > >> >> > 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 =

    >
    > >> >> > On Jul 25, 7:07 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
    > >> >> > blanks, no spam please)> wrote:
    > >> >> >> 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?

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

    >
    > >> >> >> <> wrote in message

    >
    > >> >> >>news:...

    >
    > >> >> >> >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 =
     
    , Aug 22, 2008
    #10
  11. Re: Opening 2003 ADP in 2007 fails even with newly defined SQL Ser

    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)


    <> wrote in message
    news:...

    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 =


    On Aug 15, 9:22 am, "Sylvain Lafontaine" <sylvain aei ca (fill the
    blanks, no spam please)> wrote:
    > 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/archive/2007/07/19/tcp-provider-n...
    >
    > 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
    >
    > --
    > Sylvain Lafontaine, ing.
    > MVP - Technologies Virtual-PC
    > E-mail: sylvain aei ca (fill the blanks, no spam please)
    >
    > "xshen" <> wrote in message
    >
    > news:...
    >
    > > 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" wrote:

    >
    > >> > Have you solved the exact problem that haunts me too?

    >
    > >> Huh?

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

    >
    > >> "xshen" <> wrote in message
    > >>news:...
    > >> > Have you solved the exact problem that haunts me too?

    >
    > >> > "Sylvain Lafontaine" wrote:

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

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

    >
    > >> >> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
    > >> >> please)>
    > >> >> wrote in messagenews:u16$...
    > >> >> > 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)

    >
    > >> >> > <> wrote in message
    > >> >> >news:...

    >
    > >> >> > 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 =

    >
    > >> >> > On Jul 25, 7:07 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
    > >> >> > blanks, no spam please)> wrote:
    > >> >> >> 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?

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

    >
    > >> >> >> <> wrote in message

    >
    > >> >> >>news:...

    >
    > >> >> >> >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, Aug 22, 2008
    #11
  12. xshen Guest

    Re: Opening 2003 ADP in 2007 fails even with newly defined SQL Ser

    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" wrote:

    > 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)
    >
    >
    > <> wrote in message
    > news:...
    >
    > 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 =
    >
    >
    > On Aug 15, 9:22 am, "Sylvain Lafontaine" <sylvain aei ca (fill the
    > blanks, no spam please)> wrote:
    > > 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/archive/2007/07/19/tcp-provider-n...
    > >
    > > 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
    > >
    > > --
    > > Sylvain Lafontaine, ing.
    > > MVP - Technologies Virtual-PC
    > > E-mail: sylvain aei ca (fill the blanks, no spam please)
    > >
    > > "xshen" <> wrote in message
    > >
    > > news:...
    > >
    > > > 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" wrote:

    > >
    > > >> > Have you solved the exact problem that haunts me too?

    > >
    > > >> Huh?

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

    > >
    > > >> "xshen" <> wrote in message
    > > >>news:...
    > > >> > Have you solved the exact problem that haunts me too?

    > >
    > > >> > "Sylvain Lafontaine" wrote:

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

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

    > >
    > > >> >> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
    > > >> >> please)>
    > > >> >> wrote in messagenews:u16$...
    > > >> >> > 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)

    > >
    > > >> >> > <> wrote in message
    > > >> >> >news:...

    > >
    > > >> >> > 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 =

    > >
    > > >> >> > On Jul 25, 7:07 pm, "Sylvain Lafontaine" <sylvain aei ca (fill the
    > > >> >> > blanks, no spam please)> wrote:
    > > >> >> >> 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?

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

    > >
    > > >> >> >> <> wrote in message

    > >
    > > >> >> >>news:...

    > >
    > > >> >> >> >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 =

    >
    >
    >
     
    xshen, Aug 26, 2008
    #12
  13. Re: Opening 2003 ADP in 2007 fails even with newly defined SQL Ser

    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.

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


    "xshen" <> wrote in message
    news:...
    > 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" wrote:
    >
    >> 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)
    >>
    >>
    >> <> wrote in message
    >> news:...
    >>
    >> 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 =
    >>
    >>
    >> On Aug 15, 9:22 am, "Sylvain Lafontaine" <sylvain aei ca (fill the
    >> blanks, no spam please)> wrote:
    >> > 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/archive/2007/07/19/tcp-provider-n...
    >> >
    >> > 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
    >> >
    >> > --
    >> > Sylvain Lafontaine, ing.
    >> > MVP - Technologies Virtual-PC
    >> > E-mail: sylvain aei ca (fill the blanks, no spam please)
    >> >
    >> > "xshen" <> wrote in message
    >> >
    >> > news:...
    >> >
    >> > > 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" wrote:
    >> >
    >> > >> > Have you solved the exact problem that haunts me too?
    >> >
    >> > >> Huh?
    >> >
    >> > >> --
    >> > >> Sylvain Lafontaine, ing.
    >> > >> MVP - Technologies Virtual-PC
    >> > >> E-mail: sylvain aei ca (fill the blanks, no spam please)
    >> >
    >> > >> "xshen" <> wrote in message
    >> > >>news:...
    >> > >> > Have you solved the exact problem that haunts me too?
    >> >
    >> > >> > "Sylvain Lafontaine" wrote:
    >> >
    >> > >> >> Sorry, it's OpenConnection and CloseConnection, not
    >> > >> >> ConnectionOpen
    >> > >> >> or
    >> > >> >> ConnectionClose.
    >> >
    >> > >> >> --
    >> > >> >> Sylvain Lafontaine, ing.
    >> > >> >> MVP - Technologies Virtual-PC
    >> > >> >> E-mail: sylvain aei ca (fill the blanks, no spam please)
    >> >
    >> > >> >> "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam
    >> > >> >> please)>
    >> > >> >> wrote in messagenews:u16$...
    >> > >> >> > 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)
    >> >
    >> > >> >> > <> wrote in message
    >> > >> >> >news:...
    >> >
    >> > >> >> > 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 =
    >> >
    >> > >> >> > On Jul 25, 7:07 pm, "Sylvain Lafontaine" <sylvain aei ca (fill
    >> > >> >> > the
    >> > >> >> > blanks, no spam please)> wrote:
    >> > >> >> >> 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?
    >> >
    >> > >> >> >> --
    >> > >> >> >> Sylvain Lafontaine, ing.
    >> > >> >> >> MVP - Technologies Virtual-PC
    >> > >> >> >> E-mail: sylvain aei ca (fill the blanks, no spam please)
    >> >
    >> > >> >> >> <> wrote in message
    >> >
    >> > >> >> >>news:...
    >> >
    >> > >> >> >> >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, Aug 27, 2008
    #13
    1. Advertisements

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. BJ Freeman

    Re: ADP routine fails in ADP.

    BJ Freeman, Jul 7, 2003, in forum: Microsoft Access ADP SQL Server
    Replies:
    0
    Views:
    646
    BJ Freeman
    Jul 7, 2003
  2. Vadim Rapp

    Re: ADP routine fails in ADP.

    Vadim Rapp, Jul 7, 2003, in forum: Microsoft Access ADP SQL Server
    Replies:
    0
    Views:
    595
    Vadim Rapp
    Jul 7, 2003
  3. Kevin McCall

    Export SQL User-Defined Function or SQL View From ADP?

    Kevin McCall, Aug 7, 2003, in forum: Microsoft Access ADP SQL Server
    Replies:
    0
    Views:
    1,108
    Kevin McCall
    Aug 7, 2003
  4. Carl

    2002 ADP versus 2003 ADP

    Carl, Dec 11, 2003, in forum: Microsoft Access ADP SQL Server
    Replies:
    3
    Views:
    365
    Sylvain Lafontaine
    Dec 12, 2003
  5. Paul Makulski

    Can adp see SQL User Defined Functions?

    Paul Makulski, Mar 19, 2004, in forum: Microsoft Access ADP SQL Server
    Replies:
    1
    Views:
    257
    MGFoster
    Mar 19, 2004
Loading...

Share This Page