473,836 Members | 1,464 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

A97: Dim dbs As Database yields error "user-defined type not defined"

MLH
I copied the following code snippet from A97 HELP. Am
getting an error at compile time suggesting there's a problem
with the first line (compile error, user-defined type not defined).
It is likely that I've left something out. Doesn't seem to like Dim
dbs as Database - that's what's hi-lited after acknowledging the
error. Can you see anything wrong with that syntax?

Dim dbs As Database, rst As Recordset
Dim rstEmployees As Recordset, rstOrders As Recordset
Dim rstProducts As Recordset, strSQL As String

' Return reference to current database.
Set dbs = CurrentDb
' Create table-type Recordset object.
Set rstEmployees = dbs.OpenRecords et("Employees" , dbOpenTable)
' Create dynaset-type Recordset object.
Set rstOrders = dbs.OpenRecords et("Employees" , dbOpenDynaset)

' Create snapshot-type Recordset object.
Set rstProducts = dbs.OpenRecords et("Products", dbOpenSnapshot)
' Print value of Updatable property for each Recordset object.
For Each rst In dbs.Recordsets
Debug.Print rst.Name; " "; rst.Updatable
Next rst
' Free all object variables.
rstEmployees.Cl ose
rstOrders.Close
rstProducts.Clo se
Set dbs = Nothing

How to find the example code (above) in A97 HELP...
1) Click HELP, click Contents and Index, type "dao"
2) dbl-clik Recordsets In the larger field w/ vertical scroll bar
3) Pick the 2nd choice (Recordsets Collection (DAO)
4) Click Example. 3 topics are found. Choos the last one entitled
"Recordset Object, Recordsets Collection Example (Microsoft
Access)
Nov 13 '05 #1
11 9869
MLH wrote:
I copied the following code snippet from A97 HELP. Am
getting an error at compile time suggesting there's a problem
with the first line (compile error, user-defined type not defined).
It is likely that I've left something out. Doesn't seem to like Dim
dbs as Database - that's what's hi-lited after acknowledging the
error. Can you see anything wrong with that syntax?

Dim dbs As Database, rst As Recordset
Dim rstEmployees As Recordset, rstOrders As Recordset
Dim rstProducts As Recordset, strSQL As String


That's weird. Is the above pasted from your code? I get exactly the
kind of error you mention, but only when I mispell something like this
example where "Integer" is misspelled: "dim int1 as intger".
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #2
MLH
<snip>

That's weird. Is the above pasted from your code? I get exactly the
kind of error you mention, but only when I mispell something like this
example where "Integer" is misspelled: "dim int1 as intger".


Yes. And I copied the example straight from A97 HELP, pasting it
behind a command button.

How to find the example code in A97 HELP...
1) Click HELP, click Contents and Index, type "dao"
2) dbl-clik Recordsets In the larger field w/ vertical scroll bar
3) Pick the 2nd choice (Recordsets Collection (DAO)
4) Click Example. 3 topics are found. Choos the last one entitled
"Recordset Object, Recordsets Collection Example (Microsoft
Access)
Nov 13 '05 #3
Tim Marshall wrote:
The standard default libraries, in other words... Do you have anything
else in your references? I wonder if anyting is missing or if there's
something else there that might be interfering. Do you have a reference
to an ADO library?


Looking at your other post, if the problem with your original post here
originated AFTER the problem you describe, I suspect, that might be the
problem.

However, if you alter your code to specify that the recordset variables
are DAO, not ADO, ie,

Dim dbs As DAO.Database, rst As DAO.Recordset
Dim rstEmployees As DAO.Recordset, rstOrders As DAO.Recordset
Dim rstProducts As DAO.Recordset, strSQL As String

(I threw in the database variable as well, for good measure)

This will hopefully allow A97 to distinguish between DAO and ADO.

If the above does not work, I would concentrate on resolving the other
issue you bring up in your other thread and I'm certain the problem in
this thread will go away.

