aperros
10-26-2009, 03:49 PM
I have a problem with some access vba code. I am processing a significant amount of data and I am using the following code to create a query in access using vba.
The error I get randomly is the following. Sometimes when I run the code I won't even get the error.
Run-time error' -2147467259(80004005)':
Unspecified Error
The error occurs on the following line:
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & dbpath & ";"
Dim cn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim sSQL As String
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & dbpath & ";"
sSQL = "SELECT CDbl([Static_Forces].[Area]) AS Area, IIf(Right([OutputCase],5)='_Th A',Mid([Outputcase],1,Len([OutputCase])-5),"
sSQL = sSQL & "IIf(Right([OutputCase],5)='_Th G',Mid([Outputcase],1,Len([OutputCase])-5),[OutputCase])) AS Combos, CDbl(Static_Forces.Joint) as Joint, "
sSQL = sSQL & "Static_Forces.F11, Static_Forces.F22, Static_Forces.F12, Static_Forces.M11, Static_Forces.M22, Static_Forces.M12, Static_Forces.V13, "
sSQL = sSQL & "Static_Forces.V23, IIf(Right([OutputCase],5)='_Th A',Right([OutputCase],5),IIf(Right([OutputCase],5)='_Th G',Right([OutputCase],5))) AS TH "
sSQL = sSQL & "FROM Static_Forces "
sSQL = sSQL & "WHERE (((IIf(Right([OutputCase],5)='_Th A',Mid([Outputcase],1,Len([OutputCase])-5),IIf(Right([OutputCase],5)='_Th G',Mid([Outputcase],1,"
sSQL = sSQL & "Len([OutputCase])-5),[OutputCase]))) Like 'C*') AND ((CDbl(IIf(Right(Mid([Static_Forces].[OutputCase],2,14),5) Like '_Th*',(Mid([Static_Forces]."
sSQL = sSQL & "[OutputCase],2,Len([Static_Forces].[OutputCase])-6)),(Mid([Static_Forces].[OutputCase],2,Len([Static_Forces].[OutputCase])-1)))))>=2000 And "
sSQL = sSQL & "(CDbl(IIf(Right(Mid([Static_Forces].[OutputCase],2,14),5) Like '_Th*',(Mid([Static_Forces].[OutputCase],2,Len([Static_Forces].[OutputCase])-6)),"
sSQL = sSQL & "(Mid([Static_Forces].[OutputCase],2,Len([Static_Forces].[OutputCase])-1)))))<=2999));"
Set cat = New ADOX.Catalog
Set cmd = New ADODB.Command
cat.ActiveConnection = cn
cmd.CommandText = sSQL
cat.Views.Append "Static_C2XX", cmd
Set cat = Nothing
Set cmd = Nothing
cn.Close
Set cn = Nothing
thanks for your help
Edited 27-Oct-09 by geekgirlau. Reason: insert vba tags
The error I get randomly is the following. Sometimes when I run the code I won't even get the error.
Run-time error' -2147467259(80004005)':
Unspecified Error
The error occurs on the following line:
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & dbpath & ";"
Dim cn As ADODB.Connection
Dim cat As New ADOX.Catalog
Dim cmd As New ADODB.Command
Dim sSQL As String
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & dbpath & ";"
sSQL = "SELECT CDbl([Static_Forces].[Area]) AS Area, IIf(Right([OutputCase],5)='_Th A',Mid([Outputcase],1,Len([OutputCase])-5),"
sSQL = sSQL & "IIf(Right([OutputCase],5)='_Th G',Mid([Outputcase],1,Len([OutputCase])-5),[OutputCase])) AS Combos, CDbl(Static_Forces.Joint) as Joint, "
sSQL = sSQL & "Static_Forces.F11, Static_Forces.F22, Static_Forces.F12, Static_Forces.M11, Static_Forces.M22, Static_Forces.M12, Static_Forces.V13, "
sSQL = sSQL & "Static_Forces.V23, IIf(Right([OutputCase],5)='_Th A',Right([OutputCase],5),IIf(Right([OutputCase],5)='_Th G',Right([OutputCase],5))) AS TH "
sSQL = sSQL & "FROM Static_Forces "
sSQL = sSQL & "WHERE (((IIf(Right([OutputCase],5)='_Th A',Mid([Outputcase],1,Len([OutputCase])-5),IIf(Right([OutputCase],5)='_Th G',Mid([Outputcase],1,"
sSQL = sSQL & "Len([OutputCase])-5),[OutputCase]))) Like 'C*') AND ((CDbl(IIf(Right(Mid([Static_Forces].[OutputCase],2,14),5) Like '_Th*',(Mid([Static_Forces]."
sSQL = sSQL & "[OutputCase],2,Len([Static_Forces].[OutputCase])-6)),(Mid([Static_Forces].[OutputCase],2,Len([Static_Forces].[OutputCase])-1)))))>=2000 And "
sSQL = sSQL & "(CDbl(IIf(Right(Mid([Static_Forces].[OutputCase],2,14),5) Like '_Th*',(Mid([Static_Forces].[OutputCase],2,Len([Static_Forces].[OutputCase])-6)),"
sSQL = sSQL & "(Mid([Static_Forces].[OutputCase],2,Len([Static_Forces].[OutputCase])-1)))))<=2999));"
Set cat = New ADOX.Catalog
Set cmd = New ADODB.Command
cat.ActiveConnection = cn
cmd.CommandText = sSQL
cat.Views.Append "Static_C2XX", cmd
Set cat = Nothing
Set cmd = Nothing
cn.Close
Set cn = Nothing
thanks for your help
Edited 27-Oct-09 by geekgirlau. Reason: insert vba tags