Jumat, 06 April 2012

RE: [MS_AccessPros] Streaming lining the size of a back end database

 

Art-

Perhaps you're not giving the form a chance to close by using Quit. Try this:

Public Sub onCloseDatabase(control As IRibbonControl)
' This Sub is called if the user clicks the Close Database on our Ribbon
' Make sure frmMain is there
If IsFormLoaded("frmMainMenu") Then
' Yes - execute the Exit procedure
DoCmd.Close acForm, "frmMainMenu"
DoCmd.Quit

End If

End Sub

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

---------------------------

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art Lorenzini
Sent: Friday, April 06, 2012 2:43 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Streaming lining the size of a back end database

John,

Spoke to soon. When I close the form it works fine. But if I exit the database I get a "Object variable or With Block variable not set" error

This is the pieces of code I have.

Option Compare Database
Option Explicit
Dim dbStatic As DAO.Database, rsStatic As DAO.Recordset


---

Private Sub Form_Load()
Set dbStatic = CurrentDb
Set rsStatic = dbStatic.OpenRecordset("tblUnit")
Me.txtFileLocation = DLookup("Database", "MSysObjects", "Name='tblUnit'")

End Sub

---

Private Sub Form_Close()
rsStatic.Close
Set rsStatic = Nothing
Set dbStatic = Nothing

End Sub

After a little further testing. I have a custom ribbon which I use to exit the database. It seems that this is the only time this issue happens.

This is the ribbon callback code:

Public Sub onCloseDatabase(control As IRibbonControl)
' This Sub is called if the user clicks the Close Database on our Ribbon
' Make sure frmMain is there
If IsFormLoaded("frmMainMenu") Then
' Yes - execute the Exit procedure
'Form_frmMain.cmdExit_Click
DoCmd.Quit

End If

End Sub


With warm regards,

Arthur Lorenzini
Sioux Falls, South Dakota

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Friday, April 6, 2012 1:24 AM
Subject: RE: [MS_AccessPros] Streaming lining the size of a back end database

Art-

Let's say you have a main Switchboard form that stays open for the duration. In the code behind the form do:

Option Compare Database
Option Explicit
Dim dbStatic As DAO.Database, rsStatic As DAO.Recordset

….

Private Sub Form_Load()
Set dbStatic = CurrentDb
Set rsStatic = dbStatic.OpenRecordset("<name of linked table>")
End Sub

….

Private Sub Form_Close()
rsStatic.Close
Set rsStatic = Nothing
Set dbStatic = Nothing
End Sub

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

-------------------------------------

From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art Lorenzini
Sent: Friday, April 06, 2012 12:23 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Streaming lining the size of a back end database

John,

How do I accomplish this:

2) When your app opens in the front end, open a recordset to a linked table
(doesn't matter which one) and leave it open for the duration of the app.

With warm regards,

Arthur Lorenzini
Sioux Falls, South Dakota

________________________________
From: John Viescas <JohnV@msn.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, April 5, 2012 2:13 PM
Subject: RE: [MS_AccessPros] Streaming lining the size of a back end database

Art-

Here are a couple:

1) Turn OFF Name AutoCorrect and then edit each of the backed tables and make
sure SubformDatasheet is set to [None].

2) When your app opens in the front end, open a recordset to a linked table
(doesn't matter which one) and leave it open for the duration of the app.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

--------------------------------

From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Art
Sent: Thursday, April 05, 2012 9:00 PM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Streaming lining the size of a back end database

I know there is probably a lot of tips on on how to boost the performance of a
backend database but could you give me like the top 5.

Right now I am going back through and removing all the lookups I had built into
the tables and using combos on the form do the work. What else could I look at?

I know this is a wide and varied topic but any help would be great.

Thank you,

Arthur Lorenzini
Sioux Falls, SD

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]

__._,_.___
.

__,_._,___

Tidak ada komentar:

Posting Komentar