I use the following code.

Sub ADOImportFromAccessTable(DBFullName As String, TableName As String, TargetRange As Range)
' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", _
"TableName", Range("C1")
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set TargetRange = TargetRange.Cells(1, 1)
target = TargetRange.Cells(1, 1)
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"
Set rs = New ADODB.Recordset
With rs
.Open "SELECT * FROM Postcode" & " WHERE [Postcode] = " & TargetRange, cn, , , adCmdText
waarde = rs.Fields(intColIndex).Value
TargetRange.Offset(0, 1) = waarde

I get the error
syntaxerror operator missing.

Can someone help
Thanks Hans

Hans,

If PostCode is a text field you need to enclose the referenced text in double or single quotes. Singles are the simpler:

.Open "SELECT * FROM Postcode WHERE [Postcode] = '" & TargetRange.Value & "'", cn, , , adCmdText

Copy the above line to your code module and the sequence of quotes will be much easier to read.

--

John Green - Excel MVP
Sydney
Australia


"hans" wrote in message ...
I use the following code.

Sub ADOImportFromAccessTable(DBFullName As String, TableName As String, TargetRange As Range)
' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", _
"TableName", Range("C1")
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set TargetRange = TargetRange.Cells(1, 1)
target = TargetRange.Cells(1, 1)
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"
Set rs = New ADODB.Recordset
With rs
.Open "SELECT * FROM Postcode" & " WHERE [Postcode] = " & TargetRange, cn, , , adCmdText
waarde = rs.Fields(intColIndex).Value
TargetRange.Offset(0, 1) = waarde

I get the error
syntaxerror operator missing.

Can someone help
Thanks Hans

solved it
"hans" schreef in bericht ...
I use the following code.

Sub ADOImportFromAccessTable(DBFullName As String, TableName As String, TargetRange As Range)
' Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", _
"TableName", Range("C1")
Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set TargetRange = TargetRange.Cells(1, 1)
target = TargetRange.Cells(1, 1)
' open the database
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & DBFullName & ";"
Set rs = New ADODB.Recordset
With rs
.Open "SELECT * FROM Postcode" & " WHERE [Postcode] = " & TargetRange, cn, , , adCmdText
waarde = rs.Fields(intColIndex).Value
TargetRange.Offset(0, 1) = waarde

I get the error
syntaxerror operator missing.

Can someone help
Thanks Hans