Can someone tell me what i am doeing wrong?

H

hans

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
 
J

John Green

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


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
 
H

hans

solved it
"hans" <[email protected]> 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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top