Working with SQL

Posted on 1998-09-14
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.
Question by:cybernide

Expert Comment

ID: 1434679
Do you wanr to use ODBC DAO or connect true  an OCX to your SQL server?

Accepted Solution

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")
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

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
add text to end of existing text in file 16 70
Excel object stays open 19 76
Visual Studio 2005 text editor 10 38
How to make an ADE file by code? 11 85
Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
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…
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

773 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