VBA error: Expected user-defined type, not project.

I'm trying to learn VBA in access and I cut-pasted some sample code from an online book on the subject into my Visual Basic Editor (with my Access database open).

I changed the names in the sample to match my database, but I get this error when I run the code:

Expected user-defined type, not project.

Here's the code:

Sub exaObjectVar()

'Declare some object variables
Dim dbLib As fct
Dim rsFact As Recordset
Dim rsFact2 As Recordset

'Set dbLib to the current database (i.e. fct)
Set dbLib = CurrentDb

'Open a recordset object for the fact table
Set rsFact = dbLib.OpenRecordset("fact")

'Two object variables will refer to the same object
Set rsFact2 = rsFact

'Use a property of this object
MsgBox "fact record count: " & rsFact.RecordCount

'Destroy the object using rsFact2 reference

'Now rsFact has nothing to refer to, so we get error
MsgBox "fact record count: " & rsFact.RecordCount

End Sub

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Once I got such message in the project with the name like some VB function or keyword. For example, if project name is MyDB, and you write:

dim MyDB as Database
set MyDB = new Database

you get such error. VB is stupid. I don't know whether such situation is possible in VBA, possibly conflict with database name.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It looks like your problem is in:     Dim dbLib As fct
fct is the unknown type and should be replaced by Database

what does this line:

Dim dbLib As fct


what is fct?

You the attempt to set dbLib to be CurrentDb, which REQUIRES that the variable dbLib be declared as a Database type (Dim dbLib as Database).

fct may be the NAME of the Database you are using, but if this is being done in VBA code, in a module within the DAtabase(named 'fct') itself, then CurrentDb IS in fact pointing to the 'current' database (which is the one named 'fct')

you seem to have a mis-understanding of what the Dim statement is used for.

Dim dbLib as Database  declares that the Variable named 'dbLib' is a DATABASE type object (actually, the variable dbLib will hold a REFERENCE to a Datbase type object), but does NOT assign a 'value' to that variable.  You then use the Set dbLib = CurrentDb statement to actually assign the value of dbLib to be a REFERENCE to the Current Database (the database within which the code is executing).

Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

so try changin this line:

Dim dbLib As fct

to read

Dim dbLib as Database

and see if that helps.

Depending on What version of Access you are running it may not like the Database.  You may have to go into the Tools...References Menu and select DAO library.  If you have Both ADO and DAO in the project you will have to explicity dim the objects

Dim db as DAO.Database
Richie_SimonettiIT OperationsCommented:
I am with Arthur:
(what is fct?)

gateguard, some feedaback required.....
Richie_Simonetti:  my guess is that gateguard is VERY new to this and has a database named "fct.mdb".  He/she thinks that CurrentDb should be pointed to a variable named fct, to be sure that it points to the correct mdb (of course, this code is writen and is running INSIDE fct.mdb, but clrearly gateguard is VERY confused as to what is going on).

gateguardAuthor Commented:
fct is the name of my database

I see the syntax error in that line.

I'm going to correct it and try again.

(And it's true, I am confused!)
gateguardAuthor Commented:
that was it
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.