Cannot Reconnect MS Access 2002 Project

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.

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.

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?

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.

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).

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.

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.

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!

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