By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,244 Members | 1,772 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,244 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.OpenRecordset("Employees", dbOpenTable)
' Create dynaset-type Recordset object.
Set rstOrders = dbs.OpenRecordset("Employees", dbOpenDynaset)

' Create snapshot-type Recordset object.
Set rstProducts = dbs.OpenRecordset("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.Close
rstOrders.Close
rstProducts.Close
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
Share this Question
Share on Google+
11 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.OpenRecordset("Employees", dbOpenTable)
' Create dynaset-type Recordset object.
Set rstOrders = dbs.OpenRecordset("Employees", dbOpenDynaset)

' Create snapshot-type Recordset object.
Set rstProducts = dbs.OpenRecordset("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.Close
rstOrders.Close
rstProducts.Close
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

P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
MLH <CR**@NorthState.net> wrote in
news:oi********************************@4ax.com:
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

P: n/a
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.
xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx

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.


Nov 13 '05 #10

P: n/a
MLH
Yes, that's exactly the case. He first sent me the A2K file and
later followed up with the A97 convert after I told him I couldn't
open the first one in A97.

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


Nov 13 '05 #11

P: n/a
Bri
David has hit the nail on the head. AC2K was a bit braindead in its
priorities and would create a new db with ADO referenced and not with
DAO. They had this new tech that they were pushing and you had to
override the default and add DAO back in manually if you wanted to use
it. So, the problem is not with AC97 which many of us still think of as
the sweetspot in the Access lineage.

--
Bri

MLH wrote:
Yes, that's exactly the case. He first sent me the A2K file and
later followed up with the A97 convert after I told him I couldn't
open the first one in A97.
Was this A97 database perhaps given to you by someone who had saved
it as A97 format from A2K or later?



Nov 13 '05 #12

This discussion thread is closed

Replies have been disabled for this discussion.