Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Can't define database object in code

Posted on 2003-11-28
14
Medium Priority
?
615 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +4
14 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

721 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