Help!!! Database lookup from excel

[mju]

I keep getting Syntax error (missing operator) in query
I have tried all ican change the statement but it is still not working
Sub DATABASE()

Const BILL = "a"
Const DOC = "b"
Dim con As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim looper As Long
Dim BILLNO As String, DOCNO
Dim cellPointer As Variant


strConn = "C:\Documents and Settings\Desktop\smart.mdb"
Set con = New ADODB.Connection
con.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strConn
laname = InputBox("Enter SENDING Traping Partner")
Cells(1, 1).Value = laname
For looper = 1 To Range(BILL & Rows.Count).End(xlUp).Row
Set cellPointer = Worksheets("Sheet1").Range(BILL & looper)
sSQL = "SELECT tblTradePartner.* FROM tblTradePartner WHERE
tblTradePartner.BILL NO = " & cellPointer & " "

Set rs = New ADODB.Recordset
rs.Open sSQL, con, adOpenStatic, adLockOptimistic
If Not IsNull(rs.Fields("DOC NO").Value) Then
Range(DOC & looper) = rs.Fields("DOC NO").Value
End If
rs.Close
Set rs = Nothing
Next looper

cnn.Close
Set cnn = Nothing
End Sub

 

[Joel]

I think this line is your problem

Set cellPointer = Worksheets("Sheet1").Range(BILL & looper)

You can't pass a Range value to the SQL. You can only pass the value in the
rnage

try this

cellPointer = Worksheets("Sheet1").Range(BILL & looper).Value

If the Rage is more than one cell you will still have a problem because
excel will make cellpointer an array.

I always get my Queries working by first manualy doing the query from the
worksheet by doing the following

Data - Import External Data - New Database Query. Use a fix value for the
cellpointer value. Get this working. Then modify the query to use a Range
location from the worksheet. If you still get errors post the recorded query
and I will make the necessary changes.

 

[mju]

Thanks. I recorded the macro and this is what i got. it works fine but how do
i incorporate an input box to ask user for the bill no and then return the
corresponding doc number? Thanks alot!!!

With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DBQ=C:\Documents and Settings\spring-004\Desktop\
-DB_BE.mdb;DefaultDir=C:\Documents and Settings\\Desktop;D" _
), Array( _
"river={Microsoft Access Driver (*.mdb)};DriverId=25;FIL=MS
Access;MaxBufferSize=2048;MaxScanRows=8;PageTimeout=5;SafeTransactio" _
), Array("ns=0;Threads=3;UserCommitSync=Yes;")),
Destination:=Range("B12"))
.CommandText = Array( _
"SELECT tblPartner.ClientBillNo, tblPartner.DocumentNumber" &
Chr(13) & "" & Chr(10) & "FROM `C:\Documents and Settings\Desktop\
DB_BE`.tblPartner tblPartner" & Chr(13) & "" & Chr(10) & "WHERE
(tblPartner.Tp" _
, "ClientBillNo='10003') ")
.Name = "Query from practice2_3"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub