MySQL vs. Sharepoint

[perlfan]

MySQL vs. SQL Azure

Hi there - I have a small Access application that I sell and I'd like to give my customers also the opportunity to work on their data using an online database. MySQL works ok, but I have never solved the "Server has gone away" issue (including timeout adjustments etc.) meaning the connection regularly gets lost when the user is doing nothing. Now I am looking for a stable solution that I could recommend to my customers. Do you guys have made some experiences with SQL Azurehttp://www.microsoft.com/en-us/showcase/details.aspx?uuid=09aa4f10-333a-4c98-aed1-4cb300de63ec? Is it recommendable as an online database which could also be accessed from Access Runtime?

Your advice would be much appreciated!!! Thanks in advance - FRANK

 

[perlfan]

...if someone could recommend a stable database solution for my Access client application in general, it would be much appreaciated, too. As I said - MySQL is great because it's free, but it is rather unstable considering the fact that the connection often gets interrupted.
Thanks for advice - FRANK

 

[RainLover]

MySQL is stable. Millions upon Millions of Databases use it.

I would suggest that solving your Time out issues would be a better way to go.

Suggest you email your Host as they most likely have the fix you need.

One would think that it not too difficult to fix for those who are MySQL savy.

 

[perlfan]

I am not saying that MySQL is unstable in general. But as online database for my Access client application is has been very unstable with constant time out problems and many people exerience this issue with no solution in sight. As I said - it is recommended to change server timeouts etc. which I did and it didn't help. I also tried a DSN less connection for establishing the ODBC connection with auto-reconnect etc. - nothing really helped.

So my question is rather if someone made good experiences (stable, non interrupting connections) with MS SQL or other formats.

Thanks - FRANK

 

[RainLover]

I may not have chosen my words correctly.

I meant to point out that MySQL is one of the most popular Database Engines.

My advise is to find a solution to your problems. If you change, it may not be for the better and may in fact introduce different problems.

I use MySQL and have never experienced the problems you are having.

 

[spikepl]

@RainLover

