By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,140 Members | 1,301 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,140 IT Pros & Developers. It's quick & easy.

using access on a mapped drive

P: 98
i have a database that sits on a shared drive. this drive is accessable across the country. the code in the database works fine when accessed here, but in other parts of the company that are out of state, the forms crash. its

could this be some sort of network access issue? or would working from a mapped drive not cause this sort of error?
Feb 4 '09 #1
Share this Question
Share on Google+
6 Replies


missinglinq
Expert 2.5K+
P: 3,532
This is not really my area of expertise, but there are a couple of things here I can tell you.

If by "database that sits on a shared drive" you mean that you have an unsplit database, sitting on a shared drive, that is being used by multi-users, you're sitting on a time bomb. This is a sure recipe for repeated data corruption/loss. In muti-users environments you have to split the database, placing a copy of the front end (forms, reports, queries, etc) on each user's PC, and placing a single copy of the back end (with the tables/data) on the shared drive. Access can do the splitting for you, thru Tools - Database Utilities - Database Splitter.

From trolling this and other Access forums for a number of years, I know that Access generally works fine over a hard-wired, Local Area Networks. It works horribly over wireless networks, and even worse over Wide Area Networks.

And yes, the problems associated with these networks relate to connectivity, or rather the loss of it, as you're experiencing. Hopefully someone her with expertise in the area can advise you on a better means of accomplishing your goals.

Linq ;0)>
Feb 4 '09 #2

ADezii
Expert 5K+
P: 8,623
@trixxnixon
  1. Is this a Stand-Alone or Split Database?
  2. What exactly do you mean by the Forms crash?
Feb 4 '09 #3

P: 98
@ADezii

it is not split. the application has a switch board that opens other forms depending on which button is clicked. the forms work fine on the terminals here, but when someone in NY opens the databse from the same shared drive,
it crashes.

there is a loop involving a date, where the date is taken and converted in order to determine if the request was recieved after 5:00 PM.
when it crashes it gives the can not find project or library error, and on clicking ok, the "Right" Function is highlighted.

Expand|Select|Wrap|Line Numbers
  1. Function GetTheDate()
  2. 'this function grabs the date from the date submitted written by michael colins
  3. Dim DB As Database, rs As Recordset, sSQL, DW As String
  4. Dim loopend, ct, X As Integer
  5. 'Dim sENum As String
  6.  
  7.     Set DB = CurrentDb
  8.     DueDate = Trim(Format(DueDate, "m/d/yyyy"))
  9.     sSQL = "SELECT * FROM [Holiday Table] WHERE [HolidayDate] = '" & DueDate & "'"
  10.  
  11.     Set rs = DB.OpenRecordset(sSQL)
  12.     If Not rs.EOF Then
  13.         DW = rs![HolidayDate]
  14.         GetTheDate = 1
  15.     Else
  16.        GetTheDate = 0
  17.     End If
  18.  
  19.     rs.close
  20.     DB.close
  21.  
  22. End Function
there is also an error coming from the fosUsername function, where on the second "can not find project or library" error, the "String$" function is highlighted.
Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Public Declare Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
  5. (ByVal lpBuffer As String, nSize As Long) As Long
  6.  
  7. Function fOSUserName() As String
  8. On Error GoTo fOSUserName_Err
  9.  
  10. Dim lngLen As Long, lngX As Long
  11. Dim strUserName As String
  12.  
  13. strUserName = String$(254, 0)
  14. lngLen = 255
  15. lngX = apiGetUserName(strUserName, lngLen)
  16.  
  17. If lngX <> 0 Then
  18. fOSUserName = Left$(strUserName, lngLen - 1)
  19. Else
  20. fOSUserName = ""
  21. End If
  22.  
  23.  
  24. fOSUserName_Exit:
  25. Exit Function
  26.  
  27. fOSUserName_Err:
  28. MsgBox Error$
  29. Resume fOSUserName_Exit
  30. End Function
  31.  
On the references tab, the “Microsoft calendar control 8.0” is marked as missing.

have you ever experienced errors like these appearing on one terminal but not on another?
Feb 6 '09 #4

Expert 100+
P: 1,287
Yes I have. On machines without identical installations, missing references will cause you odd errors where Access will fail to locate simple functions like String$.

You have some options.

1. Make sure every computer your application will be used on has the same setup.
2. Find the oldest reference dlls you can to build your project with, because they will run fine if the target machine has a newer one, but not if they have an older version or no version.
3. Remove the reference from your project and use late binding on objects. Then you just handle the runtime error smoothly when the target machine doesn't have the capability to create those objects.

Number 2 may work for you, but I think 3 is the best solution, giving your app a better chance not to fail on any machine.
Feb 6 '09 #5

DonRayner
Expert 100+
P: 489
Another option would be to place the database on a terminal server and have the users remote desktop onto the server to run the database. This would also speed things up.
Feb 6 '09 #6

ADezii
Expert 5K+
P: 8,623
Just looking at a segment of your code, and depending on certain conditions, you may wish to make some modifications, such as:
Expand|Select|Wrap|Line Numbers
  1. Function GetTheDate() As Boolean
  2. 'this function grabs the date from the date submitted
  3. 'written by michael colins
  4. Dim DB As DAO.Database
  5. Dim rs As DAO.Recordset
  6. Dim sSQL As String
  7. Dim DW As Date
  8.  
  9. 'Why these Variable Declarations? Why are ct and loopend
  10. 'Declared as Variants? Where are they used?
  11. 'Dim loopend
  12. 'Dim ct
  13. 'Dim X As Integer
  14.  
  15. Set DB = CurrentDb
  16.  
  17. 'Do you need a True DATE Variable in DueDate?
  18. DueDate = CDate(Trim(Format(DueDate, "m/d/yyyy")))
  19.  
  20. 'If [HolidayDate] is a DATE/TIME Field in Holiday Table, you have
  21. 'the wrong Qualifiers (')
  22. sSQL = "SELECT * FROM [Holiday Table] WHERE [HolidayDate] = #" & DueDate & "#"
  23.  
  24. Set rs = DB.OpenRecordset(sSQL, dbOpenForwardOnly)
  25.  
  26. If Not rs.BOF And Not rs.EOF Then
  27.   DW = rs![HolidayDate]
  28.   GetTheDate = True
  29. Else
  30.   GetTheDate = False
  31. End If
  32.  
  33. rs.Close
  34. Set rs = Nothing
  35. End Function
Feb 7 '09 #7

Post your reply

Sign in to post your reply or Sign up for a free account.