Help with code Front end back end DB

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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

--
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
| 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.
 
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?
 
Yes, correct. It uses the CreateShortcut method of VBScript to create a
shortcut on 'All Users' desktop.

--
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:
|
| ' 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?
|
|
 
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?
 
Glad to hear it. See this article for help on disabling sandboxmode.

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

--
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!
|
| 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?
 
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
 
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...
 
Back
Top