Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

strange run-time error

Status
Not open for further replies.
dharkangel

dharkangel

MIS
Sep 25, 2002
159
US
Hi everyone, I have this piece of code that I repeat below. It runs fine until it gets to the sixth iteration of the code (the part that says 'REPEAT CODE' area. I thought it may be a problem with the SQL for that sixth specific block of code but it wasn't. The runtime error occurs on the sixth iteration of the code no matter what variables are in the code. The error says 'Run-time error -2147217865 (80040e37): The Microsoft Jet database engine could not find the object ''. Make sure the object exists and that you specll its name and the path name correctly'

Dim etmRecordSet As New ADODB.Recordset 'create recordset var
Dim etmRecordSetCount As Integer 'create var to store record count of specific week data
Dim etmCombinedRecordSet As New ADODB.Recordset 'create recordset var
Dim etmCombinedRecordSetCount As Integer 'create var to store record count of specific week data

Dim eumRecordSet As New ADODB.Recordset 'create recordset var
Dim eumRecordSetCount As Integer 'create var to store record count of specific week data
Dim eumCombinedRecordSet As New ADODB.Recordset 'create recordset var
Dim eumCombinedRecordSetCount As Integer 'create var to store record count of specific week data


'*************************************************
'REPEAT CODE BEGIN
'ETM
'**************************************************
etmRecordSet.ActiveConnection = myConnection 'set recordset var

'set name of role from array
RoleName = RoleArray(1, 0)

'get the number of Assigned Actions for a given week, for ETM
mySQL = "SELECT DISTINCT (Replace(QPTActions.[Assigned To],',','')) AS Assignee, UserRoleData.Role " & _
"FROM QPTActions INNER JOIN UserRoleData ON UserRoleData.[Resource Name]=Replace(QPTActions.[Assigned To],',','') " & _
"WHERE DateValue(QPTActions.[Entry Date])<=DateValue('" & weekStart & "') And DateValue(QPTActions.[Close Date])>DateValue('" & weekStart & "') AND UserRoleData.Role='" & RoleName & "' " & _
"UNION SELECT DISTINCT (Replace(BarcelonaInternalActions.[Assign To],',','')) AS Assignee, UserRoleData.Role " & _
"FROM BarcelonaInternalActions INNER JOIN UserRoleData ON UserRoleData.[Resource Name]=Replace(BarcelonaInternalActions.[Assign To],',','') " & _
"WHERE DateValue(BarcelonaInternalActions.[Entry Date])<=DateValue('" & weekStart & "') And DateValue(BarcelonaInternalActions.[Close Date])>DateValue('" & weekStart & "') AND UserRoleData.Role='" & RoleName & "' " & _
"UNION " & _
"SELECT DISTINCT (Replace(RDActions.[Assigned To],',','')) AS Assignee, UserRoleData.Role " & _
"FROM RDActions INNER JOIN UserRoleData ON UserRoleData.[Resource Name]=Replace(RDActions.[Assigned To],',','') " & _
"WHERE DateValue(RDActions.[Entry Date])<=DateValue('" & weekStart & "') And DateValue(RDActions.[Close Date])>DateValue('" & weekStart & "') AND UserRoleData.Role='" & RoleName & "' " & _
"UNION SELECT DISTINCT (Replace(TokyoInternalActions.[Assigned To],',','')) AS Assignee, UserRoleData.Role " & _
"FROM TokyoInternalActions INNER JOIN UserRoleData ON UserRoleData.[Resource Name]=Replace(TokyoInternalActions.[Assigned To],',','') " & _
"WHERE DateValue(TokyoInternalActions.[Entry Date])<=DateValue('" & weekStart & "') And DateValue(TokyoInternalActions.[Close Date])>DateValue('" & weekStart & "') AND UserRoleData.Role='" & RoleName & "';"

'open the recordset of all the data from SQL statement
etmRecordSet.Open mySQL, , adOpenStatic

'get the record count
etmRecordSetCount = etmRecordSet.RecordCount

'set connection for second query
etmCombinedRecordSet.ActiveConnection = myConnection 'set recordset var

'get the number of Assigned Actions for a given week, for ETM
mySQL2 = "SELECT [Issue ID], Replace(QPTActions.[Assigned To],',','') AS Assignee, UserRoleData.Role " & _
"FROM QPTActions INNER JOIN UserRoleData ON UserRoleData.[Resource Name]=Replace(QPTActions.[Assigned To],',','') " & _
"WHERE DateValue(QPTActions.[Entry Date])<=DateValue('" & weekStart & "') And DateValue(QPTActions.[Close Date])>DateValue('" & weekStart & "') AND UserRoleData.Role='" & RoleName & "' " & _
"UNION SELECT [Issue ID], Replace(BarcelonaInternalActions.[Assign To],',','') AS Assignee, UserRoleData.Role " & _
"FROM BarcelonaInternalActions INNER JOIN UserRoleData ON UserRoleData.[Resource Name]=Replace(BarcelonaInternalActions.[Assign To],',','') " & _
"WHERE DateValue(BarcelonaInternalActions.[Entry Date])<=DateValue('" & weekStart & "') And DateValue(BarcelonaInternalActions.[Close Date])>DateValue('" & weekStart & "') AND UserRoleData.Role='" & RoleName & "' " & _
"UNION " & _
"SELECT [Issue ID], Replace(RDActions.[Assigned To],',','') AS Assignee, UserRoleData.Role " & _
"FROM RDActions INNER JOIN UserRoleData ON UserRoleData.[Resource Name]=Replace(RDActions.[Assigned To],',','') " & _
"WHERE DateValue(RDActions.[Entry Date])<=DateValue('" & weekStart & "') And DateValue(RDActions.[Close Date])>DateValue('" & weekStart & "') AND UserRoleData.Role='" & RoleName & "' " & _
"UNION SELECT [Issue ID], Replace(TokyoInternalActions.[Assigned To],',','') AS Assignee, UserRoleData.Role " & _
"FROM TokyoInternalActions INNER JOIN UserRoleData ON UserRoleData.[Resource Name]=Replace(TokyoInternalActions.[Assigned To],',','') " & _
"WHERE DateValue(TokyoInternalActions.[Entry Date])<=DateValue('" & weekStart & "') And DateValue(TokyoInternalActions.[Close Date])>DateValue('" & weekStart & "') AND UserRoleData.Role='" & RoleName & "';"

'open the recordset of all the data from SQL statement
etmCombinedRecordSet.Open mySQL2, , adOpenStatic

'get the record count
etmCombinedRecordSetCount = etmCombinedRecordSet.RecordCount


'*************************************************
'REPEAT CODE END
'**************************************************
'*************************************************
'REPEAT CODE BEGIN
'EUM
'**************************************************
eumRecordSet.ActiveConnection = myConnection 'set recordset var

'set name of role from array
RoleName = RoleArray(2, 0)

'get the number of Assigned Actions for a given week, for EUM
mySQL = "SELECT DISTINCT (Replace(QPTActions.[Assigned To],',','')) AS Assignee, UserRoleData.Role " & _
"FROM QPTActions INNER JOIN UserRoleData ON UserRoleData.[Resource Name]=Replace(QPTActions.[Assigned To],',','') " & _
"WHERE DateValue(QPTActions.[Entry Date])<=DateValue('" & weekStart & "') And DateValue(QPTActions.[Close Date])>DateValue('" & weekStart & "') AND UserRoleData.Role='" & RoleName & "' " & _
"UNION SELECT DISTINCT (Replace(BarcelonaInternalActions.[Assign To],',','')) AS Assignee, UserRoleData.Role " & _
"FROM BarcelonaInternalActions INNER JOIN UserRoleData ON UserRoleData.[Resource Name]=Replace(BarcelonaInternalActions.[Assign To],',','') " & _
"WHERE DateValue(BarcelonaInternalActions.[Entry Date])<=DateValue('" & weekStart & "') And DateValue(BarcelonaInternalActions.[Close Date])>DateValue('" & weekStart & "') AND UserRoleData.Role='" & RoleName & "' " & _
"UNION " & _
"SELECT DISTINCT (Replace(RDActions.[Assigned To],',','')) AS Assignee, UserRoleData.Role " & _
"FROM RDActions INNER JOIN UserRoleData ON UserRoleData.[Resource Name]=Replace(RDActions.[Assigned To],',','') " & _
"WHERE DateValue(RDActions.[Entry Date])<=DateValue('" & weekStart & "') And DateValue(RDActions.[Close Date])>DateValue('" & weekStart & "') AND UserRoleData.Role='" & RoleName & "' " & _
"UNION SELECT DISTINCT (Replace(TokyoInternalActions.[Assigned To],',','')) AS Assignee, UserRoleData.Role " & _
"FROM TokyoInternalActions INNER JOIN UserRoleData ON UserRoleData.[Resource Name]=Replace(TokyoInternalActions.[Assigned To],',','') " & _
"WHERE DateValue(TokyoInternalActions.[Entry Date])<=DateValue('" & weekStart & "') And DateValue(TokyoInternalActions.[Close Date])>DateValue('" & weekStart & "') AND UserRoleData.Role='" & RoleName & "';"

'open the recordset of all the data from SQL statement
eumRecordSet.Open mySQL, , adOpenStatic

'get the record count
eumRecordSetCount = eumRecordSet.RecordCount

'set connection for second query
eumCombinedRecordSet.ActiveConnection = myConnection 'set recordset var

'get the number of Assigned Actions for a given week, for EUM
mySQL2 = "SELECT [Issue ID], Replace(QPTActions.[Assigned To],',','') AS Assignee, UserRoleData.Role " & _
"FROM QPTActions INNER JOIN UserRoleData ON UserRoleData.[Resource Name]=Replace(QPTActions.[Assigned To],',','') " & _
"WHERE DateValue(QPTActions.[Entry Date])<=DateValue('" & weekStart & "') And DateValue(QPTActions.[Close Date])>DateValue('" & weekStart & "') AND UserRoleData.Role='" & RoleName & "' " & _
"UNION SELECT [Issue ID], Replace(BarcelonaInternalActions.[Assign To],',','') AS Assignee, UserRoleData.Role " & _
"FROM BarcelonaInternalActions INNER JOIN UserRoleData ON UserRoleData.[Resource Name]=Replace(BarcelonaInternalActions.[Assign To],',','') " & _
"WHERE DateValue(BarcelonaInternalActions.[Entry Date])<=DateValue('" & weekStart & "') And DateValue(BarcelonaInternalActions.[Close Date])>DateValue('" & weekStart & "') AND UserRoleData.Role='" & RoleName & "' " & _
"UNION " & _
"SELECT [Issue ID], Replace(RDActions.[Assigned To],',','') AS Assignee, UserRoleData.Role " & _
"FROM RDActions INNER JOIN UserRoleData ON UserRoleData.[Resource Name]=Replace(RDActions.[Assigned To],',','') " & _
"WHERE DateValue(RDActions.[Entry Date])<=DateValue('" & weekStart & "') And DateValue(RDActions.[Close Date])>DateValue('" & weekStart & "') AND UserRoleData.Role='" & RoleName & "' " & _
"UNION SELECT [Issue ID], Replace(TokyoInternalActions.[Assigned To],',','') AS Assignee, UserRoleData.Role " & _
"FROM TokyoInternalActions INNER JOIN UserRoleData ON UserRoleData.[Resource Name]=Replace(TokyoInternalActions.[Assigned To],',','') " & _
"WHERE DateValue(TokyoInternalActions.[Entry Date])<=DateValue('" & weekStart & "') And DateValue(TokyoInternalActions.[Close Date])>DateValue('" & weekStart & "') AND UserRoleData.Role='" & RoleName & "';"

'open the recordset of all the data from SQL statement
eumCombinedRecordSet.Open mySQL2, , adOpenStatic

'get the record count
eumCombinedRecordSetCount = eumCombinedRecordSet.RecordCount

'*************************************************
'REPEAT CODE END
'**************************************************
etc......

etmRecordSet.Close
etmCombinedRecordSet.Close
Set etmRecordSet = Nothing
Set etmCombinedRecordSet = Nothing

eumRecordSet.Close
eumCombinedRecordSet.Close
Set eumRecordSet = Nothing
Set eumCombinedRecordSet = Nothing

etc....
 
Sort by date Sort by votes
I see two "REPEAT CODE" sections and the "etc." suggests that there may be more. Can you isolate where the error is happening a bit more precisely?
 
Upvote 0 Downvote
Yes, this is true. The code repeats 6 times with, as you probably noticed, different variables for each REPEAT CODE block (ie etmRecordSet vs. eumRecordSet). When the program gets to the 6th block I get the error on a this line:

eumRecordSet.Open mySQL, , adOpenStatic

So in my 6th block of repeating code it is actually:

pmRecordSet.Open mySQL, , adOpenStatic.

The strange thing is if I make this 6th block code the 5th block, by deleting one of the blocks, the code runs fine. It seems to not want to let me have more than 5 blocks of this repeating code

 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

Kennelbloke
  • Locked
  • Question
TransferSpreadsheet 3125 Error
Replies
5
Views
1K
Andrzejek
Andrzejek
Moss100
  • Locked
  • Question
Find error in currency feild 2
Replies
8
Views
984
Andrzejek
Andrzejek
Aurillius
  • Locked
  • Question
Convert Date/Time cell to a date and time in VBA
Replies
2
Views
779
Andrzejek
Andrzejek
Kim296
  • Locked
  • Question
Sending Gmail using vba Send error
Replies
6
Views
1K
strongm
strongm
EM1107
  • Locked
  • Question
calculated time difference. 1
Replies
2
Views
722
strongm
strongm

Part and Inventory Search

Sponsor

Back
Top