Microsoft Jet Error VBA Excel

  • Thread starter Thread starter anhtuando
  • Start date Start date
A

anhtuando

Hello,

I currently have an Excel file that uses a lot of VBA. I'm basically
using ADO to run a query. Here's the odd part, we're working on three
different computers with different versions of Excel.

1) Excel 2003 SP2 - Windows XP SP1
2) Excel 2003 - Windows XP (not sure of service Pack)
3) Excel 2002 - Windows XP SP2

Here's the thing, the file works fine on 1 & 3. However, on the Excel
2003 without SP2, it does not. I get the following error:

"Microsoft jet database engine could not find object 'MyNamedRange'"

Any ideas would be much appreciated.

Thanks in advance!
 
Here is the code. The error happens on the "Call empRS.Open(..." line.

Dim empRS As ADODB.Recordset
Dim empSQL As String

curConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & ThisWorkbook.FullName & ";" & _
"Extended Properties=Excel 8.0;"

Set empRS = New ADODB.Recordset
empSQL = "SELECT DISTINCT " & selColNm & ", " & selColEm & " " & _
"FROM ChartOfAccounts " & _
"WHERE ENTITY = 1;"

Call empRS.Open(empSQL, curConnectionString, adOpenForwardOnly, _
adLockReadOnly, CommandTypeEnum.adCmdText)

Do While Not empRS.EOF
msgbox empRS.Fields(1).Value
empRS.MoveNext
Loop
 
Back
Top