Connecting Access runtime app to SQL2000 in VBA

[cbirknh]

Hi,
I am trying to connect to SQL server 2000 using an application created as an access data project (*.adp) and deployed with office developer (Clients Access2002 runtime and WinNT/Server SQLServer 2000 and Win2000 server).
The application initally opens the login form and runs code to see if a connection exists; if no connection exists it prompts for UserID and password then runs the code at the bottom of this message. This code works fine from my computer but not on others. (After removing the error handling), I get the err.num -2147467259 ([DBNMPNTW] Could not connect to server). Any guidance anyone could provide on where I could be going wrong would be greatly appreciated. Thanks, Chris

Public intAttempts As Integer

Private Sub cmdEnter_Click()
On Error GoTo ErrorHandler
Dim strConnect As String
Dim num As Integer

'Make sure name is entered
If Nz(Me.UserID, "&quot = "" Then
MsgBox "Please enter your UserID"
Me.UserID.SetFocus
Exit Sub
End If

'Make sure password is entered
If Nz(Me.pword, "&quot = "" Then
MsgBox "Please enter your password"
Me.pword.SetFocus
Exit Sub
End If

strConnect = "PROVIDER=SQLOLEDB.1;PASSWORD=" & Me.pword & ";PERSIST SECURITY INFO=FALSE;USER ID=" & Me.UserID & ";Workstation ID=" & Environ("ComputerName&quot & ";INITIAL CATALOG=Bariatric;DATA SOURCE=xxx.xxx.xxx.xxx,1433"
Application.CurrentProject.OpenConnection strConnect
mdlPatients.MainMenu
Exit Sub

ExitHandler:
If intAttempts = 3 Then
num = MsgBox("Invalid UserID or Password. Registry will be closed. Contact your database administrator for assistance", vbOKOnly, "Bariatric Program Registry&quot
DoCmd.Quit acQuitPrompt
Else
num = MsgBox("Invalid User ID or Password. Please try again", vbOKOnly, "Bariatric Program Registry&quot
Me.pword = ""
Me.pword.SetFocus
Exit Sub
End If

ErrorHandler:
intAttempts = intAttempts + 1
Resume ExitHandler
End Sub

 

[jgoodman00]

Are these computers connected through your LAN?
Where is the SQL server running from?
What service pack is the server running?

I had a problem similar to this, with the connection being refused on VPN users due to SQL server (pre SP1) enumerating IP's connected to it at startup. If a new user then connected (such as through VPN) it would not permit that connection, hence the problem...


This might somehow be related to your problem... James Goodman

http://www.james-goodman.co.uk