provider is not cabable of performing requesting operation

[PeasNCarrots]

I get an error message saying provider is not cabable of performing requesting operation. This code works perfectly in another database.

I checked references, so that is not it.

Here is the line of Code it breaks on.
cat.Procedures.Delete "ptQ_Gss_Preload_" & arrDSN(intArr)


Complete Code:
Private Sub GetGSSData()

Dim conn As ADODB.Connection
Dim rstPreloads As ADODB.Recordset
Dim strSQL, strSQL1 As String
Dim arrDSN(1) As String
Dim strDate As String
Dim intArr As Integer
Dim cat As ADOX.Catalog
Dim cmd As New ADODB.Command

'Set variables
Set rstPreloads = New ADODB.Recordset
Set conn = New ADODB.Connection

'Get Date from form
strDate = Forms!frmGetArc.AuditDateA

'Add DSN Names to Array
arrDSN(0) = "Chema" 'Chelmsford Server
arrDSN(1) = "Wor" 'Worcester Server

'SQL String for GSS Data
rstPreloads.Open "T_SLICS", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly, adCmdTableDirect

'Get Preloads for Slics in District 36
Do Until rstPreloads.EOF
strSQL1 = "(TLDSEU_CUR.DTN_OGZ_NR) = '" & rstPreloads.Fields("SLIC").Value & "' OR " & strSQL1
rstPreloads.MoveNext
Loop

rstPreloads.Close

'Remove the trailing OR
strSQL1 = Left(strSQL1, Len(strSQL1) - 3)



strSQL = "SELECT TLDSEU_CUR.OUB_SRT_DT, TLDSEU_CUR.ORG_OGZ_NR, TOUOWSN_CUR.PKG_TCK_NR," _
& " TOUOWSN_CUR.USR_NR, TTYPSRT_DCO.SRT_TYP_MNM_TE, TOUOWSN_CUR.SRT_TYP_CD," _
& " TLDSEU_CUR.DTN_OGZ_NR, SubString([PKG_TCK_NR],3,6) As ShipNum" _
& " FROM TOUOWSN_CUR INNER JOIN TLDSEU_CUR ON TOUOWSN_CUR.LD_REF_NR = TLDSEU_CUR.LD_REF_NR" _
& " INNER JOIN TTYPSRT_DCO ON TLDSEU_CUR.DTN_SRT_TYP_CD = TTYPSRT_DCO.SRT_TYP_CD" _
& " WHERE ((TLDSEU_CUR.OUB_SRT_DT) = '" & strDate & "') AND (" & strSQL1 & ")" _
& " AND (TLDSEU_CUR.DTN_SRT_TYP_CD = '01' OR TLDSEU_CUR.DTN_SRT_TYP_CD = '06')"


'Create new passthrough queries for GSS data
For intArr = 0 To 1

Set cat = New ADOX.Catalog
Set cmd = New ADODB.Command
cat.ActiveConnection = CurrentProject.Connection
Set cmd.ActiveConnection = cat.ActiveConnection

cmd.CommandText = strSQL
cmd.Properties("Jet OLEDB:ODBC Pass-Through Statement") = True


'connection for passthrough query
cmd.Properties _
("Jet OLEDBass Through Query Connect String") = _
"ODBC;DSN=" & arrDSN(intArr) & ";UID=ucr_readonly;PWD=readonly;DATABASE=D685UCR;"

'Add/delete queries to database
'cat.Procedures.Delete "ptQ_Gss_Preload_" & arrDSN(intArr)
cat.Procedures.Append "ptQ_Gss_Preload_" & arrDSN(intArr), cmd

'Clear memory of variables before creating new query
Set cat = Nothing
Set cmd = Nothing
Set rstPreloads = Nothing
Set conn = Nothing

Next

End Sub

 

[kjv1611]

For the line

cat.Procedures.Delete "ptQ_Gss_Preload_" & arrDSN(intArr)

Have you checked to see if that particular object actually exists? What is "cat"? Is that a user-defined object/class variable?

Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[PeasNCarrots]

cat is a variable

Dim cat As ADOX.Catalog

The object does exist, and if it doesn't I commented out the delete part of it.

Something else is wrong and I cannot figure it out for the life of me. And I am usually quite good in figuring this stuff out.

 

[kjv1611]

Ooops, what I actually meant about the "does it exist" part was does this part exist:
"ptQ_Gss_Preload_" & arrDSN(intArr)
??

Just a guess.. b/c if you want to delete a table that doesn't exist, you'll get an error.


Stephen
"Jesus saith unto him, I am the way, the truth, and the life:
no man cometh unto the Father, but by me." John 14:6 KJV

 

[PeasNCarrots]

Yes, I am aware of that. I wish it was that easy! Still cannot figure it out. It has to be a settings issue somewhere because I tried to exact code posted from Microsoft and it does not work. However when I use it in a new database on the same computer it does work. I also tried importing objects from that database into a new one and that did not work.

ANY MASTERS, EXPERTS PLEASE HELP!