Sql – VBA string length problem

ms-accesssqlstringvba

I have an Access application where everytime a user enters the application, it makes a temp table for that user called 'their windows login name'_Temp. In one of my reports I need to query using that table, and I can't just make a query and set it as the recourdsource of the report, since the name of the table is always different.

What I tried then was to programatically set the recordset of the report by running the query and setting the form's recordset as the query's recordset. When I tried this, it kept giving me an error about the query.

I tried to debug, and I found that the string variable isn't able to contain the whole query at once. When I ran it with break points and added a watch for the string variable, it shows me that it cuts off the query somewhere in the middle.

I've experienced this problem before, but that was with an UPDATE query. Then, I just split it into two queries and ran both of them separately. This one is a SELECT query, and there's no way I can split it. Please help!

Thank you

Heres what I've tried doing:

ReturnUserName is a function in a module that returns just the login id of the user

Private Sub Report_Open(Cancel As Integer)

Dim strQuery As String
Dim user As String
user = ReturnUserName

strQuery = "SELECT " & user & "_Temp.EmpNumber, [FName] & ' ' & [LName] AS [Employee Name], " & _
"CourseName, DateCompleted, tblEmp_SuperAdmin.[Cost Centre] " & _
"FROM (tblCourse INNER JOIN (" & user & "_Temp INNER JOIN tblEmpCourses ON " & _
user & "_Temp.EmpNumber = EmpNo) ON tblCourse.CourseID = tblEmpCourses.CourseID) " & _
"INNER JOIN tblEmp_SuperAdmin ON " & user & "_Temp.EmpNumber = tblEmp_SuperAdmin.EmpNumber" & _
"WHERE (((" & user & "_Temp.EmpNumber) = [Forms]![Reports]![txtEmpID].[Text])) " & _
"ORDER BY CourseName;"

Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
Dim rsCmd As ADODB.Command
Set rsCmd = New ADODB.Command
rsCmd.ActiveConnection = CurrentProject.Connection

rsCmd.CommandText = strQuery
rs.Open rsCmd
Me.Recordset = rs
rs.Close

End Sub

This what strQuery contains when I add a breakpoint on rsCmd.CommandText = strQuery:

SELECT myusername_Temp.EmpNumber, [FName]
& ' ' & [LName] AS [Employee Name],
CourseName, DateCompleted,

tblEmp_SuperAdmin.[Cost Centre] FROM

(tblCourse INNER JOIN (myusername_Temp
INNER JOIN tblEmpCourses ON

myusername_Temp.EmpNumber = EmpNo) ON
tblCourse.CourseID=

(It's all one line, but I've written it like this because the underscores italicize the text)

And the error I get says Run Time Error: Join not Supported.

Best Answer

Not quite what I was hoping for, but guessing, for:

strQuery = "long query goes here"

Try:

strQuery = "some long query goes here "
strQuery = strQuery  & "more query goes here "

BASED ON NEW INFORMATION:

strQuery = "SELECT " & user & "_Temp.EmpNumber, [FName] & ' ' & [LName] AS [Employee Name], " & _
"CourseName, DateCompleted, tblEmp_SuperAdmin.[Cost Centre] " & _
"FROM (tblCourse " & _
"INNER JOIN tblEmpCourses ON tblCourse.CourseID = tblEmpCourses.CourseID) " & _
"INNER JOIN (Temp INNER JOIN tblEmp_SuperAdmin  " & _
"ON  Temp.EmpNumber = tblEmp_SuperAdmin.EmpNumber)  " & _
"ON Temp.EmpNumber = tblEmpCourses.EmpNo  " & _
"WHERE " & user & "_Temp.EmpNumber =  " & [Forms]![Reports]![txtEmpID] & _
" ORDER BY CourseName;"

Note that in VBA: 

& [Forms]![Reports]![txtEmpID].[Text] &

That is, the reference to the form must go outside the quotes so you get the value.

NEW INFORMATION #2

Your best bet would be to add these tables to the Access query design window and create the joins that you want, then switch to SQL view and use the string generated for you. I do not believe that the string is too long, only that the SQL is incorrect. The SQL I posted above should work, but it may not be what you want.

Related Topic