Solved

Can't define database object in code

Posted on 2003-11-28
14
610 Views
Last Modified: 2011-09-20
Hi:

     When I attempt to create a database object like this:

        DIM dbs AS Database

    and run the code I get the message "User defined-type not defined".  In fact, when I type to the "AS" the pop-up dropdown menu that appears does not even contain "Database" as a choice.  I have to be able to define a database object so I can do SELECT queries.  Can anyone tell me why I can't define this object?.  Thanks.

Steve
0
Comment
Question by:steveRosenberg
  • 4
  • 2
  • 2
  • +4
14 Comments
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9840462
try:

DIM dbs 'AS Database

or

DIM dbs AS DAO.Database
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
ID: 9840472
This might help also:

Dim rs 'As DAO.RecordSet
Set rs=CurrentDB.OpenRecordSet("Table1")

Mike
0
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9840477
Hi steveRosenberg,

Egtebas has provided you with the correct answer.

If you want to run a query under ADO, you may use the syntax:

StrVar = CurrentProject.Connection.Execute(SQL_Str)



Kind regards,
Sven
0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 7

Accepted Solution

by:
wsteegmans earned 250 total points
ID: 9840644
Hi steveRosenberg,

If you're using Access 2000 or higher, the default library for Database objects is ADO (ActiveX Data Objects). It's the 'new' approach to write DB-code.

Howerver, you use
> DIM dbs AS Database

This is the 'old' way, also called DAO (Data Access Objects). But, if you're using Access 2000 or higher, this DAO is not the default any more ...

However, if you still want to use it, add it to your references:
From your Visual Basic Editor, choose menu: Tools -> References (Check Microsoft DAO 3.6 Object Library)
Your code will work now as
DIM dbs AS Database
DIM dbs AS DAO.Database

If interested, a nice article how to migrate from DAO to ADO ...
Porting DAO Code to ADO with the Microsoft Jet Provider
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dndao/html/daotoado.asp

Regards!
0
 
LVL 28

Expert Comment

by:TextReport
ID: 9841712
Once you have selected the DAO reference to make it the default you need to promote the DAO Reference to above all other references that have the say object types, ei ADO.

If you want DAO as you default make it 3rd in the list then you do not need to declare it with the DAO. in front.

Cheers, Andrew
0
 
LVL 3

Expert Comment

by:DMurray
ID: 9841743
Dim dbs as dao.database

set dbs = CurrentDb

then you can use it...

remember

set dbs = nothing

at the end of your code.
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9841846
TextReport is right, however there won't be a problem declaring your database (ADO hasn't such a Database Class).

Maybe, you can even better remove the Microsoft ActiveX Data Objects Library, if you just want to use DAO.

Event better, use always in your declarations the library ... This means, use
'DIM dbs AS DAO.Database' instead of 'DIM dbs AS Database'.
So, you know perfectly what you're doing, and it works always (no matter what the priority of your references is).

Regards!
0
 
LVL 32

Expert Comment

by:jadedata
ID: 9852135
0
 
LVL 9

Expert Comment

by:ornicar
ID: 10075411
----------------------------------------------------------------------------------------
This question has been abandoned and needs to be finalized.
 You can accept an answer, split the points, or get a refund (information at http:/help.jsp#hs5)
  If you need a moderator to help you, post a question at Community Support (http:/Community_Support/)

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

ornicar
Cleanup Volunteer

---------------------------------------------------------------------------------------------
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10105816
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: eghtebas {http:#9840472}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

jadedata
EE Cleanup Volunteer
0
 
LVL 28

Expert Comment

by:TextReport
ID: 10105911
Jack, I would suggest that wsteegmans had the correct answer as the answer provided didn't just "get round" the issue but also explained it.

Cheers, Andrew
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10106062
No comment has been added lately, so it's time to clean up this TA.
I will leave the following recommendation for this question in the Cleanup topic area:

Accept: wsteegmans {http:#9840644}

Please leave any comments here within the next seven days.
PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER!

jadedata
EE Cleanup Volunteer
0
 
LVL 32

Expert Comment

by:jadedata
ID: 10106089
Recommendation changed on re-read.  
Thanx Andrew, you're right on this one.
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

815 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now