Solved

VB statements to connect to SQL Express.

Posted on 2007-03-27
3
868 Views
Last Modified: 2013-12-25
I am currently developing an application using Visual Studio Express and SQL Express. I connected an SQL Express database  to Visual Studio Express via the new connection wizard.  No problem. Now I'm trying to access tables within an SQL Express database using VB.  I can't seem to find the statements to use to do that.

I asked this question before in a different manner and I'm afraid I was misunderstood.

If possible, please share with me the statement(s) I need to insert into my VB code.

Thanks for any help you can provide.

brosner22
0
Comment
Question by:brosner22
3 Comments
 
LVL 11

Accepted Solution

by:
Louis01 earned 500 total points
ID: 18800946
This is a class I use as a Data Access Layer.

 '**********************************[ Component Header ]******************************
'Author:        Louis Bester
'Description:   adoDAL - Ado Data Access Class.
'**********************************[ Component Header ]******************************
Option Explicit

Public Enum QUERYOPTIONS
    QO_UseTable = ADODB.adCmdTable
    QO_UseText = ADODB.adCmdText
    QO_UseStoredProc = ADODB.adCmdStoredProc
    QO_UseUnknown = ADODB.adCmdUnknown
    QO_UseDefault = -1
End Enum

Private Enum LOCKTYPES
    LT_ReadOnly = ADODB.adLockReadOnly
    LT_Optimistic = ADODB.adLockOptimistic
    LT_Pessimistic = ADODB.adLockPessimistic
    LT_BatchOptimistic = ADODB.adLockBatchOptimistic
End Enum

Private Const QUERY_TIMEOUT = 120

Public adoConn As ADODB.Connection
Private bInTrans As Boolean
Private lngErrorNo As Long
Private strErrorDesc As String
Private lngRows As Long

Public Property Get RowsAffected() As Long
        RowsAffected = lngRows
End Property

Public Property Let InTrans(ByVal vData As Boolean)
    bInTrans = vData
End Property

Public Property Get InTrans() As Boolean
        InTrans = bInTrans
End Property

Public Property Let ErrorNo(ByVal vData As Long)
    lngErrorNo = vData
End Property

Public Property Get ErrorNo() As Long
        ErrorNo = lngErrorNo
End Property
Public Property Let ErrorDescription(ByVal vData As String)
    strErrorDesc = vData
End Property

Public Property Get ErrorDescription() As String
        ErrorDescription = strErrorDesc
End Property
Public Property Get DALError() As String
        DALError = lngErrorNo & "" & strErrorDesc
End Property

Public Function OpenConnection(ByVal vstrConnect As String) _
                               As Boolean
On Error GoTo OpenConnection_Error
' Opens the required ADO connection
'
' Inputs:
'    vstrConnect - Connect string for a registered DSN.
' Outputs:
'    Returns TRUE if successful, else FALSe.
   ClearErrors
   OpenConnection = False
   
   'Open the ADO connection.
   If adoConn Is Nothing Then
        Set adoConn = New ADODB.Connection
   End If
   
   If adoConn.State = adStateClosed Then
    adoConn.Open vstrConnect
   End If
   adoConn.CommandTimeout = 10000
   
   OpenConnection = True
Exit Function

OpenConnection_Error:
lngErrorNo = Err.Number
strErrorDesc = Err.Description
End Function


Public Function CloseConnection() As Boolean
On Error GoTo CloseConnection_Error
' Close the ADO connection
'
' Inputs:
' Outputs:
'    Returns TRUE if successful, else FALSe.
   ClearErrors
   CloseConnection = False
   
   If adoConn.State = adStateOpen Then
        adoConn.Close
   End If
   Set adoConn = Nothing
   
   CloseConnection = True
Exit Function

CloseConnection_Error:
lngErrorNo = Err.Number
strErrorDesc = Err.Description
End Function

Public Function ExecuteSQL(ByVal vstrSQL As String) _
                            As Boolean
On Error GoTo ExecuteSQL_Error
' Opens the required ADO connection and executes the sql statement.
'
' Inputs:
'    vstrSQL     - The sql statement to execute.
' Outputs:
'    Returns TRUE if successful, else FALSe.
   ClearErrors
   ExecuteSQL = False

   adoConn.CommandTimeout = 10000
   lngRows = -1
   'Execute the sql statement.
   adoConn.Execute vstrSQL, lngRows
   
   ExecuteSQL = True
   
Exit Function

ExecuteSQL_Error:
lngErrorNo = Err.Number
strErrorDesc = Err.Description
End Function


Public Function Begin() _
                            As Boolean
