Connecting to SQL Server 7

[John Graham]

Hi,
I've just upgraded an Access 97 db to SQL Server 7. The db is on a network drive and I need to allow multi user access to it using a VB front end. My question is how do I connect to a SQL Server 7 db using VB. The code I am using in VB at the moment to connect is:

Public cLocation As String
Public dbsOpen As Database

'
Public Sub dbOpenDB(cLocation)
'
Set dbsOpen = OpenDatabase(cLocation)
'
End Sub

I'm then creating recordsets as follows:

cSQL = "SELECT * FROM UserPasswords WHERE UserName='" & cmbUsers.Text & "'"

Set rEncrypt = dbsOpen.OpenRecordset(cSQL)

any help or advice anyone can give will be much appreciated

thanks

john

[crispin]

You need to move to ADO now it's a big boys toy, heres some code:

'IN A FORM
Public WithEvents DBConn As ADODB.Connection

'need a command button here
Private Sub Command2_Click()
Set DBConn = New ADODB.Connection
Dim X As New cConnect
Dim lRet&
X.DBName = "GMI_2000"
lRet = X.Connect(DBConn)
End Sub

Private Sub DBConn_ConnectComplete(ByVal pError As ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pConnection As ADODB.Connection)
MsgBox "Connected OK"
End Sub

'IN A CLASS CALLED cConnect

' Private data storage for the Type property.
Private msDBName As String


Public Property Get DBName() As String
'CSW22/05/01 property used in VB to get the database name for code reuse
   DBName = msDBName
End Property

Public Property Let DBName(DBName As String)
'CSW22/05/01 property used in VB to set the database name for code reuse
      msDBName = DBName
End Property

Public Function Connect(DBConn As ADODB.Connection) As Long
'CSW22/05/01function to execute a standard stored procedure to insert errors into the access databases
    On Error GoTo errConnect
    Dim DBComm As New ADODB.Command
    Dim pFName As ADODB.Parameter
    Dim pErrNum As ADODB.Parameter
    Dim pErrTime As ADODB.Parameter
    Dim pUName As ADODB.Parameter
    Dim pRet As ADODB.Parameter
    'CSW22/05/01set up the connection to connect to the appropriate database (only used in vb)
    With DBConn
      .ConnectionString = "Provider=SQLOLEDB;Data Source=SVRNAME;Initial Catalog=" & DBName & "; User ID=sa;"
      .CursorLocation = adUseServer
      'you can change this to support other feature like disconnected recordsets
      'maybe put it into a property
    End With
    DBConn.Open
    Connect = 0
    Exit Function
errConnect:
    Dim ADOerror As ADODB.Error
    For Each ADOerror In DBConn.Errors
        MsgBox "Error Number " & ADOerror.Number & " occured, description is : " & ADOerror.Description
    Next ADOerror
    Connect = -1
End Function

[John Graham]

Thanks a lot Crispin, can I still use my recordset process as I did with Access, ie create recordsets from SQL statements???

thanks again


john

[nullus]

Indeed

Dim rs As ADODB.Recordset

Private Sub Command1_Click()
  Set rs = New ADODB.Recordset
  Dim strSQL As String

  strSQL = "SELECT Field1, Field2 FROM Table1"
  rs.Open strSQL, DBConn, adOpenStatic, adLockOptimistic

  Do Until (rs.EOF)
    MsgBox rs!Field1 & " " & rs!Field2
    rs.MoveNext
  Loop
End Sub

[John Graham]

wicked nullus thanks a lot, I was dreading serious code changes but this is not too bad.

cheers


john