CurrentDb.OpenRecordset error (error 91 - Object variable or With block variable not set)

  • Thread starter Thread starter E. Kwong
  • Start date Start date
E

E. Kwong

I'm trying to extract the field names of a one-row table but keep gettning
"Error 91 : Object variable or With block variable not set". The code
snippet:

Dim rst As DAO.Recordset
Dim f As DAO.Field

On Error GoTo GetFieldNames_Err
Set rst = CurrentDb.OpenRecordset("table1")
For Each f In Rst.Fields
do somthing
Next
rst.Close

The error keeps hitting at the "set rst..." line. Really puzzling. I've
googled about the OpenRecordset method and have tried suggestions on some of
the posts but still to no avail.

I'm using Access 2003, and SQL Server 2000 tables. The codes are in a
Project (.adp). The References include DAO 3.6 object library and ADO 2.1
library.

Any insight greatly appreciated.
 
E. Kwong said:
I'm trying to extract the field names of a one-row table but keep gettning
"Error 91 : Object variable or With block variable not set". The code
snippet:

Dim rst As DAO.Recordset
Dim f As DAO.Field

On Error GoTo GetFieldNames_Err
Set rst = CurrentDb.OpenRecordset("table1")
For Each f In Rst.Fields
do somthing
Next
rst.Close

The error keeps hitting at the "set rst..." line. Really puzzling. I've
googled about the OpenRecordset method and have tried suggestions on some
of the posts but still to no avail.

I'm using Access 2003, and SQL Server 2000 tables. The codes are in a
Project (.adp). The References include DAO 3.6 object library and ADO
2.1 library.

Any insight greatly appreciated.
Click to expand...


I'm pretty sure that in an ADP, there is no "current database", only
CurrentProject.Connection CurrentDb, so CurrentDb returns Nothing. IIRC, in
an ADP you have to use ADO, and write code along these lines:

Dim rst As ADODB.Recordset
Dim f As ADODB.Field

On Error GoTo GetFieldNames_Err

Set rst = New ADODB.Recordset
rst.Open "Table1", CurrentProject.Connection

For Each f In Rst.Fields
' do something
Next
rst.Close
 
Dirk Goldgar said:
E. Kwong said:
I'm trying to extract the field names of a one-row table but keep
gettning "Error 91 : Object variable or With block variable not set".
The code snippet:

Dim rst As DAO.Recordset
Dim f As DAO.Field

On Error GoTo GetFieldNames_Err
Set rst = CurrentDb.OpenRecordset("table1")
For Each f In Rst.Fields
do somthing
Next
rst.Close

The error keeps hitting at the "set rst..." line. Really puzzling.
I've googled about the OpenRecordset method and have tried suggestions on
some of the posts but still to no avail.

I'm using Access 2003, and SQL Server 2000 tables. The codes are in a
Project (.adp). The References include DAO 3.6 object library and ADO
2.1 library.

Any insight greatly appreciated.
Click to expand...


I'm pretty sure that in an ADP, there is no "current database", only
CurrentProject.Connection CurrentDb, [...]
Click to expand...

To correct my typo, that should read:

I'm pretty sure that in an ADP, there is no "current database", only
CurrentProject.Connection, [...]
 
Thanks. Will try.


Dirk Goldgar said:
I'm pretty sure that in an ADP, there is no "current database", only
CurrentProject.Connection CurrentDb, so CurrentDb returns Nothing. IIRC,
in an ADP you have to use ADO, and write code along these lines:

Dim rst As ADODB.Recordset
Dim f As ADODB.Field

On Error GoTo GetFieldNames_Err

Set rst = New ADODB.Recordset
rst.Open "Table1", CurrentProject.Connection

For Each f In Rst.Fields
' do something
Next
rst.Close

--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)
Click to expand...
 
Back
Top