ODBC Connection or Custom API?

[Attilio]

Hello to everyone.
I have a vb6 application that uses a Database MS Access in read-only method.

I want to change this database with another, centralized and server side like SQL Server or MySQL.

Actually, into vb6 I use DAO for the connection (I know is an older method and there are better like ADO)

I don't know if is better to create my own API and connect to the data by vb6 or buy a SQL SERVER Cloud (Ex: azure) and connect them by ODBC Connection.
I'm afraid that this connection might be too slow...


What do you advise me to do?

The DB is 5MB of dimension with 500/600 connection every day

[Elroy]

I manage a project that is quite similar to what you describe. For mine, I use the DAO for everything. It has local MDB databases that much is stored to, and there are also WAN databases (MySQL). We get to the WAN databases via linked tables that are hooked up with the MySQL drivers and then the ODBC drivers.

Here's what we've found after about a year of operations (the WAN part ... the LAN part has been in operation for 20+ years).

1) For "download" queries, the ODBC linked tables work absolutely fine.

2) For "uploading" (from our local [LAN] MDB files into the SQL server), the ODBC connections are slow. For direct field-to-field (table-to-table) uploading, it seems to go ok, but we also upload whole files (on the LAN) up to blob fields on the SQL server, and that is painfully slow.

We're currently working on switching much of it over to using ADO and a direct connection (just completely circumventing the ODBC linked tables), but just for the upload part. For the download/query part, people just like what I call the "Visual Query" builder that MS-Access gives you too much to let go of it. (Although, a couple of people are learning how to use the MySqlWorkbench program, which is pretty nice.)

[DataMiser]

You do not want to use ODBC unless there is no other choice.

You can use SQL Server Express which is free or the full-blown SQL Server and use ADO with the native client oledb provider to access the data.

[Attilio]

Where do you store MySQL Database? On Azure? Or on another service?

[Zvoni]

This depends on even more things factoring in (everything the others already said not withstanding)

Let's say you decide to "buy" a Service (Cloud, dedicated provider, whatever).

Usually, connection to "somewhere" via Internet (and it doesn't matter if it's a DB-Server, FTP-Server, whatever),
the Download-Speed is way higher than upload-speed (e.g. my parents have a DL-Speed 4-5 times the UL-speed).

So, a DB-Server within a LAN is not "realistically" comparable to a DB-Server "bought" from a Service in terms of "speed"-performance

I'm working in a company, which has a company-only WAN (basically a LAN via MPLS) with 12 subsidiaries world-wide, with some 400-500 users (many with multiple sessions and/or Software-Clients),
resulting in some 1500-2000 parallel connections daily (!!) to our centralized DB-Server, with no noticable lag in the connection.
No Proxy for load-balancing, no nothing. All sessions directly to the DB-Server.

Something to think about