Help constructing a SQL String

[salzan]

I developed a query in query window and copied the sql statement as follows in my code...

strSQL = "SELECT tblCategory.CatName, " & _
                "tblDepartment.DeptName, " & _
                "tblProject.ProjName, " & _
                "tblResource.ResName, " & _
                "tblResProj.StartDate, " & _
                "tblResProj.EndDate, " & _
                "blResProj.TotalHrs, " & _
                "tblResProj.Hours, " & _
                "tblResProj.TypeHrs " & _
"FROM (tblDepartment INNER JOIN tblResource ON " & _
      "tblDepartment.DeptId = tblResource.DeptId) " & _
"INNER JOIN ((tblCategory INNER JOIN tblProject ON " & _
             "tblCategory.CatId = tblProject.CatId) " & _
"INNER JOIN tblResProj ON tblProject.ProjId = tblResProj.ProjId) ON " & _
                         "tblResource.ResId = tblResProj.ResId " & _
"WHERE tblResProj.TypeHrs = 'F';"
 
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

[Scott Price]

Your code should look something like this:

Dim db As Database
Dim rs As Recordset
 
Set db = CurrentDb()
 
Set rs = db.OpenRecordset(strSQL)
 
 

I haven't gone to the trouble of copying in your strSQL variable statement, which you can do in your database.

Regards,
Scott

[salzan]

This I how I'm doing it.

Dim cnConnect As ADODB.Connection
Dim rsInput As ADODB.Recordset
Dim strConnect As String
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
             "Data Source=" & _
             CurrentProject.Path & _
             "\" & _
             CurrentProject.Name & _
             ";"
Set cnConnect = New ADODB.Connection
cnConnect.Open strConnect
 
Set rsInput = New ADODB.Recordset
With rsInput
    .CursorType = adOpenDynamic
    .CursorLocation = adUseServer
    .LockType = adLockOptimistic
End With
 
strSQL = "SELECT tblCategory.CatName, " & _
"tblDepartment.DeptName, " & _
"tblProject.ProjName, " & _
"tblResource.ResName, " & _
"tblResProj.StartDate, " & _
"tblResProj.EndDate, " & _
"blResProj.TotalHrs, " & _
"tblResProj.Hours, " & _
"tblResProj.TypeHrs " & _
"FROM (tblDepartment INNER JOIN tblResource ON " & _
"tblDepartment.DeptId = tblResource.DeptId) " & _
"INNER JOIN ((tblCategory INNER JOIN tblProject ON " & _
"tblCategory.CatId = tblProject.CatId) " & _
"INNER JOIN tblResProj ON tblProject.ProjId = tblResProj.ProjId) ON " & _
"tblResource.ResId = tblResProj.ResId " & _
"WHERE tblResProj.TypeHrs = 'F';"
 
rsInput.Open strSQL, cnConnect

[salzan]

I also tried it your way and I got another error message: 3061 too many paramaters. Expected 1.

[Scott Price]

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:

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

[MMcCarthy]

Salsan

The connection looks fine. Have you tried running your query in the query window to see if any results are returned?

[MMcCarthy]

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.

[salzan]

The query works as I constructed it in query window.

[FishVal]

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.

Dim rsInput As ADODB.Recordset
 
Set rsInput = New ADODB.Recordset
With rsInput
    .CursorType = adOpenDynamic
    .CursorLocation = adUseServer
    .LockType = adLockOptimistic
End With
 
strSQL = "SELECT tblCategory.CatName, " & _
"tblDepartment.DeptName, " & _
"tblProject.ProjName, " & _
"tblResource.ResName, " & _
"tblResProj.StartDate, " & _
"tblResProj.EndDate, " & _
"blResProj.TotalHrs, " & _
"tblResProj.Hours, " & _
"tblResProj.TypeHrs " & _
"FROM (tblDepartment INNER JOIN tblResource ON " & _
"tblDepartment.DeptId = tblResource.DeptId) " & _
"INNER JOIN ((tblCategory INNER JOIN tblProject ON " & _
"tblCategory.CatId = tblProject.CatId) " & _
"INNER JOIN tblResProj ON tblProject.ProjId = tblResProj.ProjId) ON " & _
"tblResource.ResId = tblResProj.ResId " & _
"WHERE tblResProj.TypeHrs = 'F';"
 
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.

[salzan]

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 :)

[salzan]

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.

[puppydogbuddy]

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

[puppydogbuddy]

Sorry, did not refresh my browser. Good job Fish!

[FishVal]

Not a problem.
Good luck and happy coding.

Best regards,
Fish