Convert VB DAO/Access to use MySQL via Internet

[lennyh]

I currently have a VB 6 client that uses DAO to talk to a LAN attached Access Database. Assuming I convert my Access db to MySQL, can you tell me the best way to convert my VB app to talk to the new MySQL db? I would like to provide access from the new vb client via the internet rather than a direct LAN connection.

I'm not looking for detailed instructions, here (although of course that would be great), but rather a general approach. Some sample questions I have are:

1. Will ODBCDirect provide the connectivity I'm looking for? If that would work, it seems like that's the easiest conversion.

2. Do I need to convert to ADO? And if so, do I use RDS? I'm confused about the relationship between RDS and ADO and the requirement for one or the other for this application.

3. Also, it seems that most discussions of ADO are in conjunction with Active Server Pages. I don't care about enabling db access via a browser; my interest is using the VB client. So I'm a bit confused that most all the ADO documentation revolves around ASP.

Thanks for any help you can provide.

 

[CajunCenturion]

I would convert to ADO, then you can connect to both the MySQL and Access databases simultaneously, with two different connection objects. You will also need to download MyODBC and install it on the client machine. An ADO connection string to a MySQL Database looks something like the following:

With gADO_OSCConn
.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" & _
&quot;SERVER=<IP Address>;&quot; & _
&quot;DATABASE=<Database Name>;&quot; & _
&quot;UID=<User ID>;&quot; & _
&quot;PWD=<Password>;&quot; & _
&quot;OPTION=&quot; & Trim(lLng_ConnOpt)
.Open
End With

Since this database is on a Linux box, I connect via IP address. You'll need to consult with the MySQL manual for a discussion of the connection options (lLng_ConnOpt) and which ones apply in your situation.

Once you've connected to both databases, you can read from access and write that data to MySQL. Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[lennyh]

CajunCenturion, thank you for your helpful response. I understand that ADO is the preferable solution. Since I already have quite an extensive aplication using DAO, I was wondering if DAO w/ ODBCDirect might be a possible approach requiring less costly redo. Do you happen to know if ODBCDirect supports access to a web-attached MyQL db via MyODBC? And if so, is there any significant downside in going that way other than perhaps a reduction in efficiency or long-tem extensiblity.

Also, on a related note, can you clarify the relationship, if any, between ADO and RDS?

In any case, thanks again for your help.

Lenny

 

[CajunCenturion]

I'm afraid that I'm not going to be much help. I've only used MyODBC and MySQL with ADO in a series of back-office applications written in VB. All of the web-specific apps are developed in PHP. As a result, my understadning of RDS is limited to the conceptual level.
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[lennyh]

I understand. Thanks for your response.