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

Help constructing a SQL String

P: 38
I developed a query in query window and copied the sql statement as follows in my code...
Expand|Select|Wrap|Line Numbers
  1. strSQL = "SELECT tblCategory.CatName, " & _
  2.                 "tblDepartment.DeptName, " & _
  3.                 "tblProject.ProjName, " & _
  4.                 "tblResource.ResName, " & _
  5.                 "tblResProj.StartDate, " & _
  6.                 "tblResProj.EndDate, " & _
  7.                 "blResProj.TotalHrs, " & _
  8.                 "tblResProj.Hours, " & _
  9.                 "tblResProj.TypeHrs " & _
  10. "FROM (tblDepartment INNER JOIN tblResource ON " & _
  11.       "tblDepartment.DeptId = tblResource.DeptId) " & _
  12. "INNER JOIN ((tblCategory INNER JOIN tblProject ON " & _
  13.              "tblCategory.CatId = tblProject.CatId) " & _
  14. "INNER JOIN tblResProj ON tblProject.ProjId = tblResProj.ProjId) ON " & _
  15.                          "tblResource.ResId = tblResProj.ResId " & _
  16. "WHERE tblResProj.TypeHrs = 'F';"
  17.  
  18. rsInput.Open strSQL, cnConnect
When I open the recordset I get an error message: No Value given for one or more required parameters. I should add that the query works in the query window. Also, is there a direct way to open a query in VBA so I can step through the recordset?

Thank you in advance.
Salzan
Feb 19 '08 #1
Share this Question
Share on Google+
13 Replies


Scott Price
Expert 100+
P: 1,384
Your code should look something like this:

Expand|Select|Wrap|Line Numbers
  1. Dim db As Database
  2. Dim rs As Recordset
  3.  
  4. Set db = CurrentDb()
  5.  
  6. Set rs = db.OpenRecordset(strSQL)
  7.  
  8.  
I haven't gone to the trouble of copying in your strSQL variable statement, which you can do in your database.

Regards,
Scott
Feb 19 '08 #2

P: 38
This I how I'm doing it.
Expand|Select|Wrap|Line Numbers
  1. Dim cnConnect As ADODB.Connection
  2. Dim rsInput As ADODB.Recordset
  3. Dim strConnect As String
  4. strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
  5.              "Data Source=" & _
  6.              CurrentProject.Path & _
  7.              "\" & _
  8.              CurrentProject.Name & _
  9.              ";"
  10. Set cnConnect = New ADODB.Connection
  11. cnConnect.Open strConnect
  12.  
  13. Set rsInput = New ADODB.Recordset
  14. With rsInput
  15.     .CursorType = adOpenDynamic
  16.     .CursorLocation = adUseServer
  17.     .LockType = adLockOptimistic
  18. End With
  19.  
  20. strSQL = "SELECT tblCategory.CatName, " & _
  21. "tblDepartment.DeptName, " & _
  22. "tblProject.ProjName, " & _
  23. "tblResource.ResName, " & _
  24. "tblResProj.StartDate, " & _
  25. "tblResProj.EndDate, " & _
  26. "blResProj.TotalHrs, " & _
  27. "tblResProj.Hours, " & _
  28. "tblResProj.TypeHrs " & _
  29. "FROM (tblDepartment INNER JOIN tblResource ON " & _
  30. "tblDepartment.DeptId = tblResource.DeptId) " & _
  31. "INNER JOIN ((tblCategory INNER JOIN tblProject ON " & _
  32. "tblCategory.CatId = tblProject.CatId) " & _
  33. "INNER JOIN tblResProj ON tblProject.ProjId = tblResProj.ProjId) ON " & _
  34. "tblResource.ResId = tblResProj.ResId " & _
  35. "WHERE tblResProj.TypeHrs = 'F';"
  36.  
  37. rsInput.Open strSQL, cnConnect
Feb 19 '08 #3

P: 38
I also tried it your way and I got another error message: 3061 too many paramaters. Expected 1.
Feb 19 '08 #4

