J
J. Adams
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..
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..