asked on

Internal OLE Automation error?

Hi,

This may be too simple, but I just don't know why I got error message
Internal OLE Automation error
by executing the following code:

Private Sub Command1_Click()
Set objCon = New ADODB.Connection
Dim objRec As New ADODB.Recordset
objCon.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\test\test.mdb"
objRec.Open "select * from test where [First Name] Like '*m*'", objCon, adOpenKeyset
End Sub

If I replace "Like" to "=", there will be no error.

Thank you in advance

ASKER CERTIFIED SOLUTION

wsh2

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

learn

ASKER

Hi,

Thanks a lot for your help.

So, we need to use "%" instead of "*"? In DAO it should be "*".....why?
You didn't put .MoveFirst in your code, it that the defualt setting?

Cheers.

learn

ASKER

Hi,

I have just ried your code. Unfortunately, I still got the same error message!!
Can you help?

Éric Moreau

DAO and ADO are using different wildcards!

learn

ASKER

To emoreau,

Really?! However, both use "*" in Select clause...and do you mean '%m%' can represent 'smith' in ADO???????????

wsh2

Do a VB Menu -> Project -> References.. and make sure "Microsoft ActiveX Data Object 2.1 Library" (or 2.5) is checked.

---------------------------------------
Prior to doing any troubleshooting (particularily OLE automation type) make sure YOU are up to a current level of VB before proceeding.

If you are not at LEAST VB Service Pack 3 (SP3).. I STRONGLY suggest you upgrade to it. SP3 is VERY stable, well proven and is still widely in use amongst VB developers. You can get it here:

http://msdn.microsoft.com/vstudio/sp/vs6sp3/default.asp

The most current VB Service Pack level is SP4. SP4 is still relatively new, but so far has proven to be worthy. SP4 fixes more bugz.. and is much more compliant with Win2000, but it also is a much larger download, requires MDAC v2.5 and has a predisposition to requiring IE5 on your development machine to achieve full functionality. You can get SP4 here:

http://msdn.microsoft.com/vstudio/sp/vs6sp4/default.asp

Éric Moreau

The * in the Select clause is a SQL standard meaning all fields. Different wildcards are used with the Like operator.

learn

ASKER

Hi wsh2,

I am using VB5 in Windows98 and have installed Vs97_sp3. Is that OK?

Éric Moreau

Have you tried another field (without space) as I said in the other question?

wsh2

Vs97_sp3.. is fine.. <smile>.

And the code I provided above runs well. Just as a test.. run this.. k?

---------------------------------------
1. Start a New Standard.Exe Project.
2. Set a reference (VB Menu -> Project -> References to "Microsoft ActiveX Data Objects 2.1 Library".
3. Add a ListBox (List1) to Form1.
4. Copy/Paste the following code into the Form1 code window.
5. Change the Datasource to your Sample Nwind (Northwind) sample DB file.
6. Press F5 to run. All Employee names containing the letter v will appear in the ListBox.. (Leverling + Davallo)

<----- Code Begin ----->

Option Explicit

Private Sub Form_Load()

Dim objCon As ADODB.Connection
Set objCon = New ADODB.Connection
With objCon
.Provider = "Microsoft.Jet.OLEDB.4.0"
.ConnectionString = "c:\MyPath\NWind.MDB" ' <-- CHANGE ME
.Open
End With

Dim strSQL As String
strSQL = _
"SELECT * " & _
"FROM [employees] " & _
"WHERE [LastName] LIKE '%v%' "

Dim objRec As ADODB.Recordset
Set objRec = New ADODB.Recordset
With objRec
.ActiveConnection = objCon
.CursorType = adOpenKeyset
.Open (strSQL)
Do Until .EOF
List1.AddItem ![LastName]
.MoveNext
Loop
.Close
End With

objCon.Close
Set objRec = Nothing
Set objCon = Nothing

End Sub

<----- Code End ----->

wsh2

learn..
One other thing that can cause OLE automation errors is if you have NULL values in your database table. Check your test table to see if this may be the case.