Hi,
I got an ado code which I am running from Access 2000 front end. THis code is supposed to run a stored procedure on sql server backend by passing parameters to update records. However, I am getting the error message Sql server does not exist or access denied. I am running the sql server on local machine. I know that the userid and password is correct. However, I have no idea why I am having this problem. I would appreciate any help on this matter. Thanks in advance.
CODE:
Private Sub cmdRunProc_Click()

  Dim conn As ADODB.Connection
  Dim cmdUpdate As ADODB.Command
  Dim lngRecs
  Dim strType
  Dim curPercent


Set conn = New ADODB.Connection
Set cmdUpdate = New ADODB.Command

myDSN = "Provider=SQLOLEDB;Data Source=sqlpubs; " & _
              "Initial Catalog=pubs; user id=sa; password=alloyd"

conn.Open myDSN
Set cmdUpdate.ActiveConnection = conn


  ' Get the form values
  strType = Forms!frmParamPassing!txtBookType
  curPercent = Forms!frmParamPassing!txtPercent


  ' Set the properties of the command
  With cmdUpdate
    .ActiveConnection = conn
    .CommandText = "usp_UpdatePrices"
    .CommandType = adCmdStoredProc

    ' Add the parameters
    .Parameters.Append .CreateParameter("@Type", adVarWChar, adParamInput, 12, strType)
    .Parameters.Append .CreateParameter("@Percent", adCurrency, adParamInput, , curPercent)

   ' Execute the command
    .Execute lngRecs, , adExecuteNoRecords
  End With


  ' And finally tell the user what's happened

   MsgBox ("Procedure complete. Records were updated")

  Set conn = Nothing
  Set cmdUpdate = Nothing

End Sub

Can you try running a simple SELECT query with that DSN to see if that will run?

Jack,

Is that your sql server name is "sqlpubs"? can you try connecting to it using the same username and password with SQL Query Analyser and see if that works just to double check...

Cheers

_________________________
- Vijay G
Strive for Perfection

Not sure if you are missing a SEMI COLON there.               Cheers

_________________________
- Vijay G
Strive for Perfection

Hi Folks,
I checked my sql server name and sure enough it did not match the sql server name that I have in code. After changing the name the problem got resolved. Thanks a lot guys and especially to Vijay who pointed out to check the name. The procedure is not changing any value to the backend. I am suspecting it is because I have to send numeric value for the percent that is not being implicitly converted in the code. Not sure. Need more digging to do. Thanks again. Regards.