AccessVB versus VBS

[james_keegan via AccessMonster.com]

Hey Folks:
I'm trying to get a table in Access populated with user-ID's, names, e-mail
phone and other data from the Active Directory via an ldap-query.

I got a VBS script to get to the AD tables, and return the appropriate
recordset (with some help from 'The Scripting Guy' over at MSDN).
***
Const ADS_SCOPE_SUBTREE = 2

Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection

objCommand.Properties("Page Size") = 1000
objCommand.Properties("Searchscope") = 2

objCommand.CommandText = "SELECT sAMAccountName, givenName, sn,
telephonenumber, title, department, physicaldeliveryofficename, mail FROM
'LDAP://HSEN/OU=Tompkins County DSS (A50), OU=Counties,OU=All Users and
Computers,DC=HSEN' WHERE objectCategory='user' "

Set objRecordSet = objCommand.Execute
***
But I can't figure out how to do the same thing from within an Access VB
module.

My attempt based on stuff I've seen here fails almost immediately:
***
Dim objConnection As Object
Dim objRecordset As Object
Dim objCommand As Object
objConnection = CreateObject("ADODB.Connection")
***
Fails at the "adodb.connection" line with an "Invalid outside procedure"
message.

I've added the 'Microsoft Activex Data Objects 2.8 Library" from the tools-
resources menu, so that wasn't it.

How can I acomplish what I want to do here? Or should I be using the VBS
script to populate the Access table? How do I reference the Access table
from the VBS script if that's the right way to go?

I would prefer to do this all within Access if that's possible, so that
whenever the database was opened, the table would refresh from the lasted AD
data. Otherwise, I'd have to write a batch file and call the vbscript, and
then call the access database.

Any clues would be most appreciated!

Thanks.

jk

 

[David W. Fenton]

objConnection = CreateObject("ADODB.Connection")
***
Fails at the "adodb.connection" line with an "Invalid outside
procedure" message.

You need to SET it:

Set objConnection = CreateObject("ADODB.Connection")

 

[DAVID]

Just to be clear, you need to do that (using SET)
inside a procedure:

Function MyFunc()
Set objConnection = CreateObject("ADODB.Connection")
End function

It is common to put the object declarations inside the
function as well, to limit scope and visibility:

Function MyFunc()
Dim objConnection
Dim objRecordset
Dim objCommand
Set objConnection = CreateObject("ADODB.Connection")
Set objCommand = CreateObject("ADODB.Command")
objConnection.Provider = "ADsDSOObject"
objConnection.Open "Active Directory Provider"
Set objCommand.ActiveConnection = objConnection
End Function

(david)

 

[james_keegan via AccessMonster.com]

THANKS so much - The problem was that I was not inside of a sub or a function!
D'OH!

The procedure is working fine now, getting the loginID, names, title, phone
numbers and other data about each user and dumping it to a table.

Now for some fine-tuning. There are some user-objects that I DON'T want in
the list. I can filter these out after I populate the table with a simple
"select * from tblStaff where title is not null;" query, but when I try to
add that to the command line so that the whole mess looks like:

***objcommand.CommandText = "SELECT SamAccountName, givenname, sn, title,
department, physicaldeliveryofficename, telephonenumber, mail FROM 'LDAP://
[domain]/OU=[localOU],OU=[localContainer],OU=All Users and Computers,DC=
[domain]' WHERE objectCategory='user' and title Is Not Null"*** (potentially
identifying names munged with [generic] syntax)

it barfs at me.

How can I filter out the 'title is null' userobjects in this type of Select
statement?

Or am I stuck with grabbing everyone, and filtering at the local level?

Thanks again for your help. This site is GREAT! I wish I had more time to
give back. I try to answer the questions I can when I get a chance!

jk

 

[DAVID]

That provider uses a restricted SQL, may not be able
to do much (check the documentation). It may not have
the concept of null, or may not be able to do
restrictions at all.

You may also be able to do filters on the recordset,
before you stuff it into a table.

Try Using () on the where clauses: 'user') and (title

(david)

 

[james_keegan via AccessMonster.com]

I tried the paren's, but that didn't help.

I've set up a query to filter those title=null records out, and I'll just use
that. The less than ten hits records I don't need is not going to
significantly impact performance!

Thanks for your help.

jk

That provider uses a restricted SQL, may not be able
to do much (check the documentation). It may not have
the concept of null, or may not be able to do
restrictions at all.

You may also be able to do filters on the recordset,
before you stuff it into a table.

Try Using () on the where clauses: 'user') and (title

(david)

THANKS so much - The problem was that I was not inside of a sub or a function!
D'OH!

[quoted text clipped - 24 lines]

jk