Hi
I will soon have a excel sheet distributed between hundreds of users.
When the excel is first opened, it gets connected to a mySQL database (and stays connected until excel application is closed)
People then do queries, etc using the following code.
' connects to database when workbook is opened (only once)
Set oConn = New ADODB.Connection
oConn.Open connection_string
etc
The following code (part of only) is run to do queries
...
With rsPubs
.ActiveConnection = oConn
.Open sql_Query_String ' sql_Query_String is a SQL query
Range("A10").CopyFromRecordset rsPubs
.Close
End With
...
What I need to know is if, once an user has stopped searching/querying the database, should I use
oConn.Close
(and connect again "when needed") or is it better to leave the connection opened at all times while Excel application is running?
Users normally will have the excel opened for a period of 30-60 minutes, although they will not be querying the mySQL database continuously.
I have done some testings but only with 5 users at once. Everything runs perfect however I am concerned once there are +50 users connected, I will start having performance/connection problems.
Has anyone had any experience with this type of set up ?
thanks for your comments
I will soon have a excel sheet distributed between hundreds of users.
When the excel is first opened, it gets connected to a mySQL database (and stays connected until excel application is closed)
People then do queries, etc using the following code.
' connects to database when workbook is opened (only once)
Set oConn = New ADODB.Connection
oConn.Open connection_string
etc
The following code (part of only) is run to do queries
...
With rsPubs
.ActiveConnection = oConn
.Open sql_Query_String ' sql_Query_String is a SQL query
Range("A10").CopyFromRecordset rsPubs
.Close
End With
...
What I need to know is if, once an user has stopped searching/querying the database, should I use
oConn.Close
(and connect again "when needed") or is it better to leave the connection opened at all times while Excel application is running?
Users normally will have the excel opened for a period of 30-60 minutes, although they will not be querying the mySQL database continuously.
I have done some testings but only with 5 users at once. Everything runs perfect however I am concerned once there are +50 users connected, I will start having performance/connection problems.
Has anyone had any experience with this type of set up ?
thanks for your comments
