Solved

Working with SQL

Posted on 1998-09-14
2
165 Views
Last Modified: 2010-04-30
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.
0
Comment
Question by:cybernide
2 Comments
 
LVL 2

Expert Comment

by:dabellei
ID: 1434679
Do you wanr to use ODBC DAO or connect true  an OCX to your SQL server?
0
 
LVL 9

Accepted Solution

by:
cymbolic earned 100 total points
ID: 1434680
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")
endif
rs.close
gcntgt.close
set gcntgt=nothing

end

'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
eRsTgt:
 LogErr
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)
            Wend
            While InStr(x$, ":") > 0  'gets rid of constraint name header
             x$ = Mid$(x$, InStr(x$, ":") + 1)
            Wend
            prt$ = vbCrLf & LTrim$(x$) & vbCrLf
        Next
        '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$
        Else
         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
    Else
     prt$ = Err.Description & vbCrLf
    End If
    fi = FreeFile
    Open ErrLog$ For Append As fi
    Print #fi, prt$
    iErrs = iErrs + 1
    DoEvents
   
    '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$
     CloseDataSources
     End
    End If
    Close #fi

End Function
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

762 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

22 Experts available now in Live!

Get 1:1 Help Now