Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Problem Querying MS Access DB & Active Directory

Posted on 2004-09-23
8
Medium Priority
?
310 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
Technology Partners: 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 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

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

Question has a verified solution.

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

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…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
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…
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…
Suggested Courses

916 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