using access on a mapped drive

[trixxnixon]

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?

[missinglinq]

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

[ADezii]

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?

1. Is this a Stand-Alone or Split Database?
2. What exactly do you mean by the Forms crash?

[trixxnixon]

1. Is this a Stand-Alone or Split Database?
2. What exactly do you mean by the Forms crash?

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.

Function GetTheDate()
'this function grabs the date from the date submitted written by michael colins
Dim DB As Database, rs As Recordset, sSQL, DW As String
Dim loopend, ct, X As Integer
'Dim sENum As String
  
    Set DB = CurrentDb
    DueDate = Trim(Format(DueDate, "m/d/yyyy"))
    sSQL = "SELECT * FROM [Holiday Table] WHERE [HolidayDate] = '" & DueDate & "'"
  
    Set rs = DB.OpenRecordset(sSQL)
    If Not rs.EOF Then
        DW = rs![HolidayDate]
        GetTheDate = 1
    Else
       GetTheDate = 0
    End If
         
    rs.close
    DB.close
    
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.

Option Compare Database
Option Explicit

Public Declare Function apiGetUserName Lib "advapi32.dll" Alias "GetUserNameA" _
(ByVal lpBuffer As String, nSize As Long) As Long

Function fOSUserName() As String
On Error GoTo fOSUserName_Err

Dim lngLen As Long, lngX As Long
Dim strUserName As String

strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)

If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If


fOSUserName_Exit:
Exit Function

fOSUserName_Err:
MsgBox Error$
Resume fOSUserName_Exit
End Function

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?

[ChipR]

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.

[DonRayner]

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.

[ADezii]

Just looking at a segment of your code, and depending on certain conditions, you may wish to make some modifications, such as:

Function GetTheDate() As Boolean
'this function grabs the date from the date submitted
'written by michael colins
Dim DB As DAO.Database
Dim rs As DAO.Recordset
Dim sSQL As String
Dim DW As Date

'Why these Variable Declarations? Why are ct and loopend
'Declared as Variants? Where are they used?
'Dim loopend
'Dim ct
'Dim X As Integer
  
Set DB = CurrentDb

'Do you need a True DATE Variable in DueDate?
DueDate = CDate(Trim(Format(DueDate, "m/d/yyyy")))

'If [HolidayDate] is a DATE/TIME Field in Holiday Table, you have
'the wrong Qualifiers (')
sSQL = "SELECT * FROM [Holiday Table] WHERE [HolidayDate] = #" & DueDate & "#"
  
Set rs = DB.OpenRecordset(sSQL, dbOpenForwardOnly)

If Not rs.BOF And Not rs.EOF Then
  DW = rs![HolidayDate]
  GetTheDate = True
Else
  GetTheDate = False
End If
  
rs.Close
Set rs = Nothing
End Function