Scott Price
Expert 100+
P: 1,384
Hi Salzan,

I'm sorry that I missed you were using an ADODB connection. When using the OpenRecordset() method you can specify either a connection object or a database object to use.

You can try using this:

Expand|Select|Wrap|Line Numbers
  1. Set rsInput = cnConnect.OpenRecordset(strSQL)
However, I think you are running into a different problem. I have no way of testing the connection code (and am not an expert on this area of VBA!) so if the above doesn't work, I'll call in some of our other experts who deal with this more.

Regards,
Scott
Feb 19 '08 #5

MMcCarthy
Expert Mod 10K+
P: 14,534
Salsan

The connection looks fine. Have you tried running your query in the query window to see if any results are returned?
Feb 19 '08 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
Salsan

The connection looks fine. Have you tried running your query in the query window to see if any results are returned?
Sorry, just reread your initial post.
Feb 19 '08 #7

P: 38
The query works as I constructed it in query window.
Feb 19 '08 #8

FishVal
Expert 2.5K+
P: 2,653
Hi, salzan.

I would like to suggest you not to open new ADODB.Connection but use CurrentProject.Connection to get one already opened.
Really, when you modify code and before the module where you make changes is saved the database is in locked state and ADODB.Connection may be rejected.

Expand|Select|Wrap|Line Numbers
  1. Dim rsInput As ADODB.Recordset
  2.  
  3. Set rsInput = New ADODB.Recordset
  4. With rsInput
  5.     .CursorType = adOpenDynamic
  6.     .CursorLocation = adUseServer
  7.     .LockType = adLockOptimistic
  8. End With
  9.  
  10. strSQL = "SELECT tblCategory.CatName, " & _
  11. "tblDepartment.DeptName, " & _
  12. "tblProject.ProjName, " & _
  13. "tblResource.ResName, " & _
  14. "tblResProj.StartDate, " & _
  15. "tblResProj.EndDate, " & _
  16. "blResProj.TotalHrs, " & _
  17. "tblResProj.Hours, " & _
  18. "tblResProj.TypeHrs " & _
  19. "FROM (tblDepartment INNER JOIN tblResource ON " & _
  20. "tblDepartment.DeptId = tblResource.DeptId) " & _
  21. "INNER JOIN ((tblCategory INNER JOIN tblProject ON " & _
  22. "tblCategory.CatId = tblProject.CatId) " & _
  23. "INNER JOIN tblResProj ON tblProject.ProjId = tblResProj.ProjId) ON " & _
  24. "tblResource.ResId = tblResProj.ResId " & _
  25. "WHERE tblResProj.TypeHrs = 'F';"
  26.  
  27. rsInput.Open strSQL, CurrentProject.Connection
If that doesn't help try to open RecordSet with default CursorLocation property.
If after that you still get an error, then the problem is in SQL expression, no matter you've built it in query editor - get a runtime value of strSQL and try to execute it in query editor window.

Regards,
Fish.
Feb 19 '08 #9

P: 38
I tried both of your suggestions and neither of them worked. this problem has mr bafold. Anybody out there that can help, I'm willing to ship a case of beer :)
Feb 19 '08 #10

P: 38
IT WORKED. THe Problem was with the sql string. Your suggestion to get a runtime value for the strSQL and try it in a query window led me to the problem.

Thank you very much.
Feb 19 '08 #11

puppydogbuddy
Expert 100+
P: 1,923
The query works as I constructed it in query window.
I think your problem could be your connection string.
See this link:
http://www.connectionstrings.com/def...carrier=access
Feb 19 '08 #12

puppydogbuddy
Expert 100+
P: 1,923
Sorry, did not refresh my browser. Good job Fish!
Feb 19 '08 #13

FishVal
Expert 2.5K+
P: 2,653
Not a problem.
Good luck and happy coding.

Best regards,
Fish
Feb 19 '08 #14

Post your reply

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