On Error GoTo Begin_Error
' Signal begin of an ADO transaction
'
' Outputs:
'    Returns TRUE if successful, else FALSe.
   ClearErrors
   Begin = False
   
   If Not bInTrans Then adoConn.BeginTrans
   bInTrans = True
   Begin = True
   
Exit Function

Begin_Error:

lngErrorNo = Err.Number
strErrorDesc = Err.Description

End Function


Public Function Commit() _
                            As Boolean
On Error GoTo Commit_Error
' Signal Commit of an ADO transaction
'
' Outputs:
'    Returns TRUE if successful, else FALSe.
   ClearErrors
   Commit = False
   
   adoConn.CommitTrans
   bInTrans = False
   
   Commit = True
   
Exit Function

Commit_Error:

lngErrorNo = Err.Number
strErrorDesc = Err.Description

End Function

Public Function RollBack() _
                            As Boolean
On Error GoTo RollBack_Error
' Signal RollBack of an ADO transaction
'
' Outputs:
'    Returns TRUE if successful, else FALSE.
   ClearErrors
   RollBack = False
   
   adoConn.RollbackTrans
   bInTrans = False
   RollBack = True
   
Exit Function

RollBack_Error:

lngErrorNo = Err.Number
strErrorDesc = Err.Description

End Function


'Public Function SQLselect(sSQLq As String, Optional vCursorType As Variant, Optional vLockType As Variant) As Recordset
'    ClearError
'    Dim rsSet As ADODB.Recordset
'
'    On Error GoTo SQLselectErr
'
'    Set rsSet = New ADODB.Recordset
'
'    If (IsMissing(vCursorType)) And (IsMissing(vLockType)) Then
'
'        rsSet.Open sSQLq, adoConxn, adOpenForwardOnly, adLockOptimistic
'
'    ElseIf (IsMissing(vCursorType)) And (Not IsMissing(vLockType)) Then
'
'        rsSet.Open sSQLq, adoConxn, adOpenForwardOnly, vLockType
'
'    ElseIf (Not IsMissing(vCursorType)) And (IsMissing(vLockType)) Then
'
'        rsSet.Open sSQLq, adoConxn, vCursorType
'
'    ElseIf (Not IsMissing(vCursorType)) And (Not IsMissing(vLockType)) Then
'
'        rsSet.Open sSQLq, adoConxn, vCursorType, vLockType
'
'    End If
'
'    Set SQLselect = rsSet
'    Set rsSet.ActiveConnection = Nothing
'    Exit Function
'
'SQLselectErr:
'    Call RaiseError(Err.Number, Err.Description)
'
'End Function
'
'Public Function SQLexecute(sSQLq As String) As Long
'    ClearError
'    Dim nRecsAffected As Long
'
'    On Error GoTo SQLexecuteErr
'
'    adoConxn.Execute sSQLq, nRecsAffected
'
'    SQLexecute = nRecsAffected
'
'    Exit Function
'
'SQLexecuteErr:
'    Call RaiseError(Err.Number, Err.Description)
'
'End Function


Public Function GetRecordset(ByVal vstrSQL As String, _
                             rrstRecordset As ADODB.Recordset) _
                             As Boolean
On Error GoTo GetRecordset_Error
' Opens the required ADO connection and executes the sql query
'   returning a disconnected recordset.
'
' Inputs:
'    vstrSQL       - The sql statement (Query) to execute.
'    rrstRecordset - A recordset object to return the recordset in.
' Outputs:
'    Returns TRUE if successful, else FALSe.
   ClearErrors
   'Debug.Print vstrSQL
   
   GetRecordset = False
   
   adoConn.CommandTimeout = 10000
   Set rrstRecordset = New ADODB.Recordset
   Set rrstRecordset.ActiveConnection = adoConn
   With rrstRecordset
      .CursorLocation = adUseClient
      .CursorType = adOpenStatic
      .LockType = LT_BatchOptimistic
      .Open vstrSQL, , , , -1
     
   End With
   lngRows = rrstRecordset.RecordCount
   Set rrstRecordset.ActiveConnection = Nothing
   
   GetRecordset = True
Exit Function

GetRecordset_Error:

lngErrorNo = Err.Number
strErrorDesc = Err.Description

End Function


Private Sub ClearErrors()

lngErrorNo = 0
strErrorDesc = ""

End Sub
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Most everyone who has done any programming in VB6 knows that you can do something in code like Debug.Print MyVar and that when the program runs from the IDE, the value of MyVar will be displayed in the Immediate Window. Less well known is Debug.Asse…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now