ODBC Connection Box Suppression

[ASherbuck]

Currently I'm using the following in access 07

MyConnectionString = "ODBC;Driver={MySql ODBC 3.51 Driver};Server=SERVER;Port=3306;Option=131072;Stmt=;Database=MOTDDB;Uid=USERID;Pwd=PWORD"

Set db = OpenDatabase("", dbDriverNoPrompt, False, MyConnectionString)

and it works fine for what I need it to do - pulling data off a remote mysql server.

A problem arises when a user no longer has an active internet connection or if the server is down. In the help file it states that if the Name portion of the OpenDatabase method is blank (OpenDatabase(""... <-- that part) and the connection type is ODBC then it would open the ODBC dialog box.

Is there a way to turn off or suppress this option? I would prefer to not have a user looking at/able to play around with the mysql connection options. I have tried setting the Name option to the name of the db but I still get the ODBC dialog box when I try to test.

I have been able to band-aid this so far by having users cancel the ODBC dialog box since access just throws an error and I can handle it through error handling, but I would rather the user not have to deal with this part at all.

 

[Banana]

Disclaimer: I've not fooled that much with 2007, though I suspected they improved the connection handling in 2007 as I'm pretty sure in 2003 & earlier if you were disconnected in middle of session, Access barfs up a spew of 'Disk or Network Error' dialogs, cascading all way to the bottom right corner. That kind of error couldn't be even trapped. Fun! This doesn't seems to happen in 2007, at least when I tested it briefly.

Anyway, the thing is that Access caches the connection and that cache is, as far as I can tell, completely inaccessible to the programmer. Here's an example as I can recall:

1) Open Access.
2) Run the connection code to connect a linked table to back-end using DSN-less connection. Do not save user & password.
3) Close the database but not the Access itself.
4) Re-open the database and run a query that would depend on a table from backend. Instead of failing, it succeeds.

An implication of the caching Access does is that if the connection gets broken, you can't reset the connection until you actually quit Access. For that reason, I had guarantee that when the users closed the last form, Access would quit, thus closing that loophole. The spew of dialogs cause the users to give it the good ol' three salute so that worked OK, if quite amateurish manner of handling the lost connection in 2003.

Anyway, I'll take a look at it sometime when I have free time and see what 2007 can do differently and how it can be handled. But the point of my meandering rambling was that if you don't want Access to try to reconnect, you are better off forcing the Access to quit and have the users re-open because there's no way to control that cached connection.

If you're interested, do give the steps I gave a try and see if the behavior remains.