Hi,

I'm trying to code a function that will allow me to search data in an Access 2010 database and populate in Excel.

The issue I have is using a defined variable in the VBA code as part of the SQL where clause. Here is the current code (having problems with line 80):

Sub testconnection1()

'declared variables
Dim cn As Object, rs As Object
Dim intColIndex As Integer
Dim DBFullName As String
Dim TargetRange As Range
Dim insight_refsearch As String

'clear old search results
Sheets("Sheet1").Range("A2:A10").ClearContents

'set variable
insight_refsearch = Sheets("Search").Range("B6").Value

10 DBFullName = "\\euoxf-ds03p\groupshares\VAT Escalations\dbVAT_Escalations_FE.accdb"

20 On Error GoTo Errorhandler

30 Application.ScreenUpdating = False

40 Set TargetRange = Sheets("Sheet1").Range("A1")

50 Set cn = CreateObject("ADODB.Connection")
60 cn.Open "Provider=Microsoft.ACE.OLEDB.12.0; " & _
"Data Source=\\euoxf-ds03p\groupshares\VAT Escalations\dbVAT_Escalations_FE.accdb;Persist Security Info=False;"

70 Set rs = CreateObject("ADODB.Recordset")
80 rs.Open "SELECT * FROM tbl_VAT_Main WHERE(((tbl_VAT_Main.[Insight Ref])='insight_refsearch'))", cn, , , adCmdText

' Write the field names
90 For intColIndex = 0 To rs.Fields.Count - 1
100 TargetRange.Offset(1, intColIndex).Value = rs.Fields(intColIndex).Name
110 Next

' Write recordset
120 TargetRange.Offset(1, 0).CopyFromRecordset rs

LetsContinue:
130 Application.ScreenUpdating = True
140 On Error Resume Next
150 rs.Close
160 Set rs = Nothing
170 cn.Close
180 Set cn = Nothing
190 On Error GoTo 0
200 Exit Sub

Errorhandler:
210 MsgBox "Error Description :" & Err.Description & vbCrLf & _
"Error at line :" & Erl & vbCrLf & _
"Error Number :" & Err.Number
220 Resume LetsContinue
End Sub