在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屋!