VBA for access

[ma]

Hello,

I am new in VBA for access. I copied a sample application from help
examples into the event for a button but it doesn't work.



The code is:




Private Sub Command0_Click()

Dim wrJet As Workspace
DefaultType = dbUseJet
' Create an unnamed Workspace object of the type
' specified by the DefaultType property of DBEngine
' (dbUseJet).
Set wrkJet = CreateWorkspace("", "admin", "")



End Sub



And the error is:

User defined type not defined. This means that it doesn't know the meaning
of workspace. What is the problem?



Best regards

 

[Dirk Goldgar]

Hello,

I am new in VBA for access. I copied a sample application from
help examples into the event for a button but it doesn't work.



The code is:




Private Sub Command0_Click()

Dim wrJet As Workspace
DefaultType = dbUseJet
' Create an unnamed Workspace object of the type
' specified by the DefaultType property of DBEngine
' (dbUseJet).
Set wrkJet = CreateWorkspace("", "admin", "")



End Sub



And the error is:

User defined type not defined. This means that it doesn't know the
meaning of workspace. What is the problem?

You probably don't have a reference set to the Microsoft DAO 3.6 Object
Library. You need that reference to work with DAO objects, and it's not
present by default in Access 2000 and 2002. While in the VB Editor
environment, click Tools -> References..., locate the reference in the
list, and put a check mark next to it.

 

[ma]

Thanks a lot. You save me a lot of time!
Now I can open a data base but not a recordset
the code is as follow:

Dim wrkJet As Workspace
Dim db As Database
Dim table As Recordsets

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrkJet.OpenDatabase("c:\jila_databse.mdb")
Set table = db.OpenRecordset("tblEpisodes", dbOpenTable)



and there is an error for set table =.... which tells that the type is
mismatched ! Could you please help on this matter too?

Thanks again for your kind help.

Best regards

 

[Dirk Goldgar]

Thanks a lot. You save me a lot of time!
Now I can open a data base but not a recordset
the code is as follow:

Dim wrkJet As Workspace
Dim db As Database
Dim table As Recordsets

Set wrkJet = CreateWorkspace("", "admin", "", dbUseJet)
Set db = wrkJet.OpenDatabase("c:\jila_databse.mdb")
Set table = db.OpenRecordset("tblEpisodes", dbOpenTable)



and there is an error for set table =.... which tells that the type is
mismatched ! Could you please help on this matter too?

First, your declaration should be -- and probably is --

Dim table As Recordset

not

Dim table As Recordsets

But more than that, you're running into a name conflict that didn't
exist when the help file examples were originally written. Access 2000
and later include by default a reference to the ActiveX Data Objects
(ADO) library, and that defines a number of objects with the same names
as DAO objects -- and the objects aren't compatible. So if you have
both references, you get confusion over which one is meant.

The objects that are defined in both the DAO and ADO libraries are:

Connection, Error, Errors, Field, Fields, Parameter,
Parameters, Property, Properties, Recordset

There's also an ADOX library (not referenced by default) that includes
some objects that conflict with DAO:

Group, Groups, Index, Indexes, Property, Properties, User, Users

If you're not using ADO, open the References dialog and remove the
reference to it. If you intend to use ADO, and so you want to keep both
references, you can avoid name conflicts by explicitly qualifying the
declarations of the objects; e.g.,

Dim wrkJet As DAO.Workspace
Dim db As DAO.Database
Dim table As DAO.Recordsets

(Note: you don't actually *have* to qualify any of the above but the
declaration of "table", because the other objects don't conflict. But
it's a good, safe practice to do so.)

 

[Immanuel Sibero]

ma,

You probably need to use specific reference to dim recordsets.

Dim rst As DAO.Recordset

Also, I would not use the word "table". It may be a reserved word. Use
something like rst, rs.

HTH,
Immanuel Sibero