Unfortunately, I can't offer much in the other issue - I've always
tended to stay away from OCXs in my development work since first seeing
various cautions about them in cdma in the late 90s, before A2000 was
released. That's not to say they are not a good idea - there are loads
of developers who successfully use them, it's just that I am a mangler,
er, a manager in my organization and chose not to play around with them. 8)

Hope this was of some help...
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #4
MLH wrote:
Yes. And I copied the example straight from A97 HELP, pasting it
behind a command button.


I just copied exactly what you originally posted into a new Access 97
standard module (see below) and it compiled just fine.

Did you check your references? In a VBA window, Tools->References. I
have three references checked:

Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library

The standard default libraries, in other words... Do you have anything
else in your references? I wonder if anyting is missing or if there's
something else there that might be interfering. Do you have a reference
to an ADO library?

This is what compiled correctly in my Access 97 SR-2 installation:

Option Compare Database
Option Explicit

Sub whatever()

Dim dbs As Database, rst As Recordset
Dim rstEmployees As Recordset, rstOrders As Recordset
Dim rstProducts As Recordset, strSQL As String

' Return reference to current database.
Set dbs = CurrentDb
' Create table-type Recordset object.
Set rstEmployees = dbs.OpenRecords et("Employees" , dbOpenTable)
' Create dynaset-type Recordset object.
Set rstOrders = dbs.OpenRecords et("Employees" , dbOpenDynaset)

' Create snapshot-type Recordset object.
Set rstProducts = dbs.OpenRecords et("Products", dbOpenSnapshot)
' Print value of Updatable property for each Recordset object.
For Each rst In dbs.Recordsets
Debug.Print rst.Name; " "; rst.Updatable
Next rst
' Free all object variables.
rstEmployees.Cl ose
rstOrders.Close
rstProducts.Clo se
Set dbs = Nothing

End Sub

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me
Nov 13 '05 #5
MLH
>> Yes. And I copied the example straight from A97 HELP, pasting it
behind a command button.


I just copied exactly what you originally posted into a new Access 97
standard module (see below) and it compiled just fine.

Did you check your references? In a VBA window, Tools->References. I
have three references checked:

Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library

The standard default libraries, in other words... Do you have anything
else in your references? I wonder if anyting is missing or if there's
something else there that might be interfering. Do you have a reference
to an ADO library?

<snip>
Boy did I ever get flip-flopped. Here's the references I had checked
when I read your answer...
1) Visual Basic For Applications
2) Microsoft Access 8.0 Object Library
3) Microsoft ActiveX Data Objects 2.1 Library
4) DialerAX
5) OLE Automation

Not checked, but probably worthy of being so are
Find and Replace 8.0 and Microsoft DAO 3.51 Object Library.
Without changing ANYTHING, I'll add the DAO Object Library
first - just 2C if that corrects the issue in the O.P.

Think I should rid myself of numbers 3, 4 and 5 above?
Nov 13 '05 #6
MLH
>Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library


You win the prize. Somehow, the Microsoft DAO 3.51 Object Library
had been removed. I use DAO all the time in A97. I have no earthly
idea how it was removed yesterday. Putting a checkmark beside it
completely resolved the problem of the

Dim dbs as Database

compile-time error.

I just don't remember this ever being an issue with Access 2.0. Did
A2.0 have References that could be completely blown away behind
the scenes potentially rendering years of programming code nonworking?
I don't think so. I'm sure I would have encountered it back then. I'll
have to be careful about installing anybody's stuff, making sure that
I know what is done, enabling me to "undo" it after the fact, if
necessary.
Nov 13 '05 #7
MLH wrote:
You win the prize. Somehow, the Microsoft DAO 3.51 Object Library
had been removed. I use DAO all the time in A97. I have no earthly
Hurrah. But I've never had that happen. Funny too that the ADO
reference was set up as A97 never had ADO as a default reference. I bet
a zillion bucks it had something to do with that ocx.
Dim dbs as Database
I began disambiguating (the dao.recordset, dao.database) a number of
years ago after reading an article about it by Michka on his Trigeminal
site:

http://www.trigeminal.com/usenet/usenet026.asp?1033

