Please help, need VBA to get information from access table...

[pedie]

Hi, please help me with VBA to retrive information meeting critiria from access table. I want to get data in to sheet1, A1;
Critiria is if Manager is = Linda and Location is = NY
my table is as shown below;

<TABLE style="WIDTH: 290pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=385><COLGROUP><COL style="WIDTH: 14pt; mso-width-source: userset; mso-width-alt: 658" width=18><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4059" width=111><COL style="WIDTH: 38pt; mso-width-source: userset; mso-width-alt: 1828" width=50><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><TBODY><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c5be97; WIDTH: 14pt; HEIGHT: 13.5pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=18 width=18>

ID​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; WIDTH: 83pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=111>

EMPLOYEE_NAMES​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; WIDTH: 38pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=50>

Manager​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; WIDTH: 60pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=80>

Dept_Name​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=64>

LOCATION​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; WIDTH: 47pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=62>

COUNTRY​

</TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #c5be97; HEIGHT: 13.5pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=18>

1​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>

Pedie​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>

Linda​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>

ATT​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>

NY​

</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #c5be97; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63>

US​

</TD></TR></TBODY></TABLE>


I have done a lot of search and tried out alot of them but is not that easy to arrange them together to work...


Thanks in advance for helping.

 

[Ruddles]

This should do the job:-

Option Explicit
 
Sub Sub4Pedie()
 
  Dim strMyDB As String
  Dim conMyDB As ADODB.Connection
  Dim rstMyDB As ADODB.Recordset
  Dim strQuery As String
  
  strMyDB = "[B][COLOR=red]C:\TEMP\scratch_database1.mdb[/COLOR][/B]"
  Set conMyDB = New ADODB.Connection
  conMyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & strMyDB & "';"
  Set rstMyDB = New ADODB.Recordset
  strQuery = "SELECT * FROM [B][COLOR=blue]Pedie[/COLOR][/B] WHERE Manager='Linda' AND LOCATION='NY';"
  
  With rstMyDB
    .Open strQuery, conMyDB, , , adCmdText
    If rstMyDB Is Nothing Or rstMyDB.State <> adStateOpen Then
      ' no records in recordset or failed to open recordset - nothing to do
    Else
      Sheets("Sheet1").Range("A1").CopyFromRecordset rstMyDB
    End If
  End With
  
  rstMyDB.Close
  conMyDB.Close
  
End Sub

You need to place the path to your Access database in place of the red bit and the name of your Access table in place of the blue bit.

Shout if any problems.

 

[Ruddles]

Sorry, in VBA you need to add a reference to the latest Microsoft ActiveX Data Objects Library (Tools > References).

 

[pedie]

Ruddles, perfect.... need more help... it works only when i remove the password; the database has password "123" for now...

Could you please make this changes that it open the password protected database get the data?

Thanks again.Much appriciated!

 

[Ruddles]

I don't know for sure but maybe something like this:-

[FONT=Courier New]  conMyDB.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='" & strMyDB & "';" _[/FONT]
[FONT=Courier New]             & "Jet OLEDB:Database Password=123"
[/FONT]

(Source: http://www.vb-helper.com/howto_ado_use_access_password.html.)

 

[pedie]

Ruddles, thanks again...
it is returning runtime error; 2147467259 80004005
unrecognized databased format 'C:\myfile.accdb

Thanks again for checking it for me....

Pls recheck it whenever you're free...

 

[Ruddles]

Ah, ACCDB! Try:-

  conMyDB.Open "Provider=Microsoft.Ace.OLEDB.12.0; Data Source='" & strMyDB & "';" _
             & "Ace OLEDB:Database Password=123"

 

[pedie]

Ruddles, I think..now 'm starting to trouble you
'm getting error

"Could not find installable ISAM"
I googled but and this could be that my registry is missing something or not properly installed...

Is there another approach to this...

Thanks again...

 

[pedie]

Ruddles, thank you very much!!! 'l go for the one without the password:

Have a great day ahead!!! and Good night!

 

[Ruddles]

Ruddles, I think..now 'm starting to trouble you
'm getting error

"Could not find installable ISAM"

Last attempt (hopefully):-

  conMyDB.Open "Provider=Microsoft.[COLOR=red][B]Ace[/B][/COLOR].OLEDB.[COLOR=red][B]12[/B][/COLOR].0; Data Source='" & strMyDB & "';" _
             & "[B][COLOR=red]Jet[/COLOR][/B] OLEDB:Database Password=123"