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

Why does this query fail in ADODB.recordset?

P: 157
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
  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
Jul 24 '09 #1
Share this Question
Share on Google+
1 Reply

Expert 5K+
P: 8,679
Try a change in Syntax, and see what happens:
Expand|Select|Wrap|Line Numbers
  1. Dim rst As New ADODB.Recordset
  3. With rst
  4.   .Source = "MAIN ompakkingskø baklager Gemba"
  5.   .ActiveConnection = CurrentProject.Connection
  6.   .CursorType = adOpenForwardOnly
  7.   .LockType = adLockReadOnly
  8.     .Open
  10.   Do While Not .EOF
  11.     MsgBox !baklager
  12.       .MoveNext
  13.   Loop
  14. End With
  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.