• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 365
  • Last Modified:

Cannot Reconnect MS Access 2002 Project

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?

Thanks!
0
rjander77
Asked:
rjander77
  • 5
  • 5
1 Solution
 
Leigh PurvisDatabase DeveloperCommented:
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.
0
 
rjander77Author Commented:
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.
0
 
Leigh PurvisDatabase DeveloperCommented:
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?
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
rjander77Author Commented:
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.
0
 
Leigh PurvisDatabase DeveloperCommented:
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).
0
 
rjander77Author Commented:
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:

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q299980&ID=KB;EN-US;q299980&SD=MSDN
and
http://www.codeproject.com/database/connectionstrings.asp#Access,

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.
0
 
rjander77Author Commented:
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.
0
 
Leigh PurvisDatabase DeveloperCommented:
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

        .Close
    End With
   
    cnn.Close
   
End Function
0
 
rjander77Author Commented:
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.

Thanks!
0
 
Leigh PurvisDatabase DeveloperCommented:
Glad you're sorted - and hope the ADO code above is a gentle example for your future use. :-)
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now