This code stopped working after I g 1

[budbeth]

This code stopped working after I got a new machine.
I've checked the references and don't have any missing.

I keep getting the error:
Run-time error '-2147467259(800004005)':
[DBNETLIB][ConnectionOpen(Connect()).]SQL Server does not exist or access denied.

Any suggestions of what could be wrong ?

TIA for all assistance,

Beth

-------------------------------------------------------
Global cnn1 As ADODB.Connection

Public Sub TestConnection(cnn1)

Dim strDataSource As String
Dim strInitialCatalog As String

strDataSource = "DATA_01"
strInitialCatalog = "DATA_01"

Set cnn1 = New ADODB.Connection
With cnn1
cnn1.Provider = "SQLOLEDB.1"
.ConnectionString = "User ID=;Password=;" & _
"Data Source=" & strDataSource & ";" & _
"Initial Catalog=" & strInitialCatalog & ";"
.ConnectionTimeout = 25
.Open
End With
End Sub

 

[vb5prgrmr]

Have you created the DSN, the ODBC data source that you are referring to? Was the server your old machine or another?

 

[budbeth]

Yes, the system DSN is on my machine (DATA_01).
The server has not changed at all, just my desktop machine.
When I look in Enterprise Manager, the database is there (DATA_01).
Could it be something as easy as renaming the DSN so it doesn't match the database name?

 

[zemp]

Your data source and your initial catalog are both the same; "DATA_01". The 'Data Source' should be the name of your SQL server and the 'Initial Catalog' should be the actual name of the database on the server. They are not usually the same. Double check the names. Thanks and Good Luck!

zemp

 

[budbeth]

Where does the ODBC come into play then?

 

[zemp]

I don't think it does. If you make a connection with an odbc DSN then it usually looks like this.

.connectionstring="DSN=myodbc"

Where 'myodbc' is the name of your system DSN.

The way you have it you are using a direct connection through SQLOLEDB.

Using an odbc link inceases the overhead and slows the connection because you have to go through the odbc drivers. Thanks and Good Luck!

zemp

 

[budbeth]

OK - I changed the Data Source to the server name, but now I get this error:
Run-time error '-2147217843(80040e4d)':
Login failed for user 'beth'.

The SQL login for Beth has a Server Role of System Administrator. Database access is set to public, db_datareader and db_datawriter.

Maybe I didn't register the server / database correctly on the new machine???

At this point, I don't know where else to look...

Thanx much for the help - I'm getting closer

 

[zemp]

You may have to provide a user id and password. Below is what I have used in the past as part of a connection string.

UID=sa;PWD=; Thanks and Good Luck!

zemp

 

[budbeth]

IN testing - will let you know how it shakes down...

Beth