By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,972 Members | 1,115 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,972 IT Pros & Developers. It's quick & easy.

Look up excel value in access table and return value

P: 63
I am trying to return a value from an access database by looking up a value in excel. This is the code I have copied from another source - but I cannot get it to work - I keep getting a run time error not a valid file name at the cnn.open part

I want to look up the value in column A of the excel sheet in an access table called Bank Gtes (guarantees2.mdb database) The matching field is called GTEE_NMBR. If a match is found I simply want to return this value in column J

- please help!



Sub getProjectDescFromAccess()
'Needs reference the Axtive X Library 2.0 or higher
Const projectIDColumn = "A"
Const projectDescColumn = "J"
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim looper As Long
Dim cellPointer As Variant

'Q:\IT\Database Masters\Guarantees2.mdb
'
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & ThisWorkbook.Path & "Q:\IT\Database Masters\Guarantees2.mdb;Persist Security Info=False"
Set cnn = New ADODB.Connection
cnn.Open strConn

For looper = 2 To Range(projectIDColumn & Rows.Count).End(xlUp).Row
Set cellPointer = Worksheets("Sheet1").Range(projectIDColumn & looper)
'If you project number field is text use this sSQL
sSQL = "SELECT tblBank Gtes.* FROM tblBank Gtes WHERE(((tblBank Gtes.GTEE_NMBR)='" & cellPointer & "'));"
'If you project number field is number use this sSQL
'sSQL = "SELECT tblBank Gtes.* FROM tblBank Gtes WHERE" (((tblBank Gtes.GTEE_NMBR)=" & cellPointer & "));"
Set rs = New ADODB.Recordset

rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
If Not IsNull(rs.Fields("GTEE_NMBR").Value) Then
Range(projectDescColumn & looper) = rs.Fields("GTEE_NMBR").Value
End If
rs.Close
Set rs = Nothing
Next looper

cnn.Close
Set cnn = Nothing
End Sub
Richard Gregson
Treasury Systems Manager
(01332) (2)45132
Jun 17 '08 #1
Share this Question
Share on Google+
6 Replies


Expert 100+
P: 634
Hi

Assuming that the Excel file an DB as in the same directory, then you connection string should be like this

strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & ThisWorkbook.Path & "\Guarantees2.mdb;Persist Security Info=False"

If not then like this?

strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=Q:\IT\Database Masters\Guarantees2.mdb;Persist Security Info=False"
Set cnn = New ADODB.Connection

I havn't checked the rest of the code (one think at a time!).

MTB
Jun 17 '08 #2

P: 63
Thanks a lot Mike - that fixed that issue perfectly . Unfortunately the code now gets stuck at this line

sSQL = "SELECT tblBank Gtes.* FROM tblBank Gtes WHERE(((tblBank Gtes.GTEE_NMBR)='" & cellPointer & "'));"


I get a syntax error (missing operator) in query expression '(((tblBank Gtes.GTEE_NMBR)="))'. message

I've tried juggling around the " and ' and adding )) but I can't seem to get the right combination

Any ideas? The rest of the code is below

thanks












Sub getProjectDescFromAccess()
'Needs reference the Axtive X Library 2.0 or higher
Const projectIDColumn = "A"
Const projectDescColumn = "J"
Dim cnn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sSQL As String, strConn
Dim looper As Long
Dim cellPointer As Variant

'Q:\IT\Database Masters\Guarantees2.mdb
'
strConn = "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=Q:\IT\Database Masters\Guarantees2.mdb;Persist Security Info=False"
Set cnn = New ADODB.Connection
cnn.Open strConn

For looper = 2 To Range(projectIDColumn & Rows.Count).End(xlUp).Row
Set cellPointer = Worksheets("Charges Form").Range(projectIDColumn & looper)
'If you project number field is text use this sSQL
sSQL = "SELECT tblBank Gtes.* FROM tblBank Gtes WHERE(((tblBank Gtes.GTEE_NMBR)='" & cellPointer & "'));"
'If you project number field is number use this sSQL
'sSQL = "SELECT tblBank Gtes.* FROM tblBank Gtes WHERE" (((tblBank Gtes.GTEE_NMBR)=" & cellPointer & "));"
Set rs = New ADODB.Recordset

rs.Open sSQL, cnn, adOpenStatic, adLockOptimistic
If Not IsNull(rs.Fields("GTEE_NMBR").Value) Then
Range(projectDescColumn & looper) = rs.Fields("GTEE_NMBR").Value
End If
rs.Close
Set rs = Nothing
Next looper

cnn.Close
Set cnn = Nothing
End Sub
Jun 20 '08 #3

Expert 100+
P: 634
Hi

On the basis this is a run time error and not a compile error, and this is the actual error message

(missing operator) in query expression '(((tblBank Gtes.GTEE_NMBR)="))'

My guess is that cell that sets the value of cellPointer is bank ???

This also seem to be the numeric version of the sSQL not the text version!

MTB
Jun 20 '08 #4

P: 63
Hi Mike - There are blank records in the GTEE_NMBR field - but the matching record I am using to test this code does exist in this access table field. So I think that cell pointer should have a value. The error I get does say syntax error before the missing operator - so I think I may have typed the code incorrectly!

Its definitely the text code that I want! so the bit that seems to be causing problems is as follows

sSQL = "SELECT tblBank Gtes.* FROM tblBank Gtes WHERE(((tblBank Gtes.GTEE_NMBR)='" & cellPointer & "'));"

Any ideas or am I missing something!

Thanks again for your help on this

Hi

On the basis this is a run time error and not a compile error, and this is the actual error message

(missing operator) in query expression '(((tblBank Gtes.GTEE_NMBR)="))'

My guess is that cell that sets the value of cellPointer is bank ???

This also seem to be the numeric version of the sSQL not the text version!

MTB
Jun 23 '08 #5

Expert 100+
P: 634
Hi Mike - There are blank records in the GTEE_NMBR field - but the matching record I am using to test this code does exist in this access table field. So I think that cell pointer should have a value. The error I get does say syntax error before the missing operator - so I think I may have typed the code incorrectly!

Its definitely the text code that I want! so the bit that seems to be causing problems is as follows

sSQL = "SELECT tblBank Gtes.* FROM tblBank Gtes WHERE(((tblBank Gtes.GTEE_NMBR)='" & cellPointer & "'));"

Any ideas or am I missing something!

Thanks again for your help on this
Hi

I did start to post this last time, but I must have got distracted !!

Try this

sSQL = "SELECT [tblBank Gtes].* FROM [tblBank Gtes] WHERE((([tblBank Gtes].GTEE_NMBR)='" & cellPointer & "'));"


Note the square brackets round the tabel name (these are needed if the name contains a space) and seen what you get now



MTB
Jun 23 '08 #6

P: 63
Worked a treat - thank you so much - sorry for the slow response!!!!
Jul 18 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.