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

ADO stuff

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
ehout
Asked:
ehout
  • 4
  • 2
  • 2
  • +3
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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
 
rdrunnerCommented:
      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
 
rdrunnerCommented:
Ohh ... Hello Angellll ;)

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

0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
earlnoliCommented:

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
 
bob_onlineCommented:
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
 
EDDYKTCommented:
I still want to see your sql string


May be you have to replace * to % in your sql string
0
 
ehoutAuthor Commented:
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
 
ehoutAuthor Commented:
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
 
earlnoliCommented:
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
 
ehoutAuthor Commented:
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
 
ehoutAuthor Commented:
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

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 4
  • 2
  • 2
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now