Cannot Reconnect MS Access 2002 Project

Posted on 2006-05-05
Last Modified: 2006-11-18
We have an MS Access 2002 Project that connects to a SQL Server 2000 back end.  Everything worked fine yesterday, but doesn't today.  The project has somehow become disconnected.  If I go to File > Connection, I can test the connection, and it completes successfully.

I tried creating a brand new Project.  I used the upsizing wizard to take one of our MDBs and convert it to an SQL DB.  The wizard completed without error and created an Access Project file to connect to the new SQL DB.  Now the new Project is disconnected!  I logged into the SQL Server Enterprise Manager and can see the new DB.  If I simply link to the tables using an ODBC connection I can see the data, so I know the upsizing worked.

So it appears that the network connection is working fine, the SQL Server is being referenced correctly, and all should be well.  Where is the option to reconnect?

Does anyone have any idea what gives?

Question by:rjander77
    LVL 44

    Expert Comment

    by:Leigh Purvis
    Can you connect to an alternative database on the server from your ADP?
    (OK none of your forms or reports etc will be relelvant - but just to see if you can see the tables, views etc).

    If so then Link to your original database again.

    I can't imagine setting the connection in code will make any difference.
    But re-connecting again might.

    Author Comment

    I think ignorance is my problem here.  I can set the connection settings (File > Connection >Data Link Properties) to any other database I want, and yes, the 'Test Connection' completes successfully, but after I click OK, nothing happens.  It remains disconnected.  I am unable to find the mythical magic button that simply lets me connect - not just test the connection, but actually connect.

    I realize I'm an idiot, but 500 points says you can fix that!

    Thanks for the help, and sorry for the delayed response.
    LVL 44

    Expert Comment

    by:Leigh Purvis
    So let's get this straight.

    You can select another database on the same server.
    Connect to it - and you see it's tables, views etc.

    But when you do the same to your target database - after selecting it in Connection - there are no tables or views available?
    But they're definately there if you look in Enterprise Manager?

    The same if you create another ADP from scratch - and ask to link it to the database you want?

    Author Comment

    Almost.  I can select another database in my current ADP (or a new one created from scratch just to test this problem).  I can 'Test Connect' to it, but when I click OK, there's nothing - no tables, views, etc.  The top of the window reads "DatabaseName - (Disconnected)".

    I can view the tables, views, etc. for any database in Enterprise Manager.  I can also use an MDB to create an ODBC link to the tables.

    I'm sorry if I'm not very clear, and I appreciate your time and patience.  Thank you.
    LVL 44

    Expert Comment

    by:Leigh Purvis
    Ah - so it seems more like a fundamental problem with your ADP.

    Can you create an ADO connection in code to a SQL Server database from an mdb?

    (i.e. might be worth downloading and installing latest MDAC from MS).

    Author Comment

    I know I have the latest MDAC - I'm running Office XP with SP3 installed.

    I Googled your ADO connection in code question and came up with a few sites:;EN-US;q299980&ID=KB;EN-US;q299980&SD=MSDN

    both of which contain some marvelous gobbledy-gook.  I'm trying to decipher them now.  I'll post back either when I've figured it out (hopefully on my own, so I learn something) or when I've given up hope and am tired of talking to the walls.

    Author Comment

    All right.  The first link was a bust; I'm not doing ASP.

    The second link had a bunch of sample code, all of which I can follow logically.  My question is where to enter the code.  Do I enter it into the VB Editor?  If so, what do I start and stop the code with (Option Compare Database?)?.  All the bits of code I tried

    ("strConnection = _T("Driver={Microsoft Access Driver (*.mdb)};"
            "Dbq=c:\\DatabasePath\\dbaccess.mdb;Uid=;Pwd=;");" - appropriate variables changed and

    "strConnect = _T("Provider=sqloledb;Data Source=MyServerName;"
            "Initial Catalog=MyDatabaseName;"
            "User Id=MyUsername;Password=MyPassword;");" - appropriate variables changed)

    produced syntax errors, which I'm sure are due to my ignorance.

    So I guess I don't know how to create an ADO connection in code to a SQL Server database from an MDB.

    Something new happened when I last opened the original ADP file.  It asked for my username and password.  I supplied them, and the database opened, but it was still disconnected.
    LVL 44

    Accepted Solution

    Have you tried a re-install of Access?
    (If the problem is fundamentally at the ADP side of things - it might be what's needed)

    Some standard ADO connection stuff for you...

    Function fADOExample()
        Dim cnn As New ADODB.Connection
        Dim rst As New ADODB.Recordset
        'To open using the ODBC provider - not as fast as OLEDB
        'cnn.Open "ODBC;DSN=YourDSNName"
        'OLEDB connection - what your ADP uses
        cnn.Open "Provider=SQLOLEDB.1;Data Source=YourServerName;User ID=YourUN;Password=YourPW;Initial Catalog=Northwind;"
        Debug.Print "Your connection status: " & IIf(cnn.State <> adStateClosed, "Connected", "Disconnected")

        With rst
            Set .ActiveConnection = cnn
            .CursorType = adOpenKeyset
            .CursorLocation = adUseClient
            .LockType = adLockOptimistic
            .Open "SELECT * FROM YourTableName"
            Debug.Print "Total records: " & .RecordCount

        End With
    End Function

    Author Comment

    I never even thought of a reinstall simply because nothing else was acting up whatsoever.

    However, as previously stated, I am an idiot.

    And you, sir, are a genius.

    LVL 44

    Expert Comment

    by:Leigh Purvis
    Glad you're sorted - and hope the ADO code above is a gentle example for your future use. :-)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
    Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
    In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
    In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now