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

[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.

 

[Dirk Goldgar]

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.

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]

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.

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

To correct my typo, that should read:

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

 

[E. Kwong]

Thanks. Will try.

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)