ACCESS FEED: [MS_AccessPros] Re: compact and repair own back end

Selasa, 29 Januari 2013

[MS_AccessPros] Re: compact and repair own back end

Jeff

Very true, Jeff. But this topic is just about compacting a database. Not really much of a security issue.

And I don't consider your reply as "butting in". Everyone is always free to participate in all the threads.

Bill

--- In MS_Access_Professionals@yahoogroups.com, Jeff Jones wrote:
>
> Sorry to butt in, but the Environ function can be spoofed by a knowledgeable user such that the results attained is someone else. If there's a risk of this happening then the API call is a better choice.
>
> Jeff
>
> ----- Original Message -----
>
>
>
>
>
> Thanks so much. This is going to be so nice and free up my Sunday mornings.
>
> Respectfully,
> Liz Ravenwood
> Programmer/Analyst
> Super First Class Products
> B/E Aerospace
> O: 1.520.239.4808
> www.beaerospace.com
>
> -----Original Message-----
> From: MS_Access_Professionals@yahoogroups.com [mailto: MS_Access_Professionals@yahoogroups.com ] On Behalf Of Bill Mosca
> Sent: Tuesday, January 29, 2013 8:18 AM
> To: MS_Access_Professionals@yahoogroups.com
> Subject: [MS_AccessPros] Re: compact and repair own back end
>
> One more thing, Liz. You are missing an API library declaration. To get the username, do it this way:
>
> Private Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
> (ByVal lpBuffer As String, nSize As Long) As Long
>
> Public Function CurrentUserName() As String 'Purpose : Returns the network login name 'DateTime : 5/28/2002 09:54
> 'Author : Bill Mosca
> Dim lngLen As Long
> Dim x As Long
> Dim strUserName As String
>
> 'Create buffer
> strUserName = String(254, Chr$(0))
> lngLen = Len(strUserName)
> x = GetUserName(strUserName, lngLen)
>
> If (x > 0) Then
> 'Trucate remaining buffer space from end of string.
> CurrentUserName = Left$(strUserName, InStr(strUserName, Chr$(0)) - 1)
> Else
> CurrentUserName = vbNullString
> End If
>
> End Function
>
> Then call is this way:
> strUser = CurrentUserName ()
>
> or you can use a simpler way without the API call:
> strUser = Environ("USERNAME")
>
> Environ() is a built-in function.
>
> Bill
>
> --- In MS_Access_Professionals@yahoogroups.com , Liz Ravenwood wrote:
> >
> > Pros, I'm trying to do a compact and repair a db's back end and have everything set so that hopefully it is not being opened by something else and then this is the module that is supposed to actually do the deed.
> >
> > Public Sub CompactNRepairBE(strForm As String)
> > Dim lngFullSize As Long
> > Dim strsql As String
> > Dim db As DAO.Database
> > Dim strBE As String
> > Dim strUser As String
> > strUser = GetLoginName()
> > st0: ' determine back end path
> > strBE = "\\ws0415\tudb$\ECR_be-TEST.accdb"
> > st1: ' get previous file size
> > ' MsgBox ("...performing necessary maintenance... This will take a few seconds.")
> > lngFullSize = CDec(FileLen(strBE)) / 1024
> > strsql = "INSERT INTO DBMaint VALUES (#" & Now() & "#, 'Pre-compact: " & strUser & "', " & lngFullSize & ");"
> > CurrentDb.Execute (strsql)
> > st2: ' close the current form
> > DoCmd.Close acForm, strForm
> >
> > st3: ' open the back end
> > Set db = DBEngine(0).OpenDatabase(strBE) '--->>>
> >
> > db.CompactDatabase
> >
> >
> > st4: ' close the back end (compact and repair upon close)
> > db.Close
> > Set db = Nothing
> > lngFullSize = CDec(FileLen(strBE)) / 1024
> > strsql = "INSERT INTO DBMaint VALUES (#" & Now() & "#, 'Post-compact: " & strUser & "', " & lngFullSize & ");"
> > CurrentDb.Execute (strsql)
> > st5: ' reopen the current form
> > CurrentDb.Execute "INSERT INTO LogStats (UserLoggedIn, TimeLoggedIn, WhichDatabase) " & _
> > "VALUES('" & strUser & "-compacted" & "', #" & Now() & "#, 'ECR DB')"
> > DoCmd.OpenForm strForm
> > End Sub
> >
> > Unfortunately, the db.compactdatabase doesn't work. It says that the method or the data member is not found and won't even compile.
> >
> > What am I doing wrong?
> >
> > Respectfully,
> > Liz Ravenwood
> > Programmer/Analyst
> > Super First Class Products
> > B/E Aerospace
> > O: 1.520.239.4808
> > www.beaerospace.com
> >
> > This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
> >
> >
> > [Non-text portions of this message have been removed]
> >
>
> ------------------------------------
>
> Yahoo! Groups Links
>
> This email (and all attachments) is for the sole use of the intended recipient(s) and may contain privileged and/or proprietary information. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
>
>
>
>
> --
>
>
> Jeffrey Park Jones
> Excel, Access, Word, Office Expert
> Excel and Access, LLCÂ®
> http://ExcelAndAccess.Com
>
> 919-671-9870
> 5109 Deer Lake Trail
> Wake Forest, NC 27587
> jpjones23@...
>
>
>
>
> [Non-text portions of this message have been removed]
>