SQL server does not exist or access denied

[vanessa via OfficeKB.com]

Can i know why my coding here diplay the error SQL server does not exist or
access denied?

Public Sub GetColNum(ByRef colCount As Integer)

Dim con, recordSet, cmdText

'Create a connection to ms sql server
Set con = CreateObject("ADODB.Connection")
Set recordSet = CreateObject("ADODB.Recordset")

con.Provider = "SqlOleDB"
con.Properties("Data Source").Value = "BBIPROD"
con.Properties("Initial Catalog").Value = "bbiprod"
con.Properties("Integrated Security").Value = "SSPI"

' con.ConnectionString = driver=SQL Server};" &
"server=cindy;uid=Cindy;pwd;database=pubs"

con.Open

'Prepare command string to get number of columns
cmdText = "SELECT count(*) as column_num from information_schema.columns
where table_name = 'tbl_request'"

'Open connection
recordSet.Open cmdText, con
colCount = recordSet.Fields("column_num")


MsgBox ("In Getcolumn() " + Str(colCount))

'Close and set to nothing
recordSet.Close
con.Close

Set recordSet = Nothing
Set con = Nothing


End Sub


Thank you very much oh.....cos i am first time using databace connection.

 

[Frank Lehmann]

Hi Vanessa,

Check the following:

Are you sure that you have an sql server that can be accessed by name "BBIPROD"?
Alternatively, use the IP address of the sql server in this place:
con.Properties("Data Source").Value = "xxx.xxx.xxx.xxx"

Are you sure that you can log on to the database with Integrated Security?
Someone must have configured that for you.
Alternatively, use
con.Properties("User ID").Value =
con.Properties("Password").Value =
Of course, someone must have configured that, too.

For these two points, check with your administrator.

So much for reasons why you get this error.

Cos you are first time using database connection:
There is room for improvement of your VBA code.
For instance, declare variables with the appropriate type they are used for:

Dim con As ADODB.Connection
Dim recordSet As ADODB.Recordset
Dim cmdText As String

Your variables are all of type variant, which is very unfavorable.

In order for ADODB references to work, you must add a
reference to your macro, that is
"Microsoft ActiveX DataObjects 2.x Library"

Other code improvements are imaginable, but first things first.

Regards,
Frank

 

[vanessa via OfficeKB.com]

Hi Frank,

Thank you very much oh....i can access my database now.
i put the wrong name for my SQL server support to be name as "localhost" and
add this field also useful to access my database:
con.Properties("User ID").Value =
con.Properties("Password").Value =

thank you.

regards,
vanessa