Solved

Can't define database object in code

Posted on 2003-11-28
14
608 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
Comment Utility
try:

DIM dbs 'AS Database

or

DIM dbs AS DAO.Database
0
 
LVL 33

Expert Comment

by:Mike Eghtebas
Comment Utility
This might help also:

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

Mike
0
 
LVL 9

Expert Comment

by:svenkarlsen
Comment Utility
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
 
LVL 7

Accepted Solution

by:
wsteegmans earned 250 total points
Comment Utility
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
Comment Utility
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
Comment Utility
Dim dbs as dao.database

set dbs = CurrentDb

then you can use it...

remember

set dbs = nothing

at the end of your code.
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 7

Expert Comment

by:wsteegmans
Comment Utility
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
Comment Utility
0
 
LVL 9

Expert Comment

by:ornicar
Comment Utility
----------------------------------------------------------------------------------------
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Recommendation changed on re-read.  
Thanx Andrew, you're right on this one.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

762 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

6 Experts available now in Live!

Get 1:1 Help Now