Solved

Working with SQL

Posted on 1998-09-14
2
168 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
When trying to find the cause of a problem in VBA or VB6 it's often valuable to know what procedures were executed prior to the error. You can use the Call Stack for that but it is often inadequate because it may show procedures you aren't intereste…
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…
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…

726 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