• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 204
  • Last Modified:

Working with SQL

How can I, without using controls, connect to an sql database and fetch data from there? I'm using vb enterprise edition version 5.0.
Please insert source code.
1 Solution
Do you wanr to use ODBC DAO or connect true  an OCX to your SQL server?
First, you need to have the SQL CLient software installed on your machine.  This is available on the SQL Server install cd, and when installed on your amchine will install the ODBC drivers you need to use to communicate to SQL Server.

Then, you need to create a DSN using your 32bit ODBC driver manager software, icon found in your control panel to set up a DSN that points to your SQL Server and your database on the server.

With the install of the SQL CLient software, you will also get the enterprise manager, which will be the primary utility front end to SQL Server for you.

With the client software and ODBC Drivers installed, and a dsn created, and the appropriate security set up for your client logon on the SQL Server Database,you can procede to access your data using RDO as FOllows:

'Declare DataSources
Dim gEn                    As rdoEnvironment   'rdoEngine
Dim gCnTgt               As rdoConnection    'Target Database
Dim gSql as string
Dim Rs as RdoResultset
Dim Nxt as long

'Open a connection to your dsn
Set gCnTgt = gEn.OpenConnection(vbNullString, rdDriverPrompt, False)  ' this will present aprompt for your user to select the correct DSN

'create sql to get something
gSql ="select nxtnum from ctlnum where filnam = 'MyFile'"

'open the resultset
Set rs = gCnTgt.OpenResultset(gSql, rdOpenForwardOnly, rdConcurReadOnly)  'Will get your data per the SQL

if not rs.eof
 nxt=rs(0)  'gets first column of resultset in nxt, you can also say
 'nxt=rs!nxtnum or nxt=rs("nxtnum")
set gcntgt=nothing


'Now real code will have on error and your own version of an error handler.  I usually encapsulate the reads, and have a common error handler, like this:

Public Sub rsTgt(rs As rdoResultset)
On Error GoTo eRsTgt
Set rs = gCnTgt.OpenResultset(gSql, rdOpenForwardOnly, rdConcurReadOnly)
gSql = vbNullString
glReads = glReads + 1
Exit Sub
End Sub

'where I use a public gSql String, and call it using RsTgt(rs), where rs is an RdoResultSet.  Then I log all errors using an error file on disk (this is for a very long running migration file):

Public Function LogErr()
Dim x$, prt$, fi As Integer, i As Integer
Static iErrs As Long
Static idup As Long
    If rdoErrors.Count > 1 And Len(gSql) > 0 Then
        For i = 1 To rdoErrors.Count - 1
            x$ = rdoErrors(i).Description
            'trim the snot out of it so we just get readable messages
            While InStr(x$, "]") > 0  'gets rid of sql server header
             x$ = Mid$(x$, InStr(x$, "]") + 1)
            While InStr(x$, ":") > 0  'gets rid of constraint name header
             x$ = Mid$(x$, InStr(x$, ":") + 1)
            prt$ = vbCrLf & LTrim$(x$) & vbCrLf
        'look, who cares about dupes anyway, so dump them as errors
        If InStr(UCase$(prt$), "INSERT DUPLICATE") > 0 Then
         gCmnRec(gCntr).Dups = gCmnRec(gCntr).Dups + 1
         LogErr = 0
         Exit Function
        End If
        If prt$ = gLstErr$ Then
         prt$ = " " & gSql
         FMigWiz.txError = Format$(iErrs) + ": " + gLstErr$ + prt$
         If idup > 0 Then
          Print #fi, "(" + Format$(idup + 1) + ") " + gLstErr$
         End If
         idup = 0
         gLstErr$ = prt$
         prt$ = prt$ & " " & gSql
         FMigWiz.txError = Format$(iErrs) + ": " + prt$
         ' Display message box
         'MsgBox (prt$)
        End If
     prt$ = Err.Description & vbCrLf
    End If
    fi = FreeFile
    Open ErrLog$ For Append As fi
    Print #fi, prt$
    iErrs = iErrs + 1
    'Determin Nature and Severity of Error
    LogErr = 0
    ' Comm Link, SQL Failure, or Invalid Object
    If InStr(prt$, "08S01") > 0 Or InStr(prt$, "S1000") > 0 Or InStr(UCase$(prt$), "OBJECT IS INVALID") > 0 Then
     prt$ = prt$ + vbCrLf + " Critical Error, Process Stopped!"
     Print #fi, prt$
     MsgBox prt$
    End If
    Close #fi

End Function
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now