在ADO中连接和查询时遇到的问题
我正在制作一个.MDB文件,其中包括一个ms访问数据库和一个使用vb 6创建的表单。我使用ms access 2000,并且需要连接到MDB中的本地数据库,和一个远程MS SQL 2005数据库。在ADO中连接和查询时遇到的问题
在下面的代码中,我可以使用msgbox来显示结果集中的返回值,但是当尝试在文本框中输出时,例如:txtStatus.Value = txtStatus.Value & rstRecordSet.Fields(1) & vbCrLf
,它只是挂起。从教程的原始示例中显示的方法得到了Debug.Print方法,但事实证明,我没有看到任何内容。我的意思是,VB没有控制台面板,打印语句将在哪里进行?
与遇到错误代码:
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,在您的标题一个笑话。
Hereyoucan找东西。 这个site将帮助您处理不同数据库的连接字符串。
访问和使用ADO的sql服务器之间的区别正是连接字符串。 我建议你避免使用远程数据控制,因为开始时会让你的生活变得更简单,但是你必须与他们抗争,因为他们不能正常工作。
这是连接的实例和数据的获取:
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
如何检查错误?我知道有错误,因为它跳转到我的错误处理部分,并说错误,但我不知道它是什么。 – lamwaiman1988 2011-03-16 09:44:04
我已经在我的问题中发布我的代码。我试图连接到我的MDB文件中的数据库,并进行查询,但不知何故失败。请帮忙。 – lamwaiman1988 2011-03-16 09:50:59
您是否参考过“Activex数据对象2.x”? – LeftyX 2011-03-16 10:13:58
你可以列出的错误,并线是发生哪些? – JeffO 2011-03-16 15:18:43