It's probably a good idea to get into the habit, though I know not
everyone does. I go (probably needlessly) overboard with it, myself;
for example, instead of dim ooga as ComboBox I write dim ooga as
Access.Combobox/
I just don't remember this ever being an issue with Access 2.0. Did
A2.0 have References that could be completely blown away behind
the scenes potentially rendering years of programming code nonworking?
I don't think so.


I don't know. Applications with Access 2 I did involved macros and
weren't all that involved - I did an awful lot more with dBase III+ and
IV. I know you're having great difficulty adjusting to A97, but the
experienced developers here generally say it is far better a product
than 2.0. Indeed, in my first months of using A2003, I felt the same
way you do with respect to the new product, but have eventually grown
used to it.

I don't think stuff can get blown away behind the scenes though all that
easily. In your case, assuming it was this ocx installation that caused
your problem, it's a case of differing versions of Access and/or
possibly Office. One of the reasons I went to Access 2003 from A97 was
because my applications started malfunctioning on newer machines that
had elements of Office 2000 installed. Apparantly it's fairly easy to
muck up the dlls when you've later versions of Office installed on the
same machine with Access 97. Indeed, many people here will cry foul,
but I've had very inconsistent results with A97 installs on windows XP
machines, another reason for me to abandon my very much loved A97. Of
course, I don't have control over operating system set up in departments
outside of my own and in my own department where I'd have strict control
over how Access and Office is installed, I'm a lot more successful.
With the other departments, however, I run into symptoms of the "It Guys
Hating Access" (see thread with similar name).

How these failures manifest themselves is via reference screw-ups.

--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "What's UP, Dittoooooo?" - Ditto
Nov 13 '05 #8
MLH <CR**@NorthStat e.net> wrote in
news:oi******** *************** *********@4ax.c om:
Visual Basic for Applications
Microsoft Access 8.0 Object Library
Microsoft DAO 3.51 Object Library


You win the prize. Somehow, the Microsoft DAO 3.51 Object Library
had been removed. I use DAO all the time in A97. I have no earthly
idea how it was removed yesterday. Putting a checkmark beside it
completely resolved the problem of the


Was this A97 database perhaps given to you by someone who had saved
it as A97 format from A2K or later?

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc
Nov 13 '05 #9
MLH
Well, after reading this (your reply) and the stuff on MichKa's
site - I'm scared shitless now. Well, I mean I'm shaken somewhat
to know that there's much more out there I'm apt to run into
than what I've encountered so far. I must say that I've been
EXTREMELY lucky that my apps haven't just obliterated them-
selves and all those within a couple of hundred yards. I break
all the rules, it seems. Yet, I have been spared much of what
might have come down the pipe. And you know what flows
downhill. I'll be more careful and more thoughtful.
xxxxxxxxxxxxxxx xxxxxxxxxxxxxxx xxxxxxxx

http://www.trigeminal.com/usenet/usenet026.asp?1033

It's probably a good idea to get into the habit, though I know not
everyone does. I go (probably needlessly) overboard with it, myself;
for example, instead of dim ooga as ComboBox I write dim ooga as
Access.Combobo x/
I just don't remember this ever being an issue with Access 2.0. Did
A2.0 have References that could be completely blown away behind
the scenes potentially rendering years of programming code nonworking?
I don't think so.


I don't know. Applications with Access 2 I did involved macros and
weren't all that involved - I did an awful lot more with dBase III+ and
IV. I know you're having great difficulty adjusting to A97, but the
experienced developers here generally say it is far better a product
than 2.0. Indeed, in my first months of using A2003, I felt the same
way you do with respect to the new product, but have eventually grown
used to it.

I don't think stuff can get blown away behind the scenes though all that
easily. In your case, assuming it was this ocx installation that caused
your problem, it's a case of differing versions of Access and/or
possibly Office. One of the reasons I went to Access 2003 from A97 was
because my applications started malfunctioning on newer machines that
had elements of Office 2000 installed. Apparantly it's fairly easy to
muck up the dlls when you've later versions of Office installed on the
same machine with Access 97. Indeed, many people here will cry foul,
but I've had very inconsistent results with A97 installs on windows XP
machines, another reason for me to abandon my very much loved A97. Of
course, I don't have control over operating system set up in departments
outside of my own and in my own department where I'd have strict control
over how Access and Office is installed, I'm a lot more successful.
With the other departments, however, I run into symptoms of the "It Guys
Hating Access" (see thread with similar name).

