how to retrieve access data with a query matching a range in XL?

[gautammalkani]

Hi All

I figured out how to connect Access to Excel as shown below. However, how would i go about executing the SQL statement that compares an excel entry in cells d1 and e1 to a that of a table in Access and then imports that section from access. What would be the appropriate syntax to put in the sql statement:

select * from registered_users
where password = range(e1).value
and username = range(d1).value

Once again, thanks for your help

Gautam



Sub Connect_to_Access_DB_and_execute_SQL_Query()

Dim Conn As New ADODB.Connection
Dim RS As New ADODB.Recordset
Dim SQL As String
Dim MyDB As String
Dim R As Long, c As Long
Dim ConnStr As String
Dim User As String, PW As String
Dim u As String
Dim p As String

u = Range("D1").Value
p = Range("E1").Value


'
' Enter your SQL Query as string, like this example

SQL = "SELECT * FROM registered_users"

'
' Enter path to your Access Database as string, like
' this example

MyDB = "C:\Documents and Settings\au7271\My Documents\userids and passwords.mdb"

'
' Enter Username and password to the database
' (empty string ("") if there is no user / pw set)

User = ""
PW = ""

'
' Don't mess around with this code, unless you know what
' you are doing
' It defines the connectionstring, and opens a
' connection to the database stated above (MyDB)

ConnStr = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & MyDB & ";" & _
"Persist Security Info=False"

Conn.Open ConnStr, User, PW

'
' This opens a recordset with the query defined as SQL,
' using the connection opened previously

RS.Open SQL, Conn

'
' Inserts headers and data

Do While Not RS.EOF
R = R + 1
For c = 1 To RS.Fields.Count
If R = 1 Then
ActiveSheet.Cells(R, c) = RS.Fields(c - 1).Name
Else
ActiveSheet.Cells(R, c) = RS.Fields(c - 1).Value
End If
Next
If Not R = 1 Then RS.MoveNext
Loop




'
' Closes the database and resets the connection and
' recordset variables

RS.Close
Conn.Close
Set RS = Nothing
Set Conn = Nothing

'
' Autofits the column widths

ActiveSheet.Columns.AutoFit

End Sub

 

[PHV]

Something like this ?[tt]
SQL = "SELECT * FROM registered_users" _
& " WHERE password='" & [E1].Value & "' AND username='" & [D1].Value & "'"[/tt]

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[gautammalkani]

HI PHV

Thank you that worked for me

Cheers

Gautam