PC Review


Reply
Thread Tools Rate Thread

SQL Server does not exist or access denied

 
 
Eric Bragas
Guest
Posts: n/a
 
      3rd Dec 2008
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
 
Reply With Quote
 
 
 
 
Steve Schapel
Guest
Posts: n/a
 
      3rd Dec 2008
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.

--
Steve Schapel, Microsoft Access MVP

"Eric Bragas" <(E-Mail Removed)> wrote in message
news:(E-Mail Removed)...
> 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."



 
Reply With Quote
 
 
 
 
Wolfgang Kais
Guest
Posts: n/a
 
      3rd Dec 2008
Hello Eric.

Eric Bragas wrote:
> 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.

--
Regards,
Wolfgang


 
Reply With Quote
 
Eric Bragas
Guest
Posts: n/a
 
      4th Dec 2008
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
 
Reply With Quote
 
Eric Bragas
Guest
Posts: n/a
 
      4th Dec 2008
Thanks, Steve, I'll give that a try.
 
Reply With Quote
 
Wolfgang Kais
Guest
Posts: n/a
 
      5th Dec 2008
Hello Eric.

Eric Bragas wrote:
> 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.

--
Regards,
Wolfgang
(..., MCSE, ...)


 
Reply With Quote
 
Eric Bragas
Guest
Posts: n/a
 
      8th Dec 2008
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
 
Reply With Quote
 
 
 
Reply

Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Connect SQL server via USB give "Server does not exist or access denied" after few succeful connect. but always work wireless Vincent Microsoft Dot NET Compact Framework 4 5th Sep 2006 01:51 AM
"SQL Server does not exist or access denied" frank Microsoft Access 2 18th Oct 2004 04:23 AM
System.Data.SqlClient.SqlException: SQL Server does not exist or access denied. dinesh Microsoft Dot NET Framework 0 10th May 2004 05:42 AM
SQL Server does not exist or access denied. gg Microsoft Dot NET Framework 2 7th Aug 2003 03:28 PM
SQL Server does not exist or access denied. Tom Jorgenson Microsoft ADO .NET 1 31st Jul 2003 09:06 AM


Features
 

Advertising
 

Newsgroups
 


All times are GMT +1. The time now is 11:08 PM.