Excel 求助，如何使用VBA实现远程访问SQL数据库

VBA

连接

SQL

SERVER

数据库

实例:

Dim

strConn

As

String,

strSQL

As

String

Dim

conn

As

ADODB.Connection

Dim

ds

As

ADODB.Recordset

Dim

col

As

Integer

'连接数据库的字符串

strConn

=

"Provider=SQLOLEDB.1Persist

Security

Info=TrueUser

ID=[user]Password=[password]Initial

Catalog=[database]Data

Source=[数据库IP地址或数据库服务器名称]Connect

Timeout=720

"

'查询语句，如果sql语句很长可以用strSQL=strSQL+来连接分成多段的语句，如果语句很短可以只写在一行上。

strSQL

=

"select

*

from

Hy_KPI_Shop_Dept_WeekRpt

"

strSQL

=

strSQL+"where

sdate='2014-01-01'

order

by

sdate,shopid

"

Set

conn

=

New

ADODB.Connection

Set

ds

=

New

ADODB.Recordset

'打开数据库连接

conn.Open

strConn

'该句和数据库连接字符串处的Connect

Timeout=720，表示说如果语句运行时间很长，这两句可以延长vba的等待时间，没有这两句，vba往往会报查询超时。

conn.CommandTimeout

=

720

With

ds

'根据查询语句获得数据

.Open

strSQL,

conn

'自动控制加入所有列标题

For

col

=

0

To

ds.Fields.Count

-

1

'请注意Offset(0,

col)中的参数一定要正确，该句表示标题将会写在第一行，从A1单元格开始，如果不想写入标题行，可将下面这句注释掉。

Worksheets("门店各课KPI周报").Range("A1").Offset(0,

col).Value

=

ds.Fields(col).Name

Next

'加入所有行数据，该句表示查询结果将会写在第一行，从A1单元格开始，但是由于标题行写在第一行了，所以实际这一行从标题下的一行写入。

Worksheets("sheet1").Range("A1").Offset(1,

0).CopyFromRecordset

ds

End

With

'关闭数据库连接和清空资源

Set

ds

=

Nothing

conn.Close

Set

conn

=

Nothing

一、界面设计

各控件名称属性分别为：label1  、text1 、label2、text2、commandok、cmdcancel

登录成功后显示的窗体：

代码设计如下：

'首先添加一个模块，写上以下通用声明和Sub main()：

Public conn As ADODB.Connection    '通用（声明）

Sub main()

  Set conn = New ADODB.Connection   '通用（main）

   conn.ConnectionString = "Provider=SQLOLEDB.1Persist Security Info=False" _

   + "User ID=sapassword=123Initial Catalog=dengluData Source=127.0.0.1"      '连接数据库代码

  conn.Open

frmLogin.Show     '首先显示登录界面。也可以在工程属性中设置启动对象为Sub main()或者frmlogin窗体

End Sub

'在Frmlogin 代码窗口，为cmdok控件写以下代码：

Private Sub cmdok_Click()

If text1.Text = "" Then

      MsgBox "用户名不能为空！", vbOKOnly + vbInformation, "友情提示"

      text1.SetFocus

      Exit Sub       '若用户名文本框内为空，则出现提示框

  End If

  If text2.Text = "" Then

      MsgBox "密码不能为空！", vbOKOnly + vbInformation, "友情提示"

      text2.SetFocus

      Exit Sub     '若输入密码文本框为空，也出现提示框

  End If

  Dim strSQl As String  

  strSQl = "select * from User1 where username='" &Trim$(text1.Text) &"' and pwd='" &Trim$(text2.Text) &"' "  

  '书写SQL代码，查询User1表中是否存在窗体中用户输入的信息。

  Dim str As New ADODB.Recordset

  Set str = New ADODB.Recordset

  str.CursorLocation = adUseClient

  str.Open strSQl, conn, adOpenStatic, adLockReadOnly

  With str

      If .State = adStateOpen Then .Close

      .Open strSQl

      If .EOF Then

          Try_times = Try_times + 1

          If Try_times >= 3 Then

              MsgBox "您已连续三次输入错误，系统将自动关闭", vbOKOnly + vbCritical, "警告"

              Unload Me            '若用户连续输入3次错误密码，则系统关闭

          Else

              MsgBox "对不起，用户名不存在或密码错误 ！", vbOKOnly + vbQuestion, "警告"

              text1.SetFocus

              text1.Text = ""

              text2.Text = ""

          End If

      Else

       

          Unload Me    '若登录成功，则隐藏当前窗体

       

        Form2.Show    '然后显示Form窗体          

      End If

  End With

End Sub

Private Sub cmdCancel_Click()  

End         '若单击Cmdcel按钮，则结束应用程序

End Sub

运行中存在的问题：

代码中有Dim conn As adodb.connection，运行时显示"用户定义类型未定义"

解决方法：点击“工程”--“引用”找到“Microsoft ActiveX Data Object 2.6”

然后就就可以正常运行了。

1、基本上所有的数据库都有对应的写法（有些数据库是要装连接驱动才能连），比如"Provider=Microsoft.Jet.OLEDB.4.0（用来连接access）或者provider =MSDASQL，而且每种数据库也可能会有多种连接方法（Provider意即数据库引擎），常见的有下面这些

Provider 代码 Provider

ADSDSOObject Active Directory Services

Microsoft.Jet.OLEDB.4.0 Microsoft Jet databases

MSDAIPP.DSO.1 Microsoft Internet Publishing

MSDAORA Oracle databases

MSDAOSP Simple text files

MSDASQL Microsoft OLE DB provider for ODBC

MSDataShape Microsoft Data Shape

MSPersist Locally saved files

SQLOLEDB Microsoft SQL Server

2、参见http://www.w3school.com.cn/ado/met_conn_open.asp#connectoptionenum

3、这样就可以对book1.xlsx中的数据使用sql语法进行 *** 作

4、当你需要返回结果的，比如说你要查询数据库符合某个条件的有几条记录，或者说取出数据库中符合条件的一批记录的时候就要用数据集，单一的conn.execute只能执行 *** 作，不能返回数据，必须配合结果集使用才可取回数据。

---