MySQL as "online database", i.e. over the WAN and not LAN? With pass-through or with Access doing all the heavy lifting locally? I am asking because I am always interested in how to run Access over WAN (as the OP apparently intends, according to #1)

 

[RainLover]

Spike

Do you have a question there. I think I missed something.

 

[spikepl]

Yeah. OP asks for "online database". You have plenty experience with MySQL. My question: over WAN or LAN?

Update: meaning access FE with MySQL BE over WAN?

 

[RainLover]

MySQL can be used on a Local machine insted of JET.

In a Local Area for in house use.

But it is used mostly for web design using PHP. Both are Open Source and very Popular.

I have used it locally for development before uploading to a Hoste Site so that it is accessable to anyone who wishes to use. Via thw WWW.

I am far from an expert on MySQL but I have built a Database with near on 200 Tables and I don't know how many Fields.

Here is a link if interested.

http://restaurants.vudara.com.au/nsw/kingscliff/kathmandu-kitchen

 

[perlfan]

I am connecting via MySQL Connector 5.1. The database is hosted on a server where I cannot change server settings, however, I already had my database on a virtual server where I could change wait timeouts etc. via shell and nothing helped.
I also have started different forum threads (http://www.access-programmers.co.uk/forums/showthread.php?t=216777; http://www.access-programmers.co.uk/forums/showthread.php?t=217574) which didn't result in a solution though.
BTW: below you'll see my code for building up the connection and linking the tables. It all workes great apart from the loosing connection issue which happens with dsn and without dsn. When I use the database offline (within LAN with XAMPP) the connection does not get interrupted which is not the setting I want though, because users should be able to access from everywhere they are.

FRANK

Public Function StartFunction()

Dim strSQL As String
Dim var As Double
Dim sales_month As Double
Dim sDateTemp As String
Dim intDays As Integer
Dim sTemp As String
Dim sValue As String
Dim dateTemp As Date
Dim tdf As TableDef


Dim Conn  As ADODB.Connection
 Dim db As Database, i As Integer
 Dim mytable As TableDef
 Dim RSTSchema As New ADODB.Recordset
 Dim cat As New ADOX.Catalog
 Dim Constr As String
 Set Conn = New ADODB.Connection
 Set db = CurrentDb

Set db = CurrentDb            
strSQL = "SELECT * FROM [Connection Details]" 

Set rs = db.OpenRecordset(strSQL)  ' Recordset öffnen
Server = rs![Database Server]
user = rs!user
Datenbank = rs![Database Name]
pw = rs!password
pw = VerEntschlüsseln(pw, user)
option_db = 4194304
DSN1 = rs!DSN

'On Error GoTo conn_error

If IsNull(DNS1) Or DSN1 = "" Then

Constr = "Driver=MySQL ODBC 5.1 Driver;" & _
          "Server=" & Server & ";User = " & user & ";Password = " & pw & ";Option= " & option_db & ";Database=" & Datenbank
Else

Constr = "DSN=" & DSN1 & ";"

End If

 Conn.Provider = "MSDASQL"
 Conn.ConnectionTimeout = 28880
 Conn.Mode = adModeRead
 Conn.Open Constr
 Conn.CursorLocation = adUseClient
 

    Set RSTSchema = Conn.OpenSchema(adSchemaCatalogs)
      cat.ActiveConnection = Conn
       For i = 0 To cat.Tables.Count - 1
         Set mytable = db.CreateTableDef(cat.Tables(i).Name)
         mytable.SourceTableName = cat.Tables(i).Name
         mytable.connect = "ODBC;" & Constr & ";Option=3"
         db.TableDefs.Append mytable
          db.TableDefs.Refresh
       Next i

status = Conn.State

DoCmd.OpenForm "Login", , , , , acDialog

Set Conn = Nothing: Set db = Nothing

End Function

 

[spikepl]

@RainLover

Online db in general is one thing. Running Access over WAN another.

Do you have experience using Access FE with MySQL BE over WAN?

I am not asking about any other applications of MySQL but that very specific one, and I have difficulty in determining whether your answer is yes or no

 

[perlfan]

Access front-end with MySQL back-end over WAN - exactly (Access 2007 via ODBC 5.1; MySQL-Client-Version: 5.0.51a). I don't know if that answers your question completely. If you need more info for the diagnosis let me know!

 

[spikepl]

@Perlfan

I have no clue, that's why I am fishing for info - to learn. If all the data is handled locally (eg queries, and not with pass-through) then all the data gets dragged across the network to be processed, and I imagine that Access is not very good at that, but I am looking for verification, since I am not sure.

 

[RainLover]

Spike

I finally understand your question.

NO, I have never run MySQL over a WAN. This is why I suggested that the OP contact the Host.

Also the main point in the original question was about the quality of MySQL which I did try to address.

Sorry if I confused anyone.

 

[spikepl]

OK - let's see if someone with this very specific knowledge on the subject chimes in.

 

[spikepl]

There is some interesting discussion on this subject here: http://www.dbforums.com/microsoft-access/1003214-loading-access-db-over-slow-wan.html

Googling MS Access over WAN gives many other interesting articles, although perhaps not so helpful for OP.

 

[perlfan]

True - there are plenty of forum discussions on MySQL with Access as front-end (a quite good one: http://www.utteraccess.com/forum/lofiversion/index.php/t1939485.html). I have never seen a real solution though where people agreed on a method which would ensure a stable ODBC connection to the MySQL db over WAN.

 

[perlfan]

I know that I am not the most patient person. However, with this forum thread I also wanted to see if there is someone with an online database solution that makes use of MS Access as front-end over WAN which has no connection problems (i.d. timeouts; Server has gone away-problems). Of course, I'd prefer a solution for my already established MySQL-Access application which could help to have a stable, non ending connection, but if there is another way out there, I'd appreciate any advice. THX - FRANK