We just moved from Office 97 to Office 2002. We have not
yet purchased the appropriate reference material so I am
stuck. In Office 97 I had an Excel file that gets data
from an Access database using the DAO - OpenDatabase
method.

Anyway, the code I used in 97 to open the database and
recordset so I can get the data I need does not seem to
work in 2002. I keep getting error "429", "Active X
component can't create object".

I looked through the available help files and the only
thing I could see that was different from Excel 97 was
that I needed to create a Workspace object. I tried using
the example from the help file but I still keep getting
that same error. I tried adding references to Access 10
Library but that won't work either. Below is a sample of
my code. Any help I could get on this would be greatly
appreciated.

Before I changed any code it would crap out at the
OpenDatabase statement. After I altered the code to
include the creation of a workspace it craps out at the
CreateWorkspace line..

Here it is, Note: some of the code has been changed to
protect the innocent

Private Function GetDatabaseData() As Boolean
'purpose: gets data from database
'accepts: none
'returns: true - if data is imported correctly
'declarations
Dim DB1 As Database, lsDBName As String, rst As
Recordset, lsSql As String
Dim wrkJet As Workspace

On Error GoTo ErrorHandler:

'equate fcn value
GetDatabaseData = False

'open database
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
lsDBName = "Y:\Shared\Test1\Test2\Test3.mdb"
Set DB1 = wrkJet.OpenDatabase(lsDBName)

'set sql statement, open recordset
lsSql = "SELECT yada yada yada
Set rst = DB1.OpenRecordset(lsSql, dbOpenSnapshot)

'dump recordset onto spreadsheet
'code to dump and massage and close recordset placed here

'close database variables, equate fcn value
rst.Close
DB1.Close
wrkJet.Close

GetDatabaseData = True
Exit Function

'error handling procedures
ErrorHandler:
'Code to handle error placed here

End Function

Thanking you in advance..
..

Hi
have you checked that a reference to this object library is set
('Tools - References' in the VBA editor)

--
Regards
Frank Kabel
Frankfurt, Germany


J.Adams wrote:
We just moved from Office 97 to Office 2002. We have not
yet purchased the appropriate reference material so I am
stuck. In Office 97 I had an Excel file that gets data
from an Access database using the DAO - OpenDatabase
method.

Anyway, the code I used in 97 to open the database and
recordset so I can get the data I need does not seem to
work in 2002. I keep getting error "429", "Active X
component can't create object".

I looked through the available help files and the only
thing I could see that was different from Excel 97 was
that I needed to create a Workspace object. I tried using
the example from the help file but I still keep getting
that same error. I tried adding references to Access 10
Library but that won't work either. Below is a sample of
my code. Any help I could get on this would be greatly
appreciated.

Before I changed any code it would crap out at the
OpenDatabase statement. After I altered the code to
include the creation of a workspace it craps out at the
CreateWorkspace line..

Here it is, Note: some of the code has been changed to
protect the innocent

Private Function GetDatabaseData() As Boolean
'purpose: gets data from database
'accepts: none
'returns: true - if data is imported correctly
'declarations
Dim DB1 As Database, lsDBName As String, rst As
Recordset, lsSql As String
Dim wrkJet As Workspace

On Error GoTo ErrorHandler:

'equate fcn value
GetDatabaseData = False

'open database
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
lsDBName = "Y:\Shared\Test1\Test2\Test3.mdb"
Set DB1 = wrkJet.OpenDatabase(lsDBName)

'set sql statement, open recordset
lsSql = "SELECT yada yada yada
Set rst = DB1.OpenRecordset(lsSql, dbOpenSnapshot)

'dump recordset onto spreadsheet
'code to dump and massage and close recordset placed here

'close database variables, equate fcn value
rst.Close
DB1.Close
wrkJet.Close

GetDatabaseData = True
Exit Function

'error handling procedures
ErrorHandler:
'Code to handle error placed here

End Function

Thanking you in advance..
.

-----Original Message-----
Hi
have you checked that a reference to this object library
is set
('Tools - References' in the VBA editor)

--
Regards
Frank Kabel
Frankfurt, Germany


J.Adams wrote:
We just moved from Office 97 to Office 2002. We have
not
yet purchased the appropriate reference material so I
am
stuck. In Office 97 I had an Excel file that gets data
from an Access database using the DAO - OpenDatabase
method.

Anyway, the code I used in 97 to open the database and
recordset so I can get the data I need does not seem to
work in 2002. I keep getting error "429", "Active X
component can't create object".

I looked through the available help files and the only
thing I could see that was different from Excel 97 was
that I needed to create a Workspace object. I tried
using
the example from the help file but I still keep getting
that same error. I tried adding references to Access 10
Library but that won't work either. Below is a sample
of
my code. Any help I could get on this would be greatly
appreciated.

Before I changed any code it would crap out at the
OpenDatabase statement. After I altered the code to
include the creation of a workspace it craps out at the
CreateWorkspace line..

Here it is, Note: some of the code has been changed to
protect the innocent

Private Function GetDatabaseData() As Boolean
'purpose: gets data from database
'accepts: none
'returns: true - if data is imported correctly
'declarations
Dim DB1 As Database, lsDBName As String, rst As
Recordset, lsSql As String
Dim wrkJet As Workspace

On Error GoTo ErrorHandler:

'equate fcn value
GetDatabaseData = False

'open database
Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
lsDBName = "Y:\Shared\Test1\Test2\Test3.mdb"
Set DB1 = wrkJet.OpenDatabase(lsDBName)

'set sql statement, open recordset
lsSql = "SELECT yada yada yada
Set rst = DB1.OpenRecordset(lsSql, dbOpenSnapshot)

'dump recordset onto spreadsheet
'code to dump and massage and close recordset placed
here

'close database variables, equate fcn value
rst.Close
DB1.Close
wrkJet.Close

GetDatabaseData = True
Exit Function

'error handling procedures
ErrorHandler:
'Code to handle error placed here

End Function

Thanking you in advance..
.

.
Hi,

I got help from a another newsgroup. The reference was
set to DAO 3.51 I switched it to DAO 3.6 and moved it
above the ADO references. I also had to fully qualify my
objects like so:

Dim db as DAO.Database
Dim rst as DAO.Recordset

After that everything worked fine.

Thanks for your response Frank. I appreciate it..

J. Adams wrote:
-----Original Message-----
[...]

I got help from a another newsgroup. The reference was
set to DAO 3.51 I switched it to DAO 3.6 and moved it
above the ADO references. I also had to fully qualify my
objects like so:

Dim db as DAO.Database
Dim rst as DAO.Recordset

After that everything worked fine.

Thanks for your response Frank. I appreciate it..

hi
though this is definetly not my area of expertis you should consider
'late binding' to prevent such errors in the future. I'm quite sure
someone else could post some good references on this topic (Dave, Bob,
Tom, etc.)?

Frank