Solved

Problem Querying MS Access DB & Active Directory

Posted on 2004-09-23
8
298 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
Comment Utility
At which point are you getting the error?

Leon
0
 

Author Comment

by:eciabattari
Comment Utility
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
Comment Utility
>>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
 
LVL 29

Accepted Solution

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

Try this

Set Con = New ADODB.Connection

Leon
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 

Author Comment

by:eciabattari
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I ended up redoing the entire AD search, found some information while searching "experts-exchange".  
Thanks for the help
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

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…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

772 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now