Help with code Front end back end DB

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

JohnK - 08 Mar 2005 14:03 GMT

I used some scripting I found from Tony's site in which he uses a database to
automatically update the client db. Everything works but I am having trouble
with one piece of this.

I had wanted to place the client on the user's desktop to help them as most
have difficulties with shortcuts. The problem is the network user name. For
my path it is c:\Documents and settings\jkirchn\desktop (I tried c:\documents
and settings\all users\desktop but it doesn't appear on the desktop). Is
there a way to code a variable for the user specific database? If not is
there a method to code the filecopy to go to their local drive and plant a
shortcut for them?

My IT folks are going to do the initial "push" via a login script and from
there the updating will be via the update database. I just need to figure
out where to have the copy go.

Thanks for any help/suggestions.

Reply to this Message

Dave Patrick - 08 Mar 2005 14:48 GMT

Here's the relavant part of a VBScript that I wrote and have been using.

Dim fso, oShell, DesktopPath
Dim strLocation, WshSysEnv

Set fso = CreateObject("Scripting.FileSystemObject")
Set oShell = CreateObject("WSCript.shell")
Set WshSysEnv = oShell.Environment("Process")

strLocation = oShell.SpecialFolders("AllUsersDesktop")
Set oShellLink = oShell.CreateShortcut(strLocation & "\Coal.lnk")
oShellLink.TargetPath = "%systemdrive%\Data\Access\TrainLineUp.mdb"
oShellLink.WindowStyle = 3
If AccessVersion <> 11 Then
oShellLink.IconLocation =
"%windir%\Installer\{00000409-78E1-11D2-B60F-006097C998E7}\accicons.exe,11"
Else
oShellLink.IconLocation =
"%windir%\Installer\{90110409-6000-11D3-8CFE-0150048383C9}\accicons.exe,11"
End If

oShellLink.Description = "Coal Database"
oShellLink.WorkingDirectory = "%systemdrive%\Data\Access"
oShellLink.Save

Function AccessVersion()
Select Case oShell.RegRead("HKCR\Access.Application\CurVer\")
Case "Access.Application.8"
AccessVersion = 8
Case "Access.Application.9"
AccessVersion = 9
Case "Access.Application.10"
AccessVersion = 10
Case "Access.Application.11"
AccessVersion = 11
End Select
End Function

Set fso = Nothing
Set oShell = Nothing
Set WshSysEnv = Nothing

Signature

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

|I used some scripting I found from Tony's site in which he uses a database to
| automatically update the client db. Everything works but I am having trouble
[quoted text clipped - 13 lines]
|
| Thanks for any help/suggestions.

Reply to this Message

JohnK - 08 Mar 2005 16:19 GMT

Thanks, but that seems overwhelming (I am still new to VBA). Here is what I
have:

' Update status form to identify version being copied.
strVer = DLookup("[VersionNumber]", "tblVersionServer")
Me.txtVer.Caption = "Installing version number ... " & strVer

' Load variables with correct file name-path values.
'strMyDB = CurrentDb.Name
strPath = "c:\Documents and Settings\All Users\"
strDest = "c:\Documents and Settings\All Users\AuditClient.mdb"
strBkup = "c:\AuditClient.mdb"

It copies perfectly from the server to the strDest and to the strBkup. The
problem is the strDest will not put it on the desktop the user sees (darned
XP). The only way I can see is if I can substitute the "all users" with
their username as a variable somehow. The other way is since it does write
perfectly to their c drive is to force a shortcut to the desktop but then
again there is the rub...how to get it to their desktop.

I have read through the code you submitted. As I said, I am new, but it
seems like this would still place it to the generic desktop for all users.
Did I misread it?

Reply to this Message

Dave Patrick - 08 Mar 2005 17:55 GMT

Yes, correct. It uses the CreateShortcut method of VBScript to create a
shortcut on 'All Users' desktop.

Signature

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

| Thanks, but that seems overwhelming (I am still new to VBA). Here is what I
| have:
[quoted text clipped - 19 lines]
| seems like this would still place it to the generic desktop for all users.
| Did I misread it?

Reply to this Message

JohnK - 08 Mar 2005 20:01 GMT

Thanks! This works perfectly!

One more question please: Under tools macros security if I set it to low
does this affect the database or my machine only? When the client opens, if
it detects a newer version on the server, will close and open an update
database, which, when finished, closes itself and reopens the client. I am
trying to avoid all those pain in the butt "unsafe macro" messages the user
needs to click through. Is there a way to shut it off?

> Yes, correct. It uses the CreateShortcut method of VBScript to create a
> shortcut on 'All Users' desktop.
[quoted text clipped - 26 lines]
> | seems like this would still place it to the generic desktop for all users.
> | Did I misread it?

Reply to this Message

Dave Patrick - 08 Mar 2005 22:20 GMT

Glad to hear it. See this article for help on disabling sandboxmode.

http://support.microsoft.com/?id=294698

Signature

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

| Thanks! This works perfectly!
|
[quoted text clipped - 4 lines]
| trying to avoid all those pain in the butt "unsafe macro" messages the user
| needs to click through. Is there a way to shut it off?

Reply to this Message

David C. Holley - 09 Mar 2005 00:43 GMT

Placing the ANYTHING in the ALL USERS Should put it on the desktop. I
would consult with your PC/LAN guys (or gals) to figure out why its not
appearing. Among other things, they may have a vested interest in
ensuring that the various PC's are setup to display files in that folder.

David H

> I used some scripting I found from Tony's site in which he uses a database to
> automatically update the client db. Everything works but I am having trouble
[quoted text clipped - 13 lines]
>
> Thanks for any help/suggestions.

Reply to this Message

JohnK - 09 Mar 2005 11:57 GMT

It was my fault all along. (I am quite embarasses actually). I left off
\Desktop at the end of my path :( I appreciate the assistance.

Dave, your script helped a great deal.

Thanks again to both Dave's...

> Placing the ANYTHING in the ALL USERS Should put it on the desktop. I
> would consult with your PC/LAN guys (or gals) to figure out why its not
[quoted text clipped - 20 lines]
> >
> > Thanks for any help/suggestions.

Reply to this Message