calling data from access
 

i have the following code and gives the error "Run-time error: '-2147217900
(80040e14)': String error in 'ProductId ='

Sub GetData()

Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = " Provider = Microsoft.Jet.OLEDB.4.0; " & " Data Source= " &
"C:\Temp\product.mdb"
sSQL = "SELECT Price FROM Products WHERE ProductId = " &
Range("A10").Value
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

If Not oRS.EOF Then
Sheet1.Range("C2").CopyFromRecordset oRS
Else
MsgBox (No data)
End If

oRS.Close
Set oRS = Nothing

End Sub

My db name = product.mdb
table name in my db = products
What i want to do is to call the value in 'price' column from my
'product.mdb' database to an excel sheet. My db columns a

column1 = ProductId
column2 = ProductName
column3 = Price

My excel columns are named as

A1 = ProductId
B1 = ProductName
C1 = Price

For example, when i write ProductId to my A2 cell, it will call that
product's price from the db and put the value to C2 cell.

How can i correct the error?

calling data from access
 

Going by the error ("String error in 'ProductId ="), I would assume you are
passing a string when it should be number.
Or if it should be a string, you are not enclosing it in quotes ;
sSQL = "SELECT Price FROM Products WHERE ProductId = " & Chr(34) &
Range("A10").Value & Chr(34)
Depends how your Products.ProductID column is defined.

NickHK

"batuhan" wrote in message
...
i have the following code and gives the error "Run-time error:
'-2147217900
(80040e14)': String error in 'ProductId ='

Sub GetData()

Const adOpenForwardOnly As Long = 0
Const adLockReadOnly As Long = 1
Const adCmdText As Long = 1
Dim oRS As Object
Dim sConnect As String
Dim sSQL As String
Dim ary

sConnect = " Provider = Microsoft.Jet.OLEDB.4.0; " & " Data Source= "
&
"C:\Temp\product.mdb"
sSQL = "SELECT Price FROM Products WHERE ProductId = " &
Range("A10").Value
Set oRS = CreateObject("ADODB.Recordset")
oRS.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText

If Not oRS.EOF Then
Sheet1.Range("C2").CopyFromRecordset oRS
Else
MsgBox (No data)
End If

oRS.Close
Set oRS = Nothing

End Sub

My db name = product.mdb
table name in my db = products
What i want to do is to call the value in 'price' column from my
'product.mdb' database to an excel sheet. My db columns a

column1 = ProductId
column2 = ProductName
column3 = Price

My excel columns are named as

A1 = ProductId
B1 = ProductName
C1 = Price

For example, when i write ProductId to my A2 cell, it will call that
product's price from the db and put the value to C2 cell.

How can i correct the error?

calling data from access
 

All columns in products database are default, i've made no changes to their
definitions (number or string). The ID's consist of both numbers and
characters (like 1a.23 or 2c.44.65). The code you gave me made a 'type
mismatch' error. Any other ideas? (Maybe a different solution from my code).

If it will be easier, i may import my db to another sheet and call data from
excel to excel, but i'll need the code to do that

"NickHK" wrote:

Going by the error ("String error in 'ProductId ="), I would assume you are
passing a string when it should be number.
Or if it should be a string, you are not enclosing it in quotes ;
sSQL = "SELECT Price FROM Products WHERE ProductId = " & Chr(34) &
Range("A10").Value & Chr(34)
Depends how your Products.ProductID column is defined.

NickHK

calling data from access
 

Do Debug.Print sSQL
What do you get ?

NickHK

"batuhan" ...
All columns in products database are default, i've made no changes to
their
definitions (number or string). The ID's consist of both numbers and
characters (like 1a.23 or 2c.44.65). The code you gave me made a 'type
mismatch' error. Any other ideas? (Maybe a different solution from my
code).

If it will be easier, i may import my db to another sheet and call data
from
excel to excel, but i'll need the code to do that

"NickHK" wrote:

Going by the error ("String error in 'ProductId ="), I would assume you
are
passing a string when it should be number.
Or if it should be a string, you are not enclosing it in quotes ;
sSQL = "SELECT Price FROM Products WHERE ProductId = " & Chr(34) &
Range("A10").Value & Chr(34)
Depends how your Products.ProductID column is defined.

NickHK