473,325 Members | 2,342 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,325 software developers and data experts.

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.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
11 9780
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.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
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**@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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
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,...
2
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...
1
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...
1
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...
4
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...
0
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...
0
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...
2
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...
11
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...
11
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...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.