469,626 Members | 883 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,626 developers. It's quick & easy.

Why does this query fail in ADODB.recordset?

157 100+
I have query this to filter the posts i want to work with, it is called "MAIN ompakkingskø baklager Gemba" and the sql is this:

Expand|Select|Wrap|Line Numbers
  1. SELECT [LOK Loksummer].Varenr, [LOK Loksummer].Lokasjon AS Baklager, [LOK Loksummer].[Antall tabletter], Left([lokasjon],2) AS c
  2. FROM [LOK Loksummer]
  3. WHERE ((([LOK Loksummer].Lokasjon) Like "*Gemba*") AND ((Left([lokasjon],2))="01" Or (Left([lokasjon],2))="02" Or (Left([lokasjon],2))="03" Or (Left([lokasjon],2))="04" Or (Left([lokasjon],2))="05" Or (Left([lokasjon],2))="06" Or (Left([lokasjon],2))="07" Or (Left([lokasjon],2))="08" Or (Left([lokasjon],2))="09" Or (Left([lokasjon],2))="10" Or (Left([lokasjon],2))="11" Or (Left([lokasjon],2))="12" Or (Left([lokasjon],2))="13" Or (Left([lokasjon],2))="14" Or (Left([lokasjon],2))="15" Or (Left([lokasjon],2))="16" Or (Left([lokasjon],2))="17" Or (Left([lokasjon],2))="18"));
I always use ADODB.recordset to work with data. Either to insert data in forms or in tables.

When i open the query by clicking on the query it shows exactly the post it is supposed to show, but when i open it with ADODB it gives an error

Expand|Select|Wrap|Line Numbers
  1. Dim rst As New ADODB.Recordset
  2. With rst
  3.     .Open "MAIN ompakkingskø baklager Gemba", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
  4.         Do Until .EOF
  5.         MsgBox !baklager
  6.         .MoveNext
  7.         Loop
  8.     .Close
  9. End With
  10. Set rst = Nothing
This DAO is working fine:
Expand|Select|Wrap|Line Numbers
  1. Dim rs As dao.Recordset
  2.  
  3. Set rs = CurrentDb.OpenRecordset("MAIN ompakkingskø baklager Gemba")
  4. While Not rs.EOF
  5. MsgBox rs!baklager
  6. rs.MoveNext
  7. Wend
  8. rs.Close
  9. Set rs = Nothing
  10.  
Jul 24 '09 #1
1 2302
ADezii
8,800 Expert 8TB
@MrDeej
Try a change in Syntax, and see what happens:
Expand|Select|Wrap|Line Numbers
  1. Dim rst As New ADODB.Recordset
  2.  
  3. With rst
  4.   .Source = "MAIN ompakkingskø baklager Gemba"
  5.   .ActiveConnection = CurrentProject.Connection
  6.   .CursorType = adOpenForwardOnly
  7.   .LockType = adLockReadOnly
  8.     .Open
  9.  
  10.   Do While Not .EOF
  11.     MsgBox !baklager
  12.       .MoveNext
  13.   Loop
  14. End With
  15.  
  16. rst.Close
  17. Set rst = Nothing
What is the Error you are receiving if the previous does not work?

You should also consider modifying part of your WHERE Clause:
Expand|Select|Wrap|Line Numbers
  1. AND Left$([Lokasjon],2) In ("01","02","03","04","05","06","07","08","09","10","11","12","13","14","15","16","17","18")
Jul 24 '09 #2

Post your reply

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

Similar topics

2 posts views Thread by Auddog | last post: by
2 posts views Thread by barret bonden | last post: by
1 post views Thread by Mayhem05 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.