Ok here is some background. We have a remote office, connected to a DSL connection. We use a VPN to connect the remote office to our office to form a WAN. We have also got a visual foxpro database in the main office which we wish to connect with using ODBC.

This application queries the database and retrieves information from it. No updating or deleting.


'this function accepts the short job name, and extract the names of client, job,
'and the folder where all files concerning the job is located
Sub get_info_by_jobnumber()

'set of parameters and variables
Dim dbw As Workspace 'workspace containing connection
Dim con_string As String 'string used for creating new connection to database
Dim docmngc As Connection 'object used as pointer to database connection
Dim msg As String
Dim qs As String 'string used for querrying database
Dim rs As Recordset
Dim flag As Boolean
Dim myclientcode As String
Dim mydiskloc As String

'create a new workspace and include it in the workspace array
Set dbw = CreateWorkspace("manager", "admin", "", dbUseODBC)
Workspaces.Append dbw

'set string for creating new connection
con_string = "ODBC;DATABASE=" & docmng_name & ";UID=" & docmng_uid & ";PWD=" & docmng_pwd & ";DSN=" & docmng_dsn

'open new connection to database from client machine, on error divert to "connerror"
On Error GoTo connerror
Set docmngc = dbw.OpenConnection("anything", dbDriverNoPrompt, False, con_string)

'set query string to get client name and job name details
qs = "SELECT shortjobno, clientcode, projectname, name as client_name FROM project " _
& "INNER JOIN client ON project.clientcode = client.code WHERE shortjobno='" _
& jobnumber.Text & "'"

'send query and receive query result as recordset object
Set rs = docmngc.OpenRecordset(qs)

flag = False
Do Until (rs.EOF Or flag = True)
If (rs!shortjobno = jobnumber.Text) Then
client_name.Text = rs!client_name
job_name.Text = rs!projectname
myclientcode = rs!clientcode
flag = True
End If



Exit Sub

msg = "I had a problem. Error Message: " & Error()
If Err.Number = 3146 Then
msg = msg & vbCrLf & "I cant seem to connect to the jobfile Database"
MsgBox msg
End If

End Sub

---------------CODE SNIPPET END--------------------

On my remote machines i have the system DSN set up to point to \\homeserver\database.dbc

Now when i run the program locally it works fine, no problem at all. WHen i run it remotely, tho it will just hang.. and not respond anymore.

Why is this? Is connect to the database thru a WAN too much? Is the query getting lost?

Does anyone know what is going on, and why my application doesn't work?

Does anyone have any solutions? or even ideas?