Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

ADO stuff

Posted on 2003-02-20
11
Medium Priority
?
338 Views
Last Modified: 2013-11-23
Hi,

In Word I try to get a recordset from an Access database. I previously used DAO, but am trying to make the app future proof (and speed things up a bit).

Can anyone point out why the following code always returns a recordset of 0. The SQl statements that are passed to the subs are correct, don't question that, it's tested.

I have a sub in which I define a var rs as adodb.recordset.
The functions outlined below are started by the command:
set rs = GetRecordSet(sql,SDbase)
where sql is the sqlstring and Dbase is the access database name and path.

Could anyone help?
Kind regards

========== start code =========
Private mobjConnection As ADODB.Connection

Function GetRecordSet(sql As String, sDBase As String) As ADODB.Recordset
On Error GoTo Handler
   
    Dim objCommand As ADODB.Command
    Dim objRecordset As ADODB.Recordset
 
    If fConnect("open", sDBase) = True Then
   
        Set objCommand = CreateObject("Adodb.Command")
        Set objRecordset = CreateObject("Adodb.Recordset")
       
        With objCommand
            .ActiveConnection = mobjConnection
            .CommandType = adCmdText
            .CommandText = sql
            .Execute
        End With
       
        With objRecordset
            .CursorLocation = adUseClient
            .LockType = adLockOptimistic
            .Open objCommand
        End With
        objRecordset.ActiveConnection = Nothing
        Set GetRecordSet = objRecordset
       
    Else
        MsgBox "Cannot get a recordset. Connection failed.", vbExclamation + vbOKOnly, "Can't get recordset"
    End If
   
Proc_Exit:

    Set objRecordset = Nothing
    Set objCommand = Nothing
   
    fConnect "close"
   
    Exit Function
   
Handler:
    sError Err.Number, Err.Description, "GetRecordset"
    Resume Proc_Exit

End Function

Private Function fConnect(strAction As String, Optional sDBase As String) As Boolean
    Dim sConn$
    On Error GoTo Error_Handler
   
    sConn = "Provider=Microsoft.Jet.OLEDB.4.0" _
             & ";Data Source=" & sDBase _
             & ";Persist Security Info=False"
    Select Case LCase(strAction)
        Case "open"
            If sDBase = "" Then
                Exit Function
            End If
            If Dir(sDBase) <> "" Then
                Set mobjConnection = CreateObject("Adodb.Connection")
                mobjConnection.ConnectionString = sConn '"File Name=" & sDBase
                mobjConnection.Open
                fConnect = True
            Else
                MsgBox "Kan '" & sDBase & "' niet vinden.", vbExclamation + vbOKOnly, "Kan dus connectie niet leggen"
                fConnect = False
            End If
           
        Case "close"
            If Not mobjConnection Is Nothing Then
                mobjConnection.Close
                Set mobjConnection = Nothing
            End If
           
        Case Else
            fConnect = False
            MsgBox "Geen valide actie.", vbExclamation + vbOKOnly, "Alleen open of close is toegestaan"
           
    End Select
   
Proc_Exit:
    Exit Function
   
Error_Handler:
    fConnect = False
    If Err.Number = 3704 Then   'Operation is not allowed when the object is closed.
        Resume Proc_Exit
    Else
        sError Err.Number, Err.Description, "fConnect"
        Resume Proc_Exit
    End If
End Function

Private Sub sError(dblErrNumb As Double, strErrDesc As String, Optional strProcedure As String = "")
   
    On Error GoTo Error_Handler
   
    If strProcedure = "" Then
        MsgBox strErrDesc, vbExclamation + vbOKOnly, mcstModName & ": errornumber " & dblErrNumb
    Else
        MsgBox "Error in procedure '" & strProcedure & "'. " & vbCrLf _
                & strErrDesc, vbExclamation + vbOKOnly, mcstModName & ": errornumber " & dblErrNumb
    End If
   
Proc_Exit:
    Exit Sub
   
Error_Handler:
    MsgBox Err.Description, vbExclamation + vbOKOnly, mcstModName & ": errornumber " & Err.Number
    Resume Proc_Exit
   
End Sub


========== end code =========
0
Comment
Question by:ehout
[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
  • 4
  • 2
  • 2
  • +3
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 7986492
One small problem:

       With objCommand
           .ActiveConnection = mobjConnection
           .CommandType = adCmdText
           .CommandText = sql
           .Execute  <-- this runs the stored procedure (remove this line)
       End With
       
       With objRecordset
           .CursorLocation = adUseClient
           .LockType = adLockOptimistic
           .Open objCommand   <-- this runs the stored procedure AGAIN !!!!
       End With


Now, my question is:
>>code always returns a recordset of 0

Do you mean
* actually, the recordset contains 0 records
* the recordcount is -1

Cheers

0
 
LVL 11

Expert Comment

by:rdrunner
ID: 7986619
      With objCommand
           .ActiveConnection = mobjConnection
           .CommandType = adCmdText
           .CommandText = sql
           .Execute
       End With
       
       With objRecordset
           .CursorLocation = adUseClient
           .LockType = adLockOptimistic
           .Open objCommand
       End With

Here is your flaw i think...

Your code will execute the SQL 2 times....

Also you might not have that type of RS that you think you have , since it will inherit some properties from the command (or connection) when you stick the command into the RS
0
 
LVL 11

Expert Comment

by:rdrunner
ID: 7986629
Ohh ... Hello Angellll ;)

*note to self ... dont let questions remain answered but not press submit*

0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Expert Comment

by:earlnoli
ID: 7986725

1) ******** INTIAL PROTOCOLS FIRST **************

First of all, it is not recommended that you put the
database path in your code.  In other words, trash your
"SDbase" variable.

You must register the database in the "DATA SOURCES (ODBC)"
You can find this in the control panel.  In windows 2000,
its under "Control Panel", "Administrative Tools"

A) WHY THIS IS BETTER?
   1) if you change the path of the database, you dont have to change your code, just change it in the CONTROL PANEL - DATA SOURCES

   2) if you decide to transfer your Access DB to SQL Server with the same tables and fields, just the same solution in #1


B) HOW TO DO THIS

a. Now open the "DATA SOURCES (ODBC)
b. Under "User DSN" tab, press "Add"
c. Find your appropriate driver, in your case Microsoft Access Driver (*.mdb)
d. Under "Data Source Name" = place a name for your DB, for the sake of example... let's use "TESTDB"
e. Then press "Select..." button and find your database.
Press ok, now you have your database registered in windows
and you can now access it via its name "TESTDB" in your
program.


2) ******** THE CODE **********

'STARTS WITH Sub "MAIN"
'It includes both DSN Based And File Based Connections - if you really want to connect via path and filename of the database

Sub Main()
On Error GoTo errHandler

    Dim Rs As New ADODB.Recordset
    Dim sql As String
    Dim DBName As String
    Dim errMsg As String
     
     
    'File Name - Based - YOUR CURRENT OPTION
    DBName = "c:\TEST DATABASE.MDB"
     
    'Data Source Name -Based - BETTER WAY
    DBName = "TESTDB"

    sql = "SELECT * FROM Tbl_Company"
   
    'GETS THE RECORDSET (1st Parameter)
    errMsg = GetRecordSet(Rs, sql, DBName)
   
    'FORCES TO DISPLAY Error Message
    If errMsg <> "" Then Err.Raise vbObjectError, , errMsg
   
    Do While Rs.EOF = False
        '<PUT CODE HERE - GETTING VALUES FROM FIELDS>
        Debug.Print Rs.Fields("CompanyName")
        Rs.MoveNext
    Loop

   
GoTo exitProc
errHandler:
    MsgBox Err.Description
exitProc:
    Set Rs = Nothing
End Sub

Function GetRecordSet( _
    ByRef Rs As ADODB.Recordset, _
    ByVal sql As String, _
    ByVal sDBNAME As String) _
    As String
   
    On Error GoTo errHandler

    'NOTE :
    ' Returns Errormessage instead rather than the RecordSet
    ' Recordset is a byref Parameter instead. Better coding style in my opinion

    Dim Cnn As New ADODB.Connection
    Dim ConnectionString As String

   
    'YOUR CURRENT OPTION
    'File Based - If sDBNAME is the name and path of the database
    ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & sDBNAME
   
    'BETTER WAY
    'Data Source Name - Based
    ConnectionString = "DSN=" & sDBNAME

   
    Cnn.Open ConnectionString
    Set Rs = Cnn.Execute(sql)

GoTo exitProc
errHandler:
    GetRecordSet = Err.Description
exitProc:
    Set Cnn = Nothing
End Function
0
 
LVL 5

Expert Comment

by:bob_online
ID: 7986919
Couple of things:  
1)  I disagree with earlnoli about the odbc stuff -- Microsoft has dropped support of odbc and you really don't want to use it anymore.  I agree, however, that you shouldn't hard code the database path.  Use an ini file, the windows registry, or a relative path based on app.path.

2) I think all you really need to change in your code is

Remove this:
      With objCommand
          .ActiveConnection = mobjConnection
          .CommandType = adCmdText
          .CommandText = sql
          .Execute
      End With

Replace this:      
      With objRecordset
          .CursorLocation = adUseClient
          .LockType = adLockOptimistic
          .Open objCommand
      End With
with this:
      With objRecordset
          .ActiveConnection = mobjConnection
          .CursorLocation = adUseClient
          .LockType = adLockOptimistic
          .Open sql
      End With
0
 
LVL 26

Accepted Solution

by:
EDDYKT earned 400 total points
ID: 7986926
I still want to see your sql string


May be you have to replace * to % in your sql string
0
 
LVL 3

Author Comment

by:ehout
ID: 7988791
Hi,

Thanx for the comments so far. I'm home now, but when I'm back on work tomorrow I'll certainly try things out.

To clarify the situation a bit:
The code above resides among a large number of other code (for several puposes) in he main Word template of our company. In this template several functions are added to Word 97 for correspondece and other things. The code above is meant to be able to put an address in the letter from the database. The data comes from our Mainframe and is loaded into an access database daily. Not a very nice solution, but it suits the needs at the moment since it's only a subset of the large DB on the mainframe. A user invokes a search window and can fill in a (part of a)number, a (part of a) name or both. A click on the search button then generates the proper SQl string, connects to the database and returns the records that fit the search criteria. These records than populate a listbox where the user can pick one to insert in the document. This generated SQL strings work with DAO and also work when entered into Access directly (in the querybuilder). So I can't imagine tha part should e the problem since the SQL string generation hasn't changed.

(EDDYK, when I'm back tomorrow I'll paste a generated SQl string for you)

About the ODBC stuff: When using the control panel to save some database settings, I'l become too dependent on the user's configuration. I would not be able to easily move the DB or connect to a different one. In the original Code The SDBase variable is filled with a value I suck out of an ini file, as I do with most settings that are needed in my code. Reason is that the Word template is always locked (have about 450 users), will the ini is not.

I'll try the above mentioned suggestions and get back to you tomorrow (it's now 8:25 PM here).

Kind regards
0
 
LVL 3

Author Comment

by:ehout
ID: 7992080
EDDYK,

As promised an example of the sql string,

Again, works on DAO, not on ADO.


select * from qryselAgentWord where adres not like '"*' AND woonpl not like '"*' AND postcode not like '"*' AND AGNR like '1*'

Bob_online, Tried your solution, but this didn't work out.
Still get recordcount = 0

Kind regards,
0
 

Expert Comment

by:earlnoli
ID: 7996967
Hi... i just have a few suggestions...

1) CURSOR TYPE
If the sql is ok... then the problem maybe lies on the cursor type...

Dim TargetRecordset as New ADODB.Recordset
Dim Cnn as New ADODB.Connection

Cnn.open <ConnectionString - Whatever>
sql = <SQL - WHATEVER>

TargetRecordset.Open sql, Cnn, adOpenKeyset

Because by default, when you use the OPEN method in a recordset, it only returns forward-only recordsets, you cant use Recordcount

2) SQL

i dont really understand what you want to accomplish with your query...

try this...

sql = "select * from qryselAgentWord where" & _
        " adres not like '%'" & _
        " AND woonpl not like '%'" & _
        " AND postcode not like '%'" & _
        " AND AGNR like '1%'"

i dont understand why you have dangling quotes, i just removed them and i replace ur wildcards (*) into (%), its the


0
 
LVL 3

Author Comment

by:ehout
ID: 7999591
Hi again,

To earlnoli: About the sql,  Don't know why it's done this way either. But it's been functioning for some years now, and I got the code this way, working. So I dodn't change it, thinking that the one how wrote it might had something in mind when writing it this way.

However since I need to change to ADO, I do need to reconsider the code (man you don't want to know what I've been changing in the past few weeks, pages of code replaced by a couple of lines. At the moment he module is almost half of it's original size with all functionality still intact). But this ADO thing is new to me.

i'm going to try the cursortype and I'll change the wildcards signs. But if this latter is te solution, I have to give credits to EDDYK since he mentioned it earlier.

Thanx,
Kind reards.
0
 
LVL 3

Author Comment

by:ehout
ID: 8006793
Hi,

Thanx to all who participated, but this time the simplest solution did the trick.

An example of a working sql string is:
select * from qryselAgentWord where adres not like '"%' AND woonpl not like '"%' AND postcode not like '"%' AND AGNR like '1%'

This produces a list of all our agents with a number starting with 1 and all other fields empty.

Thanx for the help, I learned a lot.

Kind regards.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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…
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…
Suggested Courses

722 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