Jumat, 06 April 2012

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

 

This has been an interesting thread. While I always turn the Autocorrect tracking off like Crystal said, I have had the subdatasheet pop up from time to time. My guess is that the Autocorrect must have been on when the db was created so when I compact and repair, they show back up.

Following is what I found on a MS site to programatically turn the subdata sheet off. If I notice that a subdata sheet shows up on a table, I just run this module.

Option Compare Database
Option Explicit
' This procedure came from MS: http://support.microsoft.com/kb/275085 to speed performance
'in linked tables. In the Immediate window, type TurnOffSubDataSheets, and then press ENTER to
'run the function.
'Make sure the Microsoft DAO 3.6 Object Library check box is selected
Sub TurnOffSubDataSheets()
Dim MyDB As DAO.Database
Dim MyProperty As DAO.Property
Dim propName As String, propVal As String, rplpropValue As String
Dim propType As Integer, i As Integer
Dim intCount As Integer

On Error GoTo tagError

Set MyDB = CurrentDb
propName = "SubDataSheetName"
propType = 10
propVal = "[None]"
rplpropValue = "[Auto]"
intCount = 0

For i = 0 To MyDB.TableDefs.Count - 1
If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
If MyDB.TableDefs(i).Properties(propName).Value = rplpropValue Then
MyDB.TableDefs(i).Properties(propName).Value = propVal
intCount = intCount + 1
End If
End If
tagFromErrorHandling:
Next i

MyDB.Close

If intCount > 0 Then
MsgBox "The " & propName & " value for " & intCount & " non-system tables has been updated to " & propVal & "."
End If

Exit Sub

tagError:
If Err.Number = 3270 Then
Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
MyProperty.Type = propType
MyProperty.Value = propVal
MyDB.TableDefs(i).Properties.Append MyProperty
intCount = intCount + 1
Resume tagFromErrorHandling
Else
MsgBox Err.Description & vbCrLf & vbCrLf & " in TurnOffSubDataSheets routine."
End If
End Sub

--- In MS_Access_Professionals@yahoogroups.com, "Bill Mosca" <wrmosca@...> wrote:
>
> Bob, Crystal
>
> In all my years working with SQL Server back ends, I've never seen a subdatasheet set up by itself like it will do in Access tables. I think creating a foreign key is what triggers it. When I use SQL tables I create the foreign keys in the Management Studio either in design mode or through a DDL query.
>
> Regards,
> Bill Mosca, Founder - MS_Access_Professionals
> http://www.thatlldoit.com
> Microsoft Office Access MVP
> https://mvp.support.microsoft.com/profile/Bill.Mosca
>
>
>
> --- In MS_Access_Professionals@yahoogroups.com, Crystal <strive4peace2008@> wrote:
> >
> > Hi Bob,
> >
> > interesting thought
> >
> > my guess would be No since the table design is not modified in Access ... perhaps someone with SQL Server can look at what happens when tables are linked with and without AutoCorrect on ...
> >
> >
> > Warm Regards,
> > Crystal
> >
> >  *
> >    (: have an awesome day :)
> >  *
> >
> >
> >
> > ________________________________
> > From: Robert Peterson <bob@>
> > To: MS_Access_Professionals@yahoogroups.com
> > Sent: Friday, April 6, 2012 9:28 AM
> > Subject: RE: [MS_AccessPros] Re: Streaming lining the size of a back end database
> >
> > Hi,
> >
> > I was wondering if Access creates sub datasheets when the backend is SQL server? I've never investigated this myself.
> >
> > Bob Peterson
> >
> > -----Original Message-----
> > From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of Bill Mosca
> > Sent: Friday, April 06, 2012 11:10 AM
> > To: MS_Access_Professionals@yahoogroups.com
> > Subject: [MS_AccessPros] Re: Streaming lining the size of a back end database
> >
> > Thank you, Crystal. Since Access sets up subdatasheets for you without you knowing, it can be a real pain. Make sure you turn of Name AutoCorrect Tracking before you even start building tables or the sub datasheets will pop back up when you compact.
> >
> > Bill
> >
> > --- In MS_Access_Professionals@yahoogroups.com, Crystal <strive4peace2008@> wrote:
> > >
> > > great tip, Bill! ... I generally set all Sub Datasheets to "none" in the beginning and turn off AutoCorrect, so I forget that others don't ...
> > >
> > > Happy Friday :)
> > >
> > >
> > > Warm Regards,
> > > Crystal
> > >
> > >  *
> > >    (: have an awesome day :)
> > >  *
> > >
> > >
> > >
> > > ________________________________
> > >  From: Bill Mosca <wrmosca@>
> > > To: MS_Access_Professionals@yahoogroups.com
> > > Sent: Friday, April 6, 2012 8:59 AM
> > > Subject: [MS_AccessPros] Re: Streaming lining the size  of a back end
> > > database
> > > 
> > > One more thing to do to your back end (although it won't have a big impact since you are not opening the back end much) is to set all Sub Datasheets to "none". Sub datasheets are like mini sub forms on a table. They can show child records for a given record in the parent table by clicking on the + to the left of the record (if the sub datasheet was set up).
> > >
> > > The problem with them is that they add a lot of overhead to big tables. When you open a table in datasheet view all those extra fields have to load, too.
> > >
> > > To turn them all off in all tables run my code found on my site at:
> > > http://thatlldoit.com/codesamples.aspx#anchor4
> > >
> > > Regards,
> > > Bill Mosca, Founder - MS_Access_Professionals
> > > http://www.thatlldoit.com Microsoft Office Access MVP
> > > https://mvp.support.microsoft.com/profile/Bill.Mosca
> > >
> > >
> > > --- In MS_Access_Professionals@yahoogroups.com, "Art" <dbalorenzini@> wrote:
> > > >
> > > > 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
> > > >
> > >
> > >
> > >
> > >
> > > ------------------------------------
> > >
> > > Yahoo! Groups Links
> > >
> > >
> > >
> > > [Non-text portions of this message have been removed]
> > >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
> >
> >
> > ------------------------------------
> >
> > Yahoo! Groups Links
> >
> >
> >
> > [Non-text portions of this message have been removed]
> >
>

__._,_.___
.

__,_._,___

Tidak ada komentar:

Posting Komentar