"Enable automatic reconnect" possible with VBA? (1 Viewer)

perlfan · Oct 28, 2011

Hi,

I had the problem that Access lost the connection to my MySQL db after a certain time period. Previously I connected via

Code:

Constr = "DRIVER={MySQL ODBC 3.51 Driver};Server=" & Server & _  ";port=" & Port & ";user=" & user & ";Password=" & pw & ";Database=" & Datenbank"

Now I connect via DNS set up in the windows control panel. There I checked the box "enable automatic reconnect". With the box checked the connection gets not broken anymore which is great. Now I would like to implement "enable automatic reconnect" in VBA so users of my application do not have to set up a DNS in Windows. Is that possible? Thanks for help! FRANK

Galaxiom · Oct 28, 2011

This confusion must be the most common one in the industry. DNS is Domain Name Server. DSN is Data Source Name.

Test the connection before you try to use it.

ADODB connection assuming an ADO reference is set and you have set the connection properties.

Code:

If conn.State = adStateClosed Then conn.Open

perlfan · Oct 28, 2011

Thank you for your answer.
The if part seems reasonable, but I cannot constantly check, if the connection is lost, or can I? How can I trigger an event when the connection is lost? I need some help to implement this - sorry!
Thank you - FRANK

Galaxiom · Oct 28, 2011

You check when you need the connection.

I have not used MySQL so I don't know much about it timing out.

However generally speaking, with ADO it is actually a good practice to explicitly close a connection when you don't need it. Timing out connections is not efficient use of the connection pool and will result in users not being able to get in if it is a large system.

One of the big advantages of ADO is the abilty to run with a disconnected recordset. It saves server resources. So you download the recordset containing only the records you need then disconnect. Then you reconnect to update or add records.

If you are not using the features in ADO you might as well use DAO recordsets.

perlfan · Oct 28, 2011

It isn't really what I'm looking for, but thank you anyways. I thought I could do the same what I can do in the control panel (checking the enable automatic reconnect box) also in VBA. That would be the most convenient solution for me. THANK YOU!

Banana · Oct 28, 2011

You need to pass in the Opt argument in the connection string. It's listed here. Notice at the bottom they've also given you the recommended value to pass in when using Access as well.

Also, if the timeout is due to the MySQL being hosted and they're making timeout much shorter, then consider passing in an initial passthrough query as part of your startup to set the timeout accordingly.

perlfan · Oct 29, 2011

This is what I was looking for - I tried to implement the option into my connection string, but it didn't work so far. Can somebody tell me, if the string is correct like this? Option=4194304 is supposed to switch the auto-reconnect on.

Code:

 Constr = "DRIVER={MySQL ODBC 5.1 Driver};Server=" & Server & _
 ";port=" & Port & ";user=" & user & ";Password=" & pw & ";Database=" & Datenbank & "; Option=4194304;"

perlfan · Oct 31, 2011

...can anyone help me with the correct implementation of the auto-reconnect flag in the connection string? The one I have built (Constr = "DRIVER={MySQL ODBC 5.1 Driver};Server=" & Server & _ ";port=" & Port & ";user=" & user & ";Password=" & pw & ";Database=" & Datenbank & "; Option=4194304;") doesn't work. Thanks a lot for help!! FRANK

Banana · Nov 4, 2011

Maybe it's actually OK but there's other issues going on. I already linked the similar issue where a hosted MySQL has too short a timeout set that breaks the connection and the workaround. Maybe if you gave more details as to why you think the option will resolve problem, we can help.

"Enable automatic reconnect" possible with VBA? (1 Viewer)

perlfan

Registered User.

Galaxiom

Super Moderator

perlfan

Registered User.

Galaxiom

Super Moderator

perlfan

Registered User.

Banana

split with a cherry atop.

perlfan

Registered User.

perlfan

Registered User.

Banana

split with a cherry atop.

Similar threads

Users who are viewing this thread