Problem with Unspecified Error in Access

[aperros]

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

[CreganTur]

How is value being passed into the dbpath variable? The code you posted doesn't show that.

[aperros]

It is being passed as a string. The following is one value of dbpath:


D:\UHS_Post_Processing\Combined_Forces.mdb

[CreganTur]

BTW, welcome to the forum- it's always good to see new members.

The only thing that looks out of place to me right now is the final semi-colon that you place at the end of your connection string. You don' tneed it, because you aren't sending any more parameters as a part of your connection string. So try this instead:

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & dbpath

See if that fixes your problem.

HTH:thumb

[aperros]

Thanks, I will try that and let you know how it goes

[OBP]

I note that you do not have any Error Trapping in your Code, you never know it might just give you more information and pinpoint where the error occurs.

[aperros]

Can you please explain what error trapping consists of? I have never heard that term used before. Thanks!

[OBP]

After your last Dim statement add the following code

on error goto errorcatch

and before the End Sub or End Function add

exit sub
errorcatch:
MsgBox Err.Description

[aperros]

The error occurred again at the following line:

cn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & dbpath

[CreganTur]

Follow Tony's advice and add some error trapping into your code, and let us know what error number and message you get- that'll help us narrow down what's going on.

[OBP]

Randy, why would they need the Jet Connection to create and run a Query, unless they are outside Access?

[CreganTur]

Randy, why would they need the Jet Connection to create and run a Query, unless they are outside Access?

The only reason I can think of is you could create the query in the target external database and then use it to quickly fill a recordset by creating an ADO command that points to the created query and then executing it when setting your ADO recordset.

I've never done that before, but I know how it's done. I can only see the value of it from a security standpoint. You could write or overwrite a query in the target Access database whenever parameters change in one method, and then create your recordset in a separate method.

[CreganTur]

@aperros

Here's some example code that does exactly what you're trying to do, but in a slightly different way. If you're still stuck, then I would suggest rewriting this code and see if it works for you:

Sub CreateSelectQueryADO()

Dim cat As ADOX.Catalog
Dim cmd As ADODB.Command
Dim strPath As String
Dim strSQL As String
Dim strQryName As String

On Error GoTo ErrorHandler:

'assign values to string variabled
strPath = "C:\Acc07_ByExample\Northwind.mdb"
strSQL = "SELECT Employees.* FROM Employees WHERE Employees.City = 'Redmond'"
strQryName = "Redmond Employees"

'open the catalog
Set cat = New ADOX.Catalog
cat.ActiveConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath

'create query based on specified SELECT statement
Set cmd = New ADODB.Command
cmd.CommandText = strSQL

'add new query to the database
cat.Views.Append strQryName, cmd
MsgBox "The procedure completed successfully."

ExitHere:
Set cmd = Nothing
Set cat = Nothing
Exit Sub

ErrorHandler:
If InStr(Err.Description, "already exists") Then
cat.Views.Delete strQryName
Resume
Else
MsgBox Err.Number & ": " & Err.Description
Resume ExitHere
End If

End Sub