Exporting a row in Access to Excel using the Range function in VBA

[needhelpwithvba]

I'm trying to export a specific row from Access into Excel. In this case, I have a code that looks to cell B2 and B3 in Excel to define the criteria of what to look for in Access. My code looks like this:

Sub NumberPets()

Const strDb As String = "C:\Pets.mdb"
'looks to cell B2 and B3 for criteria on #dogs and #cats
Const strQry As String = "SELECT * from [tblPets] WHERE ([tblPets].[Dogs]=Range("B2").Value) And ([tblPets].[Cats]=Range("B3").Value)"

Dim rs As ADODB.Recordset
Dim cn As ADODB.Connection

Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Ace.OLEDB.12.0; Persist Security Info = False; " & "Data Source=C:\Pets.mdb;"
Set rs = New ADODB.Recordset

With rs
Set .ActiveConnection = cn
.Open strQry
End With
'pastes data to cell B4
Worksheets("Data").Range("B4").CopyFromRecordset rs

rs.Close: cn.Close
Set rs = Nothing: Set cn = Nothing
End Sub

I get an error message that says "Compile error: Syntax error." If I replace Range("B2").Value with 1 and Range("B3").Value with 0 the code works fine. Any help would be greatly appreciated!

 

[Norie]

Where and how are you replacing Range("B3") and Range("B2")?

 

[needhelpwithvba]

If I re-write this line of code:

Const strQry As String = "SELECT * from [tblPets] WHERE ([tblPets].[Dogs]=Range("B2").Value) And ([tblPets].[Cats]=Range("B3").Value)"

as

Const strQry As String = "SELECT * from [tblPets] WHERE ([tblPets].[Dogs]=1) And ([tblPets].[Cats]=0)"

the code works. The problem is that it no longer references cell B2 and B3, which was the whole purpose of the initial macro.

 

[RoryA]

You can't have a variable constant, so you'll have to change that to a variable and use:

Dim strQuery as string
strQry = "SELECT * from [tblPets] WHERE ([tblPets].[Dogs]=" & Range("B2").Value & ") And ([tblPets].[Cats]=" & Range("B3").Value & ")"

 

[Norie]

Oops, I got it the wrong way round - it's including the ranges causing the problem isn't it?


To do that you would have to use a variable rather than a constant.

Dim strQry As String 
 
 
strQry = " SELECT * "
strQry = strQry & " FROM [tblPets] "
strQry = strQry & " WHERE ([tblPets].[Dogs]=" & Range("B2").Value & ") AND([tblPets].[Cats]=" & Range("B3").Value & ")"

 

[needhelpwithvba]

That works now. Thank you very much!!