SQL Server does not exist or access denied.

[Eric Bragas]

Hi All,

I have an Access 2003 file that keeps giving the same error message to
two users on file open, but the other two users are not getting the
error. I'm responsible for figuring out why this error is occurring
so that I can resolve it, but so far I'm stumped. The error message
the user receives is "SQL Server does not exist or access is denied."

The very first form that appears on startup sets a "strSQL" value and
then calls the following declared procedure:

Set rst = GetRecordset(strSQL, GetSQLServerConnectString)

Since GetSQLServerConnectString is a function, it executes before
GetRecordset. It is:

Public Function GetSQLServerConnectString()
GetSQLServerConnectString = "Provider=SQLOLEDB.1;Persist Security
Info=True;" & _
"User
ID=one_user;Timeout=60;Password=one_user_pw;" & _
"Data Source=MYSQLSERVER;" & _
"Initial Catalog=MYDATABASE;"
End Function

And then the GetRecordset function gets called:

Public Function GetRecordset(strSQL, connectionString)
'This function returns a recordset variable; it encapsulates
'all the commonly-used variables for calling disconnected recordsets
'for read-only operations

Const adOpenForwardOnly = 0
Const adLockReadOnly = 1
Const adUseClient = 3

Dim objRs
Dim oConn As New ADODB.Connection
Dim cmd As New ADODB.Command
Dim rst As New ADODB.Recordset

oConn.Open connectionString
.....
End Function


A user who's file keeps throwing the error on file open says that:
1. If she chooses "debug," she's taken straight to the oConn.Open
connectionString line;
2. If she clicks "OK" at the error, she's able to use the file as
normal. (Changes save, etc.)

So my question:
How in the world can this be causing a "SQL Server does not exist or
access denied" error when every user is connecting as "one_user"?

Thanks,
Eric

 

[Simon Francesco]

Hi Eric,
Are these users on different PCs?
Could it be firewall issues?
Can you connect using osql or SSMS from one of the problems PCs??
I am guessing 'Data Source=MYSQLSERVER' is not the acutal server name, could
you be having name resolution problems???
Are all users accessing the server remotely or is it hosted on one of the
user's PCs, therefore is TCPIP switched on??


What happens if you put a break point on the 'oConn.Open connectionString'
line so you intercept it first time, as my reading your post, the line tries
to execute once, throws an unhandled error then executes a second time but
this time successfully, is that correct??

Just thinking off the top of my head.

Cheers
Simon