Improve company productivity with a Business Account.Sign Up

x
?
Solved

Problem Querying MS Access DB & Active Directory

Posted on 2004-09-23
8
Medium Priority
?
313 Views
Last Modified: 2012-08-13
Hi,

I'm querying an MS Access DB and one the fields within the DB represents Active Directory CN (strUserNameToAD).

Before I close the connection to the Access DB, I pass that value (strUserNameToAD) to an Active Directory search; however, I continue to get “Invalid use of Null” with the code below.

I have run the Active Directory search and it works but not in conjunction with the MS Access DB.
The Acces and MS Word code work in conjuntion perfectly.

References:
MS Access DB, using "Microsoft DAO 3.6 Object Library"
Directory search, using “Microsoft ActiveX Data Objects 2.7 Library” and “Active DS Type Library”
MS Word, using Microsoft Word 10.0 Object Library

Intentions:
1. MS Access Query
    a.  Query DB.
    b.  For each record within DB
         i.  Pass logon ID to AD query
        ii.  Pass computer ID, etc to Word code
             1.  AD Query
                  a.  Pass logon ID from Access query.
                  b.  Open connection to AD
                  c.  Query AD for First & Last Name
                  d.  Close connection to AD
             2.  Word Code:
                 a.  Open new instance of MS Word
                 b.  Import First & Last Name, computer ID, etc. into Word
     c.  Loop – to next record
     d.  Close connection to Access DB
     e.  Manually save and close Word

'--------------------------------------------------------------------------------------------------------
Public Function ActiveDirectory()

    Dim Con As ADODB.Connection
    Dim ocommand As New ADODB.Command
    Dim gc As IADs

   'surname to be used
    strName = strUserNameToAD
 
    'Create ADO connection object for Active Directory
    Set Con = CreateObject("ADODB.Connection")
    Debug.Print "1"
        If (Err.Number <> 0) Then
            Debug.Print Err.Number & " " & Err.Description
        End If
            Con.Provider = "ADsDSOObject"
        If (Err.Number <> 0) Then
            Debug.Print Err.Number & " " & Err.Description
        End If
            Con.Open "Active Directory Provider"
        If (Err.Number <> 0) Then
            Debug.Print Err.Number & " " & Err.Description
        End If
'--------------------------------------------------------------------------------------------------------
0
Comment
Question by:eciabattari
  • 4
  • 3
8 Comments
 
LVL 29

Expert Comment

by:leonstryker
ID: 12135464
At which point are you getting the error?

Leon
0
 

Author Comment

by:eciabattari
ID: 12135910
Getting error as follows:

 'Create ADO connection object for Active Directory
    Set Con = CreateObject("ADODB.Connection")   <----------- I believe this where the error is being generated.
        If (Err.Number <> 0) Then
            Debug.Print "On CreateObject " & Err.Number & " " & Err.Description   <-------------- i get the following messag and then exit as designed.
            Exit Function
        End If
       
    Con.Provider = "ADsDSOObject"
        If (Err.Number <> 0) Then
            Debug.Print "On Provider " & Err.Number & " " & Err.Description
            Exit Function
        End If
       
    Con.Open "Active Directory Provider"
        If (Err.Number <> 0) Then
            Debug.Print "Open Con " & Err.Number & " " & Err.Description
            Exit Function
        End If
0
 
LVL 26

Assisted Solution

by:EDDYKT
EDDYKT earned 800 total points
ID: 12135919
>>MS Access DB, using "Microsoft DAO 3.6 Object Library"
Directory search, using “Microsoft ActiveX Data Objects 2.7 Library”


Why you use both DAO and ADO. Why not just use ADO?
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 29

Accepted Solution

by:
leonstryker earned 1200 total points
ID: 12136000
>>Set Con = CreateObject("ADODB.Connection")   <----------- I believe this where the error is being generated.

Try this

Set Con = New ADODB.Connection

Leon
0
 

Author Comment

by:eciabattari
ID: 12136175
I've tried and changed:

     Set Con = CreateObject("ADODB.Connection")   <----------- I believe this where the error is being generated.
     To "Set Con = New ADODB.Connection"

---------------------------------------
I've also changed the code for MS Access DB from "Microsoft DAO 3.6 Object Library", to ADO and removed the referece from the Project.

Still getting same error as before.
0
 
LVL 29

Expert Comment

by:leonstryker
ID: 12136242
Please do this:

Set Con = New ADODB.Connection
Debug.Print Err.Description

What is the exact error message that you are getting?

Leon
0
 

Author Comment

by:eciabattari
ID: 12136274
I've done what you've asked, here's the exact error message that I get:

Invalid use of Null
0
 

Author Comment

by:eciabattari
ID: 12136558
I ended up redoing the entire AD search, found some information while searching "experts-exchange".  
Thanks for the help
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
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…
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…

584 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