وبلاگ

در این آموزش چگونه انجام بدهم نحوه اتصال VBA به پایگاه داده MYSQL را با استفاده از اتصال ADODB و درایور ODBC یاد می گیریم.

نصب درایور

برای اتصال VBA به MYSQL باید درایور ODBC مربوط به MYSQL را دریافت و نصب کرده باشید. این درایور را می توانید از لینک زیر دریافت نمایید.

MYSQL ODBC Connector

بررسی اطلاعات درایور نصب شده

بعد از نصب درایور می توانید اطلاعات درایور نصب شده را در ویندوز مشاهده نمایید. این اطلاعات در مراحل بعدی برای اتصال VBA به MYSQL موردنیاز می باشد.

نحوه اتصال VBA به MYSQL

مرحله اول: اتصال به پایگاه داده

برای اتصال VBA به MYSQL باید از کلاس Connection کتابخانه ADODB استفاده نماییم. بنابراین باید یک متغیر شیء از نوع ADODB.Connection اعلان نماییم.

Dim mysql_conn As ADODB.Connection

ADODB.Connection یک شیء می باشد و باید قبل از هر کاری یک نمونه جدید از این شیء ایجاد نماییم.

Set mysql_conn = New ADODB.Connection

ConnectionString یکی از خواص شیء ADODB.Connection می باشد که رشته ای است که نحوه اتصال به دیتابیسی که می خواهید به آن متصل شوید را مشخص می نماید.

قالب رشته ConnectionString برای پایگاه داده MYSQL بصورت زیر می باشد.

DRIVER={driverName};Server=serveAddress;UID=mysqluser;PWD=mysqlpass;DB=dbName;PORT=serverPort

بخش های مختلف رشته ConnectionString در زیر مشخص شده است:

• driverName : نام درایور ODBC پایگاه داده MYSQL است که در بخش بررسی اطلاعات درایور همین آموزش نحوه پیدا کردن آن را توضیح دادیم.
• serveAddress : آدرس سروری است که MYSQL روی آن نصب شده است.
• mysqluser : نام کاربری اتصال به پایگاه داده MYSQL
• mysqlpass : رمز عبور اتصال به پایگاه داده MYSQL (اگر رمزی تعیین نکرده اید می توانید خالی بگذارید)
• dbName : نام پایگاه داده ای بر روی سرور MYSQL که می خواهید به آن متصل شوید.
• serverPort : پورت اتصال به سرور MYSQL

با توجه به توضیحات برای اتصال به MYSQL از کد زیر استفاده می نماییم.

Set mysql_conn = New ADODB.Connection
With mysql_conn
    .ConnectionString = "DRIVER={MySQL ODBC 8.0 Unicode Driver};Server=localhost;" & _
                        "UID=root;PWD=;DB=iranvba_test;PORT=3306"
    .Open
End With

با استفاده از کد بالا اتصال VBA به MYSQL کامل شده است. Open یکی از توابع (متدها) کلاس Connection است که رشته ConnectionString را اجرا می نماید و به پایگاه داده مورد نظر متصل می شود.

مرحله دوم: دریافت اطلاعات MYSQL در VBA

در مرحله اول به MYSQL متصل شدیم. در این مرحله می خواهیم ببینیم چطوری باید اطلاعات جداول MYSQL را در VBA دریافت کنیم. برای دریافت اطلاعات MYSQL در VBA باید یک Recordset ایجاد کنیم.

برای اتصال VBA به MYSQL باید از کلاس Connection کتابخانه ADODB استفاده کردیم. برای دریافت اطلاعات از MYSQL در VBA باید از کلاس Recordset کتابخانه ADODB استفاده نماییم.

ابتدا باید یک متغیر از نوع شیء ADODB.Recordset برای ذخیره Recordset ایجاد نماییم.

Dim rs As ADODB.Recordset

حالا باید یک نمونه جدید از شیء ایجاد می نماییم.

Set rs = New ADODB.Recordset

کد نهایی برای ایجاد پرس و جو (query) و دریافت اطلاعات از MYSQL به صورت زیر می باشد.

Dim mysql_conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set mysql_conn = New ADODB.Connection
With mysql_conn
    .ConnectionString = "DRIVER={MySQL ODBC 8.0 Unicode Driver};Server=localhost;" & _
                        "UID=root;PWD=;DB=iranvba_test;PORT=3306"
    .Open
End With

Set rs = New ADODB.Recordset
With rs
    .Source = "SELECT `lastName`, `firstName` FROM `employees`"
    .ActiveConnection = mysql_conn
    .CursorLocation = adUseClient
    .CursorType = adOpenDynamic
    .LockType = adLockOptimistic
    .Open
End With

در کد بالا خواص Source و ActiveConnection شیء ADODB.Recordset به ترتیب رشته حاوی دستور SQL و اتصالی که می خواهید با استفاده از آن پرس و جو را اجرا نمایید می باشند. در نهایت متد open رشته حاوی پرس و جو را بر روی اتصال فعال که شیء mysql_conn می باشد اجرا می نماید.

برای آزمایش اینکه آیا کدها بدرستی کار می کنند یا خیر، می توانید با استفاده از حلقه زیر اطلاعات را در پنجره Immediate چاپ نمایید.

With rs
    If .RecordCount <> 0 Then
        .MoveFirst
        Do Until .EOF
            Debug.Print !FirstName & " " & !LastName
            .MoveNext
        Loop
    End If
End With

کد نهایی اتصال VBA به MYSQL

برای اینکه بتوانیم کدها را بصورت یک مجموعه کامل اجرا نماییم آنها را باید درون یک روال قرار دهیم. در این آموزش از یک روال Sub (سابروتین) استفاده می نماییم.

Public Sub test_mysql_conn()
Dim mysql_conn As ADODB.Connection
Dim rs As ADODB.Recordset

    Set mysql_conn = New ADODB.Connection
    With mysql_conn
        .ConnectionString = "DRIVER={MySQL ODBC 8.0 Unicode Driver};Server=localhost;" & _
                            "UID=root;PWD=;DB=iranvba_test;PORT=3306"
        .Open
    End With
    
    Set rs = New ADODB.Recordset
    With rs
        .Source = "SELECT `lastName`, `firstName` FROM `employees`"
        .ActiveConnection = mysql_conn
        .CursorLocation = adUseClient
        .CursorType = adOpenDynamic
        .LockType = adLockOptimistic
        .Open
    End With
    With rs
        If .RecordCount <> 0 Then
            .MoveFirst
            Do Until .EOF
                Debug.Print !FirstName & " " & !LastName
                .MoveNext
            Loop
        End If
    End With
End Sub

با استفاده از کد بالا می توانید در اکسل و اکسس براحتی به پایگاه داده مدنظرتان در سرور MYSQL متصل شوید و اطلاعات را از MYSQL به سلول اکسل یا یک جدول اکسس منتقل نمایید.

بیشتر بخوانید:

• روال در VBA | تعریف روال و انواع آن در ویژوال بیسیک
• پنجره Immediate | آشنایی با پنجره آنی ویژوال بیسیک
• حلقه در VBA | ایجاد حلقه برای تکرار دستورات در ویژوال بیسیک
• دستور IF | اجرای مشروط کد با استفاده از عبارت های شرطی و منطقی