Excel VAB+ADO connect ACCESS with SQL
 

Hi! I try to develop a search interface by using Excel, but I have no idea
how can I make the SQL where clause become dynamic. Thanks a lot.
The VBA code is below:

Sub ado()
Dim AlexCon As ADODB.Connection
Dim AlexRS As ADODB.Recordset
Af = Cells("a10").Value
Ase = Cells("b10").Value
Set AlexCon = New ADODB.Connection
AlexCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\test.mdb"
Set AlexRS = New ADODB.Recordset
With AlexRS
.ActiveConnection = AlexCon
.Source = "SELECT * FROM test WHERE B=" &Af &"Or A=" &Ase
.Open
End With
Range("A1").CopyFromRecordset AlexRS
AlexRS.Close
Set myRecSet = Nothing
AlexCon.Close
Set AlexCon = Nothing
End Sub

--
VBA Very Junior

Excel VAB+ADO connect ACCESS with SQL
 

You don't have a space before your Or clause in the Select statement,
so that might cause an issue. Other than that, it looks like it
should work.
"SELECT * FROM test WHERE B=" & Af &" Or A=" & Ase

alexsas wrote:
Hi! I try to develop a search interface by using Excel, but I have no idea
how can I make the SQL where clause become dynamic. Thanks a lot.
The VBA code is below:

Sub ado()
Dim AlexCon As ADODB.Connection
Dim AlexRS As ADODB.Recordset
Af = Cells("a10").Value
Ase = Cells("b10").Value
Set AlexCon = New ADODB.Connection
AlexCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\test.mdb"
Set AlexRS = New ADODB.Recordset
With AlexRS
.ActiveConnection = AlexCon
.Source = "SELECT * FROM test WHERE B=" &Af &"Or A=" &Ase
.Open
End With
Range("A1").CopyFromRecordset AlexRS
AlexRS.Close
Set myRecSet = Nothing
AlexCon.Close
Set AlexCon = Nothing
End Sub

--
VBA Very Junior

Excel VAB+ADO connect ACCESS with SQL
 

Thanks a lot. I have found the bug of the syntax.
--
VBA Very Junior


"JW" wrote:

You don't have a space before your Or clause in the Select statement,
so that might cause an issue. Other than that, it looks like it
should work.
"SELECT * FROM test WHERE B=" & Af &" Or A=" & Ase

alexsas wrote:
Hi! I try to develop a search interface by using Excel, but I have no idea
how can I make the SQL where clause become dynamic. Thanks a lot.
The VBA code is below:

Sub ado()
Dim AlexCon As ADODB.Connection
Dim AlexRS As ADODB.Recordset
Af = Cells("a10").Value
Ase = Cells("b10").Value
Set AlexCon = New ADODB.Connection
AlexCon.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=d:\test.mdb"
Set AlexRS = New ADODB.Recordset
With AlexRS
.ActiveConnection = AlexCon
.Source = "SELECT * FROM test WHERE B=" &Af &"Or A=" &Ase
.Open
End With
Range("A1").CopyFromRecordset AlexRS
AlexRS.Close
Set myRecSet = Nothing
AlexCon.Close
Set AlexCon = Nothing
End Sub

--
VBA Very Junior