在ADO中无法连接和查询 [英] Trouble connecting and querying in ADO

问题描述

我正在制作一个.MDB文件，其中包括一个ms访问数据库和一个用vb 6制作的表单.我正在使用ms access 2000，并且需要同时连接到MDB中的本地数据库和远程MS SQL 2005数据库.

I am making a .MDB file which include a ms access database and a form made with vb 6. I am using ms access 2000, and I need to connect to both my local database in the MDB, and a remote MS SQL 2005 database.

在下面的代码中，我可以使用msgbox显示结果集返回的值，但是当尝试在文本框中输出它时，例如:txtStatus.Value = txtStatus.Value & rstRecordSet.Fields(1) & vbCrLf，它只是挂起.在本教程的原始示例中显示的方法得到了Debug.Print的方法，但是事实证明，它没有执行任何我可以看到的操作.我的意思是，VB没有控制台面板，打印语句将转到哪里?

In the below code, I can use a msgbox to display the value return from the result set, but when try to output it in a textBox, e.g: txtStatus.Value = txtStatus.Value & rstRecordSet.Fields(1) & vbCrLf, it just hangs. And the method show in the original example from the tutorial got a method of Debug.Print something, but it turns out didn't do anything which I can see. I mean, VB doesn't have a console panel, where will the print statement goes to?

出现错误的代码:

    Function Testing()
On Error GoTo Error_Handling
   Dim conConnection As New ADODB.Connection
   Dim cmdCommand As New ADODB.Command
   Dim rstRecordSet As New ADODB.Recordset

   conConnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
   App.Path & "\" & CurrentDb.Name & ";"
   conConnection.CursorLocation = adUseClient

   With cmdCommand
    .ActiveConnection = conConnection
    .CommandText = "SELECT * FROM Opt_In_Customer_Record;"
    .CommandType = adCmdText
   End With

   With rstRecordSet
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open cmdCommand
   End With

   If rstRecordSet.EOF = False Then
        rstRecordSet.MoveFirst
        Do
            MsgBox "Record " & rstRecordSet.AbsolutePosition & " " & _
          rstRecordSet.Fields(0).Name & "=" & rstRecordSet.Fields(0) & " " & _
          rstRecordSet.Fields(1).Name & "=" & rstRecordSet.Fields(1)
          rstRecordSet.MoveNext
        Loop Until rstRecordSet.EOF = True
   End If

   conConnection.Close
   Set conConnection = Nothing
   Set cmdCommand = Nothing
   Set rstRecordSet = Nothing

   Exit Function

Error_Handling:
MsgBox "Error during function Testing!"
Exit Function

End Function

推荐答案

我以为一开始是个玩笑；-) 无论如何，就像您的标题一样，我认为您是在谈论ADO.

I thought it was a joke at the beginning ;-) Anyway I assume you're talking about ADO, as in your title.

此处 可以找到东西. 该站点将帮助您使用不同数据库的连接字符串.
访问和使用ADO的sql server之间的区别就是连接字符串. 我建议您避免使用远程数据控制"，因为它一开始会使您的生活变得更简单，但随后您就不得不为它们而苦恼，因为它们无法正常工作.

Here you can find stuff. This site will help you with the connection strings for different database.
The difference between access and sql server using ADO it is exactly the connection string. I would suggest you to avoid Remote Data Controls cause make your life simpler at the beginning but then you have to struggle with them cause they don't work properly.

这是数据连接和获取的示例:

This is an example of connection and fetch of data:

Dim cnn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim strSql As String

cnn.ConnectionString = _
    "Provider=Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source=m:\testdbSource\testSource.mdb;" & _
    "User Id=admin;Password=;"
cnn.Open

cmd.ActiveConnection = cnn
cmd.CommandType = adCmdText
cmd.CommandText = "select * from tblSource"
cmd.Execute

Set cmd = Nothing
cnn.Close
Set cnn = Nothing

此示例对我有用:

Function Testing()

    On Error GoTo Error_Handling

    Dim MyDb As String
    Dim conConnection As New ADODB.Connection
    Dim cmdCommand As New ADODB.Command
    Dim rstRecordSet As New ADODB.Recordset

    MyDb = "db1.mdb"

    With conConnection
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = App.Path & "\" & MyDb
        .Open
    End With


    With cmdCommand
        .ActiveConnection = conConnection
        .CommandText = "SELECT * FROM Opt_In_Customer_Record;"
        .CommandType = adCmdText
    End With

   With rstRecordSet
    .CursorType = adOpenStatic
    .CursorLocation = adUseClient
    .LockType = adLockOptimistic
    .Open cmdCommand
   End With

   Do While Not rstRecordSet.EOF
        MsgBox "Record " & rstRecordSet.AbsolutePosition & " " & _
          rstRecordSet.Fields(0).Name & "=" & rstRecordSet.Fields(0) & " " & _
          rstRecordSet.Fields(1).Name & "=" & rstRecordSet.Fields(1)
          rstRecordSet.MoveNext
   Loop

   conConnection.Close
   Set conConnection = Nothing
   Set cmdCommand = Nothing
   Set rstRecordSet = Nothing

   Exit Function

Error_Handling:
    MsgBox "Error during function Testing!"
    MsgBox Err.Description

End Function

这篇关于在ADO中无法连接和查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！