vb 2005 express > querry table in access and put contents into array

15 Aug 2006 2:45 PM

ducky801

Hi all,

I've got some general VB experience, but i'm not sure how to approach
the problem at hand. here is what i've got:

I have a database that holds employee data (access database). I want
to make vb 2005 express look in a table (called 'EmpData') and extract
the phone login ID's for all of the agents in the table who are still
employed or who have not left the company yet (but may have a future
end date). i want to store each of those phone logins in an array,
which is used by another procedure to extract phone usage statistics.

so the SQL statement would look like this:

(SELECT EmpData.PhoneLogin FROM EmpData WHERE (((EmpData.EndDate) Is
Null Or (EmpData.EndDate)>Now()));)

i just don't know how to make VB execute this statement and store the
results in an array??? also, will i need a 1 dimensional or 2
dimensional array for this (since i want to store 'PhoneLogin', but i'm
using 'EndDate' to get the desired results)?

Any help is greatly appreciated.

TIA

AR

15 Aug 2006 4:33 PM

adm

Rather than store the results in an array, you want to use a datareader
or store them in a data table within a dataset, then loop through the
rows in the data table to do whatever you were planning to do with that
array.

For more info on the data reader technique, see:
http://visualbasic.about.com/od/usingvbnet/l/aa050303c.htm

Basically, you need to set up an OleDbAdapter to connect to the Access
database, and then go from there. Slightly adapting the code from the
link above:

        Dim strConn As String = _
            "Provider=Microsoft.Jet.OLEDB.4.0;" _
            & "User ID=Admin;" _
            & "{path}\"employees.mdb"
        Dim ocmd As OleDbCommand
        Dim odtr As OleDbDataReader

            ocmd = New OleDbCommand()
            With ocmd
                .Connection = New _
                    OleDbConnection(stConn)
                .Connection.Open()
                .CommandText = "Select * From Employees"
                odtr = .ExecuteReader()
            End With

ducky801 wrote:
Show quoteHide quote

> Hi all,
>
> I've got some general VB experience, but i'm not sure how to approach
> the problem at hand. here is what i've got:
>
> I have a database that holds employee data (access database). I want
> to make vb 2005 express look in a table (called 'EmpData') and extract
> the phone login ID's for all of the agents in the table who are still
> employed or who have not left the company yet (but may have a future
> end date). i want to store each of those phone logins in an array,
> which is used by another procedure to extract phone usage statistics.
>
> so the SQL statement would look like this:
>
> (SELECT EmpData.PhoneLogin FROM EmpData WHERE (((EmpData.EndDate) Is
> Null Or (EmpData.EndDate)>Now()));)
>
> i just don't know how to make VB execute this statement and store the
> results in an array??? also, will i need a 1 dimensional or 2
> dimensional array for this (since i want to store 'PhoneLogin', but i'm
> using 'EndDate' to get the desired results)?
>
> Any help is greatly appreciated.
>
> TIA
>
> AR

15 Aug 2006 5:16 PM

Cor Ligthert [MVP]

Ducky,

Why do you not try this.

http://www.vb-tips.com/dbpages.aspx?ID=1139f14a-c236-4ad7-8882-b1ed16424252

It gives you back also a datatable, the first telephone number is than

datatable.rows(0)(0).ToString

Cor

Show quoteHide quote

"ducky801" <areese***@gmail.com> schreef in bericht
news:1155653143.443704.264100@p79g2000cwp.googlegroups.com...
> Hi all,
>
> I've got some general VB experience, but i'm not sure how to approach
> the problem at hand. here is what i've got:
>
> I have a database that holds employee data (access database). I want
> to make vb 2005 express look in a table (called 'EmpData') and extract
> the phone login ID's for all of the agents in the table who are still
> employed or who have not left the company yet (but may have a future
> end date). i want to store each of those phone logins in an array,
> which is used by another procedure to extract phone usage statistics.
>
> so the SQL statement would look like this:
>
> (SELECT EmpData.PhoneLogin FROM EmpData WHERE (((EmpData.EndDate) Is
> Null Or (EmpData.EndDate)>Now()));)
>
> i just don't know how to make VB execute this statement and store the
> results in an array??? also, will i need a 1 dimensional or 2
> dimensional array for this (since i want to store 'PhoneLogin', but i'm
> using 'EndDate' to get the desired results)?
>
> Any help is greatly appreciated.
>
> TIA
>
> AR
>