Compile error 3

[Tobasco1136]

I don't know anything about VBA, but I am having an issue with the size of my Access DB growing by several megs each time it is accessed. I read an article about trying to recompiling it. I finally figured out how to do that, but I get the following error: "Method or data member not found." Then Access highlights .Recordset. Is there an easy was to fix this?

Private Sub Combo49_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[FileCheckOutID] = " & Str(Nz(Me![Combo49], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[Hakala]

Hi, jfakes!

Which reference libraries are you using?

In the Visual Basic window, choose Tools from the top menu, then References. In one of my Access 2002 dbs, I've got Visual Basic for Applications, Microsoft Access 10.0 Object library, OLE Automation, Microsoft ActiveX Data Objects 2.5 library, and Microsoft Office XP Web Components.

Mine recognizes .recordset just fine.



Michelle Hakala

http://www.winebird.com/

 

[Tobasco1136]

I forgot to add that this is Access 97. I developed it in 2003 then converted it for another user.

Here is what I found checked (I don't know anything about them though):
Visual Basic for Applications.
Microsoft Access 8.0 Object library
Microsoft DAO 3.5 Object Library

 

[Hakala]

I don't have Access 97 on my PC anymore so I can't even look... I assume you repaired and compacted. Have you checked for "recordset" in the Access Help file? It might be called something else in 97.


Michelle Hakala

http://www.winebird.com/

 

[Ed2020]

Try declaring your database and recordset objects with the library name:

Dim DB as DAO.Database
Dim rs as DAO.Recordset

Bloating of the MDB files is a common problem in MS Access. If it's only a few MBs I wouldn't worry about it.

Ed Metcalfe.

Please do not feed the trolls.....

 

[PHV]

Replace this:
Set rs = Me.Recordset.Clone
with this:
Set rs = Me.RecordsetClone

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Tobasco1136]

I have repaired and compacted and that helped a little, but I am hoping to stop the bloat.

I replaced the Me.Recordset.Clone with Me.RecordsetClone and the compiler went past that, but got stuck on "Option Compare Database." Again, I don't know what that means.

Here is the module:

Err_butProviderLookup2_Click:
MsgBox Err.Description
Resume Exit_butProviderLookup2_Click
Option Compare Database

 

[Ed2020]

Option Compare Database should only ever appear at the top of a module. Either move it up there or (if it already appear at the top of a module) delete it.

Why are you trying to stop the bloat? How much bloat are we talking about? is it actually causing you any problems?

Ed Metcalfe.

Please do not feed the trolls.....

 

[Hakala]

That looks like a mistake. "Option Compare Database" should be at the very top of your code. Sometimes it's followed by another line, "Option Explicit".

When you open your code, the top of the window should say

Option Compare Database
----------------------------------

or

Option Compare Database
Option Explicit
----------------------------------





Michelle Hakala

http://www.winebird.com/

 

[Tobasco1136]

This is a new database and should only be around 10 mb, however, as I run reports it has grown to 39mb and I want to make sure it doesn't continue growing at that speed. Also, it is the slowest loading database I have.

 

[Tobasco1136]

I just tested it the database after making your suggestions. I repaired then compacted it and it stayed steady at 19mb. Like I had said, it was growing to 30-40 mb just by running a report or two.

Thanks for all of your help. I don't know what I would do without Tek-Tips.

 

[Ed2020]

jfakes,

If it's growing by 30MB don't worry about it. This is still *tiny* for an Access database.

The speed of the system is probably unrelated. Make sure all of the relevant fields are indexed. As a rule of thumb any field that is used in a join (either in the database relationships, or in a query) should be indexed, as should all fields that are regularly searched on.

Ed Metcalfe.

Please do not feed the trolls.....