Transfer data from mysql to access using PHP & VB

Visual Basic Topics
Post Reply
User avatar
Rksk
Major
Major
Posts: 730
Joined: Thu Jan 07, 2010 4:19 pm
Location: Rathnapura, Sri Lanka

Transfer data from mysql to access using PHP & VB

Post by Rksk » Tue Oct 11, 2011 11:12 pm

I have wanted to tranfer some some data from a Mysql database (On a remote server) to a MS Access database (on local mechine) interfacing PHP & VB (VB6).

I did it using below mwthod, but I think it isn't a good solution.

1. Select data from mysql and print data using php as below,

Code: Select all

    $user = '1';
    $q=mysql_query("SELECT id,time,user_id,text FROM posts WHERE user_id = '$user' ORDER BY id");

    while ($post = mysql_fetch_array($q))
    {
      echo $post['id'].",".$post['time'].",".$post['user_id'].",".$post['text']."||";
    }
2. Read this output using VB, Split data and insert data into the Access database as below,

Code: Select all

    Dim mydb As Database
    Dim myrs As Recordset
    Dim s, s2 As String
    Dim sItems() As String
    Dim sItems2() As String

    Set mydb = OpenDatabase("data.mdb")
    Set myrs = mydb.OpenRecordset("posts", dbOpenTable)
    
    s = InternetGetFile("http://mydomain.com/myscript.php")
    sItems() = Split(s, "||")

        Dim X As Integer
        For X = 0 To UBound(sItems)
          sItems2() = Split(sItems(X), ",")
          myrs.AddNew
          myrs.Fields("id") = sItems2(0)
          myrs.Fields("time) = sItems2(1)
          myrs.Fields("user_id") = sItems2(2)
          myrs.Fields("text") = sItems2(3)
          myrs.Update
        Next X


I hope you will have another idea to do this.
Please help me.


Thankz,
Rksk.

[ Post made via Mobile Device ] Image
User avatar
Neo
Site Admin
Site Admin
Posts: 2642
Joined: Wed Jul 15, 2009 2:07 am
Location: Colombo

Re: Transfer data from mysql to access using PHP & VB

Post by Neo » Wed Oct 12, 2011 12:49 pm

I would export the MySQL database to a SQL script file and execute it on the Access database (if there is any SQL incompatibilities, we will have to fix them manually). If this is working you can easily automate this as well.
User avatar
Rksk
Major
Major
Posts: 730
Joined: Thu Jan 07, 2010 4:19 pm
Location: Rathnapura, Sri Lanka

Re: Transfer data from mysql to access using PHP & VB

Post by Rksk » Wed Oct 12, 2011 1:05 pm

How to execute sql scripts in Access?

[ Post made via Mobile Device ] Image
User avatar
Neo
Site Admin
Site Admin
Posts: 2642
Joined: Wed Jul 15, 2009 2:07 am
Location: Colombo

Re: Transfer data from mysql to access using PHP & VB

Post by Neo » Wed Oct 12, 2011 4:46 pm

If you have a single SQL string to execute, then Go to Query Design -> Select SQL view (from Tool bar or right click menu), paste your SQL and click on Run. If you have several SQL commands (like the SQL script file you get when you export a MySQL database), you can write a VBA code to do that within the Access database or write a VB program to execute command by command.
Post Reply

Return to “Visual Basic Programming”