Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Can't define database object in code

Posted on 2003-11-28
14
Medium Priority
?
617 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
13 Comments
 
LVL 34

Expert Comment

by:Mike Eghtebas
ID: 9840462
try:

DIM dbs 'AS Database

or

DIM dbs AS DAO.Database
0
 
LVL 34

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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
LVL 7

Accepted Solution

by:
wsteegmans earned 1000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

877 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