sql server + vb 6.0

Question

我已经在vb6.0中创建了一个数据库项目。我已经在sql server 2000中创建了我的数据库。我使用adodc和odbc来连接数据库。

我想把我的sql server保存在一个系统中，而我的vb6.0应用程序保存在另一个系统中，并且我想从网络上访问sql数据库。

请帮帮我。谢谢。

Answer 1

编写的客户端应用程序是这样的:一个VB6程序运行在桌面系统上，SQL Server运行在另一个系统上，通常运行在Windows Server操作系统上。

只要您的VB6程序中有对Microsoft ADO的引用，并且正确设置了连接字符串，就不会有问题。

如果这不能将您引向正确的方向，请在您的问题中添加更多细节。

Answer 2

下面是我用来通过ODBC DSN连接访问MS SQL数据库的类cDatabase：

Option Explicit

Private m_eDBCursorType As ADODB.CursorTypeEnum  'Cursor (Dynamic, Forward Only, Keyset, Static)
Private m_eDBLockType As ADODB.LockTypeEnum    'Locks (BatchOptimistic,Optimistic,Pessimistic, Read Only)
Private m_eDBOptions As ADODB.CommandTypeEnum 'DB Options
Private m_sDSNName As String
Private m_sSQLUserID As String
Private m_sSQLPassword As String
Private cn As ADODB.Connection

Private Sub Class_Initialize()
    m_eDBCursorType = adOpenForwardOnly
    m_eDBLockType = adLockReadOnly
    m_eDBOptions = adCmdText
End Sub

Private Function ConnectionString() As String

    ConnectionString = "DSN=" & m_sDSNName & "" & _
                            ";UID=" & m_sSQLUserID & _
                            ";PWD=" & m_sSQLPassword & ";"

End Function

Private Sub GetCN()
On Error GoTo GetCN_Error

If cn.State = 0 Then
StartCN:
    Set cn = New ADODB.Connection
    cn.Open ConnectionString

    With cn
        .CommandTimeout = 0
        .CursorLocation = adUseClient
    End With
End If


On Error GoTo 0
Exit Sub

GetCN_Error:
If Err.Number = 91 Then
    Resume StartCN
Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetCN of Module modDatabaseConnections"
End If

End Sub

Public Function GetRS(sSQL As String) As ADODB.Recordset

Dim eRS As ADODB.Recordset

On Error GoTo GetRS_Error

TryAgain:

If Len(Trim(sSQL)) > 0 Then
    Call GetCN

    Set eRS = New ADODB.Recordset       'Creates record set
    eRS.Open sSQL, cn, m_eDBCursorType, m_eDBLockType, m_eDBOptions
    Set GetRS = eRS

Else
    MsgBox "You have to submit a SQL String"
End If

On Error GoTo 0
Exit Function

GetRS_Error:
If Err.Number = 91 Then
    Call GetCN
    GoTo TryAgain
ElseIf Err.Number = -2147217900 Then
    Exit Function
Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetRS of Module" & vbCrLf & vbCrLf & "SQL - " & sSQL
End If

End Function

Public Property Get DBOptions() As ADODB.CommandTypeEnum

    DBOptions = m_eDBOptions

End Property

Public Property Let DBOptions(ByVal eDBOptions As ADODB.CommandTypeEnum)

    m_eDBOptions = eDBOptions

End Property

Public Property Get DBCursorType() As ADODB.CursorTypeEnum

    DBCursorType = m_eDBCursorType

End Property

Public Property Let DBCursorType(ByVal eDBCursorType As ADODB.CursorTypeEnum)

    m_eDBCursorType = eDBCursorType

End Property

Public Property Get DBLockType() As ADODB.LockTypeEnum

    DBLockType = m_eDBLockType

End Property

Public Property Let DBLockType(ByVal eDBLockType As ADODB.LockTypeEnum)

    m_eDBLockType = eDBLockType

End Property

Public Property Get DSNName() As String

    DSNName = m_sDSNName

End Property

Public Property Let DSNName(ByVal sDSNName As String)

    m_sDSNName = sDSNName

End Property

Public Property Get SQLUserID() As String

    SQLUserID = m_sSQLUserID

End Property

Public Property Let SQLUserID(ByVal sSQLUserID As String)

    m_sSQLUserID = sSQLUserID

End Property

Public Property Get SQLPassword() As String

    SQLPassword = m_sSQLPassword

End Property

Public Property Let SQLPassword(ByVal sSQLPassword As String)

    m_sSQLPassword = sSQLPassword

End Property

下面是我如何实例化和调用它的：

Private Sub Command1_Click()
Dim rs As ADODB.Recordset
Dim DB As cDatabase
Dim l As Long

Set rs = New ADODB.Recordset
Set DB = New cDatabase

    With DB
        .DBCursorType = adOpenForwardOnly
        .DBLockType = adLockReadOnly
        .DBOptions = adCmdText
        .DSNName = "Your_DSN_Name"
        .SQLUserID = "Your_SQL_Login_Name"
        .SQLPassword = "Your_SQL_Login_Password"
        Set rs = .GetRS("Select Field1 FROM Table1")
    End With

    If rs.RecordCount <= 0 Then Goto Exit_Sub

    For l = 1 To rs.RecordCount
        Debug.Print rs(0).Value
        rs.MoveNext
    Next l

Exit_Sub:
rs.Close
Set rs = Nothing
Set DB = Nothing
End Sub