Sql Server + Vb 6.0

I have created one database project in vb 6.0. I have created my database in sql server 2000. I am using adodc and odbc to connect to database. I want to keep my sql server in

Solution 1:

This is how many client applications are written: A VB6 program running on a desktop system, and the SQL Server running on another system, usually on a Windows Server OS.

As long as you have a reference in your VB6 program to Microsoft ADO, and you set up your connection string properly, there shouldn't be an issue.

Please add more detail to your question if this doesn't point you in the right direction.

Solution 2:

Here is the Class, cDatabase, I use to use to access a MS SQL database using an ODBC DSN Connection:

OptionExplicitPrivate 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 OptionsPrivate m_sDSNName AsStringPrivate m_sSQLUserID AsStringPrivate m_sSQLPassword AsStringPrivate cn As ADODB.Connection

PrivateSub Class_Initialize()
    m_eDBCursorType = adOpenForwardOnly
    m_eDBLockType = adLockReadOnly
    m_eDBOptions = adCmdText
EndSubPrivateFunction ConnectionString() AsString

    ConnectionString = "DSN=" & m_sDSNName & "" & _
                            ";UID=" & m_sSQLUserID & _
                            ";PWD=" & m_sSQLPassword & ";"EndFunctionPrivateSub GetCN()
OnErrorGoTo GetCN_Error

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

    With cn
        .CommandTimeout = 0
        .CursorLocation = adUseClient
    EndWithEndIfOnErrorGoTo0ExitSubGetCN_Error:If Err.Number = 91ThenResume StartCN
Else
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetCN of Module modDatabaseConnections"EndIfEndSubPublicFunction GetRS(sSQL AsString) As ADODB.Recordset

Dim eRS As ADODB.Recordset

OnErrorGoTo GetRS_Error

TryAgain:If Len(Trim(sSQL)) > 0ThenCall 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"EndIfOnErrorGoTo0ExitFunctionGetRS_Error:If Err.Number = 91ThenCall GetCN
    GoTo TryAgain
ElseIf Err.Number = -2147217900ThenExitFunctionElse
    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure GetRS of Module" & vbCrLf & vbCrLf & "SQL - " & sSQL
EndIfEndFunctionPublicPropertyGet DBOptions() As ADODB.CommandTypeEnum

    DBOptions = m_eDBOptions

EndPropertyPublicPropertyLet DBOptions(ByVal eDBOptions As ADODB.CommandTypeEnum)

    m_eDBOptions = eDBOptions

EndPropertyPublicPropertyGet DBCursorType() As ADODB.CursorTypeEnum

    DBCursorType = m_eDBCursorType

EndPropertyPublicPropertyLet DBCursorType(ByVal eDBCursorType As ADODB.CursorTypeEnum)

    m_eDBCursorType = eDBCursorType

EndPropertyPublicPropertyGet DBLockType() As ADODB.LockTypeEnum

    DBLockType = m_eDBLockType

EndPropertyPublicPropertyLet DBLockType(ByVal eDBLockType As ADODB.LockTypeEnum)

    m_eDBLockType = eDBLockType

EndPropertyPublicPropertyGet DSNName() AsString

    DSNName = m_sDSNName

EndPropertyPublicPropertyLet DSNName(ByVal sDSNName AsString)

    m_sDSNName = sDSNName

EndPropertyPublicPropertyGet SQLUserID() AsString

    SQLUserID = m_sSQLUserID

EndPropertyPublicPropertyLet SQLUserID(ByVal sSQLUserID AsString)

    m_sSQLUserID = sSQLUserID

EndPropertyPublicPropertyGet SQLPassword() AsString

    SQLPassword = m_sSQLPassword

EndPropertyPublicPropertyLet SQLPassword(ByVal sSQLPassword AsString)

    m_sSQLPassword = sSQLPassword

EndProperty

and here is how I instantiated and called it:

PrivateSub Command1_Click()
Dim rs As ADODB.Recordset
Dim DB As cDatabase
Dim l AsLongSet 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")
    EndWithIf rs.RecordCount <= 0ThenGoto Exit_Sub

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

Exit_Sub:
rs.Close
Set rs = NothingSet DB = NothingEndSub