Solved

Problem Querying MS Access DB & Active Directory

Posted on 2004-09-23
8
303 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
[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
  • 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 200 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 29

Accepted Solution

by:
leonstryker earned 300 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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

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…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
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…
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…

752 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