SQLServer 2000 and MSAccess 2000.
-
This is very simple, but it's worked like a charm for the last couple of years. If anyone has a better alternative, I'm all eyes (since I can't hear what you type)
I can see this being quite network intensive if you had 300 users, but it works great for 6.
Some prerequisites for this to work.
1. All clients must be mapped to the same drive letter for the database (not too hard)
2. All clients must have Access installed in the same folder (not too hard, but might cause some pain for existing mixed environments)
Here's the code, I put it in a .Bat file. Create a shortcut on their desktop and change the icon to Access's icon. Watch that last line for wrapping, there should be only 3 lines.
md c:\datafiles
copy "h:\data files\formsdb.mdb" "c:\datafiles\formdb.mdb"
"c:\program files\microsoft office\office\msaccess.exe" "c:\datafiles\formsdb.mdb"
-
Here's a .vbs file we use at my workplace for accomplishing the same function, but in this case it checks if the user has the most current version of the Access front-end and updates only when necessary. It provides a 'wait' message while updating (transferring the db file), then launches the db according to the version of Access on the client.
Replace 'server' with the name of the actual server; replace 'database' with the actual name of your database.
******************************
Dim fso, WshNetwork, Shell, RemoteUpdate, LocalUpdate, tf, f1, ReadTextFile, ForReading, versionFile
Set WshNetwork = Wscript.CreateObject("Wscript.Network")
Set Shell = Wscript.CreateObject("Wscript.shell")
Set fso = CreateObject("Scripting.FileSystemObject")
If (fso.FileExists("c:\Progra~1\dbfiles\database.ldb")) Then
BtnCode = Shell.Popup("You already have The Staffing Front End Open.",0,"",0)
Else
If (fso.FileExists("\\server\dbfiles\frontends\database.mde")) Then
Set RemoteUpdate = fso.GetFile("\\server\dbfiles\frontends\database.mde")
If not (fso.FileExists("c:\progra~1\dbfiles\database.txt")) Then
fso.CopyFile RemoteUpdate, "c:\progra~1\dbfiles\database.mde"
Set tf = fso.CreateTextFile("c:\progra~1\dbfiles\database.txt", True)
tf.WriteLine(RemoteUpdate.DateLastModified)
tf.Close
Else
Set versionFile = fso.GetFile("c:\progra~1\dbfiles\database.txt")
If versionFile.DateLastModified < RemoteUpdate.DateLastModified Then
If (fso.FileExists("c:\progra~1\dbfiles\database.mde")) Then
Set LocalUpdate = fso.GetFile("c:\progra~1\dbfiles\database.mde")
fso.deletefile(LocalUpdate)
End If
fso.CopyFile RemoteUpdate, "c:\progra~1\dbfiles\database.mde"
While (fso.FileExists("c:\progra~1\dbfiles\database.mde")) = False
Wend
Set tf = fso.OpenTextFile("c:\progra~1\dbfiles\database.txt", 2, True)
tf.WriteLine(RemoteUpdate.DateLastModified)
tf.Close
End If
End If
End If
'the following if statement is to account for different paths to msaccess.exe which is dependent on
'which version of Office is installed on the local computer
If (fso.FileExists("c:\progra~1\micros~2\office\msaccess.exe")) then
Shell.Run "c:\progra~1\micros~2\office\msaccess.exe c:\progra~1\dbfiles\database.mde", 5
End If
If (fso.FileExists("c:\progra~1\micros~1\art\office\msaccess.exe")) then
Shell.Run "c:\progra~1\micros~1\art\office\msaccess.exe c:\progra~1\dbfiles\database.mde", 5
End If
If (fso.FileExists("c:\progra~1\micros~3\art\office\msaccess.exe")) then
Shell.Run "c:\progra~1\micros~3\art\office\msaccess.exe c:\progra~1\dbfiles\database.mde", 5
End If
If (fso.FileExists("c:\progra~1\micros~2\art\office\msaccess.exe")) then
Shell.Run "c:\progra~1\micros~2\art\office\msaccess.exe c:\progra~1\dbfiles\database.mde", 5
End If
If (fso.FileExists("c:\progra~1\micros~2\office10\msaccess.exe")) then
Shell.Run "c:\progra~1\micros~2\office10\msaccess.exe c:\progra~1\dbfiles\database.mde", 5
End If
If (fso.FileExists("c:\progra~1\micros~1\office\msaccess.exe")) then
Shell.Run "c:\progra~1\micros~1\office\msaccess.exe c:\progra~1\dbfiles\database.mde", 5
End If
If (fso.FileExists("c:\progra~1\micros~4\art\office\msaccess.exe")) then
Shell.Run "c:\progra~1\micros~4\art\office\msaccess.exe c:\progra~1\dbfiles\database.mde", 5
End If
If (fso.FileExists("c:\progra~1\micros~3\office\msaccess.exe")) then
Shell.Run "c:\progra~1\micros~3\office\msaccess.exe c:\progra~1\dbfiles\database.mde", 5
End If
If (fso.FileExists("c:\progra~1\micros~4\office\msaccess.exe")) then
Shell.Run "c:\progra~1\micros~4\office\msaccess.exe c:\progra~1\dbfiles\database.mde", 5
End If
End If
****************************
-
Extending the timeout isnt usually my first choice. If the query legitimately takes more than 60 seconds (yes, it happens), then you extend it. If you're extending it because of blocking, you're just bandaiding. Ok for short term, but it'll just get worse.
-
Well said, Andy. I should have clarified that my suggestion was generally just a bandaid. It occurred to me to suggest it because I had just recently had a situation with some long running queries where adjusting the timeout solved the problem.
Bill
-
Is there a setting that will allow for a greater timeout period when using the fso.DriveExists, fso.FileExists, fso.FolderExists, fso.GetFolder, or fso.CopyFile commands?
Our network system is very busy during the day and I need to extend the timeout to accommodate for the slower data throughput.
Thanks,
James
-
That I don't know, James.
Perhaps someone else in the group does know.
-
As others have mentioned, running the application from the user’s local drive has several advantages. One big advantage is the ability to use code to modify the sql property of passthrough queries to handle conditional calls to different SQL stored procedures, or calls with different parameters. This technique only works in single user mode.
Similar to Bobsterboy’s batch file solution, I use this simple batch file that handles the situation when users already have the application running and they attempt to launch another instance:
@echo off
if not exist c:\temp\casssql.mdb goto AlreadyDeleted
del c:\temp\casssql.mdb
if not exist c:\temp\casssql.mdb goto AlreadyDeleted
echo ___________________________________________________
echo Delete Failed. CASS Application may already be running.
pause
goto end
:AlreadyDeleted
xcopy \\pacos01\commctr\"phone survey"\exe\casssql.mdb c:\temp
if exist c:\temp\casssql.mdb goto RunIt
echo ___________________________________________________
echo Copy to c:\temp failed. Contact IT.
goto end
:RunIt
start c:\TEMP\casssql.mdb
:end
-
All, I am new to this site, just found it today and glad I did. You asked about the above topic. I am currently using SQL Server 2000 with Access XP (2002) and Access 2003. As was mentioned above, the .ADP Project is the way to go if you want to do this. I haven't had any problems connecting to the SQL Server however, I have had a few issues that you might want to be aware of:
1) If you are going to use .ADP you need to decide what version of Access you want to use, because the default is Access 2000. There are two other versions of of .ADP (2002 & 2003). I haven't really dug in deep enough yet to get at what the differences are between the different versions. However, they are there.
2) Access has a very good reporting engine, but, I have brought it to its knees a couple of times by having a master report with a few subreports all running calculations on data from the SQL Server at the same time. The technical term for this is Windows in Molasses Mode.
3) Continuing in the reporting vein, if you need to export your data or print it into a friendly format, I would strongly suggest that you get a full version of Adobe Acrobat professional so that your PDF file will mirror exactly what you have developed in your report in Access. Where I work at, I have to distribute reports that are based on statistical analysis and calculations from numerical data along with student comments regarding the topical area asked in our online survey all married up into one report. Unfortunately, we developed wonderful reports that printed nicely, however, people wanted them softcopy, and they wouldn't export very well to Microsoft Word (This side of butt was what it looked like). Our solution became Acrobat.
Hope this helps out. We are going through a fairly large rewrite of stuff into an .ADP project because we are marrying a few different sections together so that people can work a little more collaboratively. Test Development, with Curriculum Development and Evaluations of those lessons so that we have an overall view of everything for all the parties involved. Access has become our tool of choice because it provides a quicker development platform, and .NET would take a little longer, plus people in my organization already know how to use Access in one form or another.
Tom
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply