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"? If
I look at SQL Server activity, I can see "one_user" is connected to
"MYDATABASE".

Thanks,
Eric

 

[Steve Schapel]

Eric,

As your question is based on VBA procedures, and does not realte to Access
Macros, you would have a better chance of a good answer if you repost to a
more applicable newsgroup. Perhaps microsoft.public.access.externaldata.

 

[Wolfgang Kais]

Hello Eric.

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
[...]

I guess that it's a name resolution problem. Try to "ping" the server:
Open a command prompt and enter: ping mysqlserver
If you don't get 4 successful answers displayed on the two computers,
compare the DNS and WINS settings in the TCP/IP properties of the
network adapters and also check the hosts and lmhosts files.

 

[Eric Bragas]

Wolfgang,

Thanks for your advice, but I don't think it applies very well in this
case. The server name is hardcoded into the mdb file. If one user is
able to connect to the server, then then server name is correct for
every user that attempts to connect to the server. It's not even a
variable, it's a literal string. You can see that in the
GetSQLServerConnectString function above.

Eric

 

[Eric Bragas]

Thanks, Steve, I'll give that a try.

 

[Wolfgang Kais]

Hello Eric.

Wolfgang,

Thanks for your advice, but I don't think it applies very well in this
case. The server name is hardcoded into the mdb file. If one user is
able to connect to the server, then then server name is correct for
every user that attempts to connect to the server. It's not even a
variable, it's a literal string. You can see that in the
GetSQLServerConnectString function above.

I did not say that it was a name problem, but a name resolutuion problem.
Before making a connection to server MYSQLSERVER, the client computers
have to resolve an IP address for that name. On a computer that is well
configured, a "ping" for that servername will result in 4 replies from
the correct server. Ask you network administrator for help.

 

[Eric Bragas]

Wolfgang,

Thanks for the clarification, I will definitely check that out. I
wouldn't have considered that possibility.

The user hasn't contacted me in a week so I haven't been able to try
your advice yet. I will post the results when the time comes.

Again, thank you.

-Eric