Solved

Problem Querying MS Access DB & Active Directory

Posted on 2004-09-23
8
302 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 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

There are many ways to remove duplicate entries in an SQL or Access database. Most make you temporarily insert an ID field, make a temp table and copy data back and forth, and/or are slow. Here is an easy way in VB6 using ADO to remove duplicate row…
If you have ever used Microsoft Word then you know that it has a good spell checker and it may have occurred to you that the ability to check spelling might be a nice piece of functionality to add to certain applications of yours. Well the code that…
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…
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…

679 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