Get results from Access Database.

[fi.or.jp.de]

Hi All,

I have Excel File & Access Database.

Access Database Contains - Employee details like
Employee ID, Address, Contact No., Process.
Database has more 5000 records.

In Excel I have 200 Employee ID - I need Contact No. & Process.
How can i get this results using ADO connection.

Earlier we use to export access database to excel and then
do VLookup to get the results.

Now we are trying to automate this steps.

I am using like this

Dim DB As ADODB.Connection
Dim RS As ADODB.Recordset

Set DB = New ADODB.Connection

With DB
.Provider = "Microsoft.JET.OLEDB.4.0"
.ConnectionString = "Data Source=" & LOCATION5 & ";" & _
"Jet OLEDBatabase Password=" & PSWD & ";"
.Open
End With

Set RS = New ADODB.Recordset

SQL = "SELECT * FROM EMPLOYEETABLE"
SQL = SQL & " WHERE EMP_ID = " & Sheets(sheet1).Range("A1").value

RS.Open SQL, DB, adOpenDynamic, adLockOptimistic

and it goes like and get the result.
I showed you only if i have one employee id....

Actually I have 200 employee ids in Column A.
If I use Do.... Loop.
Is this will run faster ??

I need to get the data in a faster way.

Please help how can i Achieve this.

 

[Bob Phillips]

Try this

Lastrow = Sheets("Sheet1").Range("A1").End(xlDown).Row
vecLookup =
Application.Transpose(Sheets("Sheet1").Range("A1").Resize(Lastrow))
Sql = "SELECT * FROM EMPLOYEETABLE"
Sql = Sql & " WHERE EMP_ID IN (" & Join(vecLookup, ",") & ")"


HTH

Bob

"fi.or.jp.de" wrote in message

Hi All,

I have Excel File & Access Database.

Access Database Contains - Employee details like
Employee ID, Address, Contact No., Process.
Database has more 5000 records.

In Excel I have 200 Employee ID - I need Contact No. & Process.
How can i get this results using ADO connection.

Earlier we use to export access database to excel and then
do VLookup to get the results.

Now we are trying to automate this steps.

I am using like this

Dim DB As ADODB.Connection
Dim RS As ADODB.Recordset

Set DB = New ADODB.Connection

With DB
.Provider = "Microsoft.JET.OLEDB.4.0"
.ConnectionString = "Data Source=" & LOCATION5 & ";" & _
"Jet OLEDBatabase Password=" & PSWD & ";"
.Open
End With

Set RS = New ADODB.Recordset

SQL = "SELECT * FROM EMPLOYEETABLE"
SQL = SQL & " WHERE EMP_ID = " & Sheets(sheet1).Range("A1").value

RS.Open SQL, DB, adOpenDynamic, adLockOptimistic

and it goes like and get the result.
I showed you only if i have one employee id....

Actually I have 200 employee ids in Column A.
If I use Do.... Loop.
Is this will run faster ??

I need to get the data in a faster way.

Please help how can i Achieve this.

 

[fi.or.jp.de]

Bob simply great....

Thank you.