How these failures manifest themselves is via reference screw-ups.


Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1902
by: Rob | last post by:
I need to allow a user with the default "Full Data User" permissions on the front-end database to relink to a table in a backend to which he has the same permissions. (I'd like to do read, update, insert, delete, which are the DEFAULTS for a "Full data user", but just so you know I have also added "Administrator" for this group in front and backends, just to try and figure out the problem and it didn't help). Anyway I cycle through the...
2
2655
by: caradhras | last post by:
I've been troubleshooting for a friend's charity. She wants their database accessible from both their PCs (I'll call them A & B), which are on a local network, both running A2K on Windows 98. I manually split the DB into a front & back end, and placed the backend in a shared folder on computer A. Made sure the shared folder was set to "full access." Computer A can open everything just fine. Next I copied the frontend to computer B and...
1
3497
by: Tom Wild | last post by:
Hi I am trying to create a webform that connects to an Access database. If I use the connection string: "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Gizmo\Gizmo.mdb" Then the application connects to the database and downloads data fine but when I try to fire an Update command I get the following error: "Problem firing System.Data.OleDb.OleDbCommand - Operation must use an updateable query"
1
2122
by: fl | last post by:
I am running ASPNET on my local machine. I have a problem when I try to connect to a SQL server database table. The data looks good when I right click SqlDataAdapter1 to preview the data. When F5 to run it, I get this error "Login failed for user '(null)'. Reason: Not associated with a trusted SQL Server connection..." Here is my code (they are all generated by the wizard): Public Class WebForm1 Inherits System.Web.UI.Page
4
4430
by: Ying Lu | last post by:
Hello, I have a table named "USER" under MySQL database. When I am trying to move tables from MySQL to PostgreSQL, I found that I could not create a table namely "USER". I guess "USER" is a key string used by PostgreSQL system so that we could not create a table named "USER". Is that true? Thanks a lot, Emi Lu
0
2034
by: damontimm | last post by:
My setup: Mac OS 10.4.4; mysql 4.x ... everything was installed and working fine for some time. Today, I added drupal to my system and had to create a new database in mysql -- now I am having some problems. I will outline the problem, what I have tried to do to fix it, and what I think caused it. Hope you can help and I am sorry this is long. The problem: I can't login to mysql anymore with the "root" username (which is where I have all...
0
6697
by: godsmustbcrazy | last post by:
Here is my problem. Brand new SQL Server 2005 Installation 1. Create a database "Test" 2. Create a database user "Domain\user" and set user mapping to "Test" with datareader, datawriter permissions 3. Look at Test ->Properties->Permissions activate "Domain\user" and click effective permissions and I get this error message
2
10655
by: MaxDraxyl | last post by:
Hi, I am installing DB2 Express-C on Fedora Core 6. Installation went smoothly, but I cannot create a database. $ db2 create database test SQL1092N "EMOB" does not have the authority to perform the requested command. $ grep emob /etc/passwd emob:x:500:101:Emob User:/home/emob:/bin/bash $ grep -e "^d" /etc/group
11
5063
by: Jan | last post by:
Hi: Here's a problem I've had for a long time. The client is really running out of patience, and I have no answers. Access2003, front- and back-end. Single form with 4 subforms (each representing a related table), 5-10 clerks doing data entry at one time. Tables are quite large but all work is done with unbound forms and/or local temp tables, and written
11
5032
by: fniles | last post by:
One of our application uses VB6 and Access97 database. Another application uses VB.NET 2005. This morning for about 15 seconds when the application tries to read either a query or a table from the database, in in both VB6 and VB.NET applications, I got the error "The Microsoft Jet database engine cannot find the input table or query 'myTable'. Make sure it exists and that its name is spelled correctly." Also, I got the error "The...
0
9672
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10854
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10601
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10260
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
9388
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
7794
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5652
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
4460
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
3116
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.