access stored procedure works in Access but not it Vb.net Winform

I have a stored Procedure in MS Access and it works in MS Access but when I call that Stored Procedure from a vb.net win form it returns
nothing in terms of Data. IN Access it looks like this
SELECT *
FROM [Global]
WHERE (((Global.LDAP) Like '*OU=NAZ*'));

The stored Procedure is called NAZUsers and data is from a LDAP Query that pulls every user in the Active Directory. I wanted to narrow data down to one OU (Organizational Unit) so that's what the Like statement is for

In vb.net a have a class

 Public Function GetNazPeople() As DataTable
        '  OleDbConnection.ReleaseObjectPool()
        Dim con As New OleDbConnection(connectionString)
        Dim cmd As New OleDbCommand("NAZUsers", con)
        cmd.CommandType = CommandType.StoredProcedure
        Dim adapter As New OleDbDataAdapter(cmd)

        Dim ds As New DataSet()
        adapter.Fill(ds, "NAZUsers")



        ' Set up a relation between these tables.
        Return ds.Tables("NAZUsers")
        con.Close()
    End Function

In the Win form
the call is  DataGridView1.DataSource = Program.StoreDB.GetNazPeople
I'm using now  Dim connectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                     "Data Source=NazScheduler.mdb;" instead of accdb extension for Access 2010.

It's just this Stored Procedure that doesn't work called from VB.NET Win form My other stored Proceures
work outside of Access.
Tom PowersAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PatHartmanCommented:
This may be a semantics issue but ".mdb" files do not support stored procedures.  You need to be using an .accdb to have an ACE stored procedure.  So if it really is an .accdb and you really do have a stored procedure, you need to change the provider to be ACE rather than Jet to access an .accdb.
0
Tom PowersAuthor Commented:
I changed Connection to  Dim connectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\CHEUSERS1.accdb;Persist Security Info=False;" I get CHEUSERS is not a Stored Procedure
the NazUsers I refered to earlier I recreated it in new .accdb format as CHEUsers but the syntax has not changed
StillSELECT *
FROM CHEUSERS
WHERE (((CHEUSERS.LDAP) Like '*OU=NAZ*'));
here is error message screen shot
Debug.png
0
Tom PowersAuthor Commented:
Please Help.
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Tom PowersAuthor Commented:
Still same issue when I create a sql string instead of Stored Procedure.
 Dim SQLString As String = "SELECT * FROM CHEUSERS WHERE LDAP Like '*OU=NAZ*'"

        Dim OleDBConn1 As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\CHEUSERS1.accdb;Persist Security Info=False;")
        Dim DataSet1 As New DataSet()
        Dim OleDbDataAdapter1 As System.Data.OleDb.OleDbDataAdapter = New System.Data.OleDb.OleDbDataAdapter(SQLString, OleDBConn1)
        OleDBConn1.Open()

        OleDbDataAdapter1.Fill(DataSet1, "NAZUsers")
        DataGridView1.DataSource = DataSet1.Tables("NazUsers")

        OleDBConn1.Close()

Open in new window

0
Tom PowersAuthor Commented:
I figured it out Here is code I wrote
 Private Sub btnviewNAZ_Click(sender As Object, e As EventArgs) Handles btnviewNAZ.Click
        ' On Error Resume Next
        DataGridView1.DataSource = Nothing
        DataGridView1.ClearSelection()
        Dim ConnetionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\CHEUSERS1.accdb;Persist Security Info=False"

        ' sql = "Select PlayerID,FirstName,LastName,TeamID,Jersey,Position,Status,Height,Weight,Born,College,"
        ' sql &= "BioHTML, HighSchool FROM NFLPLAYERS WHERE Status ='A' AND Position<> ''"
        ' SqlConnectionStr
        Sql = "SELECT * FROM CHEUsers"
        Sql = Sql & " WHERE " & " " & "LDAP like '%" & _
                "OU=NAZ" & "%'"



        connection = New OleDbConnection(ConnetionString)
        Dim ds As New DataSet
        Try
            connection.Open()
            Dim adapter = New OleDbDataAdapter(Sql, connection)
            adapter.Fill(ds)
            connection.Close()
            DataGridView1.DataSource = ds.Tables(0)

        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try

        ' ds.Tables(Currentrow) = "Lastname" = txtLname.Text
        ' ds.Tables(Currentrow).Column("College") = txtCollege.Text

        ' txtLname.Text = ds.Tables(Currentrow).TableName("Lastname")
        ' txtCollege.Text = ds.Tables(Currentrow).Item("College")

        Me.DataGridView1.RowsDefaultCellStyle.BackColor = Color.Bisque
        Me.DataGridView1.AlternatingRowsDefaultCellStyle.BackColor = Color.Beige
        DataGridView1.ColumnHeadersHeight = 55
        DataGridView1.ColumnHeadersHeightSizeMode = DataGridViewColumnHeadersHeightSizeMode.AutoSize
        ' Add the image column to the grid.
        ' dgvinjury.Columns.Add(imageCol)
    End Sub

Open in new window


Close THIS QUESTION
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Tom PowersAuthor Commented:
The guy with the Questions Solves his problem.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.