|
|
|
Need help connecting to DB |
|---|
| message from J. Adams on 31 May 2004 |
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..
|
| Jeff Boyce replied to J. Adams on 31 May 2004 |
Access 2000 (and 2002), by default, have NO reference set to the DAO object,
instead pointing to ADO. Both of these have "database" objects, but, if I
recall, the ADO object does not have a "Recordset".
Open a code module, select Tools & References, and check the MS DAO 3.61
object. Move it above the ADO reference, or uncheck that reference if you
aren't using ADO.
And for the future, any time you have to define objects that might be in
both, fully qualify which one, as in:
Dim db As DAO.Database
Dim rs As DAO.Recordset
Of course, this will only work if you have a reference to DAO!
|
| Douglas J. Steele replied to Jeff Boyce on 31 May 2004 |
Other way around, Jeff. There's no Database object in ADO, but there's a
Recordset object in both.
|
| Jeff Boyce replied to Douglas J. Steele on 1 Jun 2004 |
Thanks, Doug!
<scratch, scratch, scratch ... I knew something was different, just couldn't
recall which!>
Jeff Boyce
<Access MVP>
|
| J. Adams replied to Jeff Boyce on 31 May 2004 |
set to the DAO object,
objects, but, if I
the MS DAO 3.61
that reference if you
that might be in
to DAO!
using ADO on future projects to avoid the hastle..
|
| Douglas J. Steele replied to J. Adams on 31 May 2004 |
DAO is still the better method if you're strictly going against a Jet
database (an MDB file).
|
| Tony Toews replied to J. Adams on 31 May 2004 |
Not at all. If you're comfortable with DAO stay with it.
Tony
|
|
