'Database' type doesn't exist?! and invalid OpenRecordset argument 2

[TheFoxy]

I cannot understand what the problem with the following code is. It's from a function in a code module (Access 2000):

Dim DB as Database 
Set DB = CurrentDB

Dim TheRecords as Recordset 'No problems here

I get "ERROR: User-defined type not defined" on the first line. EH?!? It doesn't appear to recognise the word 'Database' at all, as it doesn't capitalise it when I type it in lowercase

Why is it giving me that error? I've seen that sort of thing written a dozen times!

Set TheRecords = DB.OpenRecordset("querytext", dbOpenSnapshot) doesn't work (obviously, considering the above), so I tried the following instead as the next bit of code:

Set TheRecords = CurrentDb.OpenRecordset("querytext", dbOpenSnapshot)

This gives me an error "invalid argument"... interestingly, dbOpenShapshot is said to be empty when I inspect it in debug mode.

Set TheRecords = CurrentDb.OpenRecordset("querytext")

"Type mismatch" this time.

The following code works for some reason:

Dim TheRecords as Object

Set TheRecords = CurrentDb.OpenRecordset("querytext")

What is going on? 'Database' types are defined in code samples all over the place, so why is Access playing dumn? And what's wrong with 'dbOpenShapshot'?

 

[SkipVought]

Hi,

Do you have a Table or Query in your database named querytext?

Skip,

_{[red]Be advised:[/red] When you ignite a firecracker in a bowl of vanilla, chocolate & strawberry ice cream, you get...
Neopolitan Blownapart!}

 

[VBslammer]

Do you have a variable in your project that is using a keyword? Such as: [tt]Dim database As String[/tt]?

You might try using fully-qualified references:

[tt]Dim db As DAO.Database[/tt]

VBSlammer

"You just have to know which screws to turn." - Professor Bob

 

[RoyVidar]

The error probably relates to not having a reference to Microsoft DAO 3.# Object Library (in VBE - Tools | References). In addition, also follow VBSlammers advice on fully qualifying all declarations of DAO objects

[tt]dim db as dao.database
dim rs as dao.recordset
dim qd as dao.querydef
...[/tt]

Roy-Vidar

 

[TheFoxy]

Do you have a Table or Query in your database named querytext?"

Lol, no, that is a placeholder for the SQL I was using.

"Do you have a variable in your project that is using a keyword? Such as: Dim database As String?"

No.

Thanks VBSlammer and RoyVidar, I'll try doing what you say. I'm sure I tried dao.(something), and this wasn't recognised either. I'll reply later once I've had a go.

In any case, the lack of a reference sounds like a very plausible explanation for the odd behaviour.

 

[TheFoxy]

I added in the reference, and used the dao prefix, and it worked. Thanks!