ADO, VBA and SQL
 

Hi All!

I have a piece of code that was taken from a running
application. It uses ADO 2.5 to access MS Access and MS
SQL server. The code stops running when trying to connect,
and the message is # 80004005. I have added MS ADO 2.5
library as available VBA reference, and restored SQL
database from a backup. I did not set any SLQ users other
than those restored from the backup, and I am logging to
Win2000 station with different user id and password. For
this code to work, do I need to set SQLOLEDB as User Data
Source from SQL server in the ODBC Data Administrator?

The code is:

Option Explicit
Dim cDC_OK As Boolean
Dim cDC As New ADODB.Connection

Public Sub DC_Open()

On Error GoTo ErrHandler

cDC.Provider = "SQLOLEDB"
cDC.Open "Data Source= 192.168.1.113; Initial Catalog =
DB1; UID=user1;PASSWORD=mis "
cDC_OK = True
cDC.Close


Exit Sub
ErrHandler:
MsgBox Err.Description
End Sub


Thanks,
zif

ADO, VBA and SQL
 

Hi zif,

Can you connect to the SQL Server db using the SQL username/password you
have in your connection string? Open Query Analyzer or some other querying
tool and try to connect using that information - does it work? If not, then
you have to set up a new SQL user for the db. I'm not 100% sure on this,
but I don't think that restoring a db from backup will get the SQL logins
from the old db (unless you restored the Master db as well).

If you can log in elsewhere, then we can rule that out and focus on the
code.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


zif wrote:
Hi All!

I have a piece of code that was taken from a running
application. It uses ADO 2.5 to access MS Access and MS
SQL server. The code stops running when trying to connect,
and the message is # 80004005. I have added MS ADO 2.5
library as available VBA reference, and restored SQL
database from a backup. I did not set any SLQ users other
than those restored from the backup, and I am logging to
Win2000 station with different user id and password. For
this code to work, do I need to set SQLOLEDB as User Data
Source from SQL server in the ODBC Data Administrator?

The code is:

Option Explicit
Dim cDC_OK As Boolean
Dim cDC As New ADODB.Connection

Public Sub DC_Open()

On Error GoTo ErrHandler

cDC.Provider = "SQLOLEDB"
cDC.Open "Data Source= 192.168.1.113; Initial Catalog =
DB1; UID=user1;PASSWORD=mis "
cDC_OK = True
cDC.Close


Exit Sub
ErrHandler:
MsgBox Err.Description
End Sub


Thanks,
zif

ADO, VBA and SQL
 

Jake,

Thanks for your reply. I've used INTEGRATED SECURITY=sspi
in the SQL database opening string and it worked right
away.

zif

-----Original Message-----
Hi zif,

Can you connect to the SQL Server db using the SQL
username/password you
have in your connection string? Open Query Analyzer or
some other querying
tool and try to connect using that information - does it
work? If not, then
you have to set up a new SQL user for the db. I'm not
100% sure on this,
but I don't think that restoring a db from backup will
get the SQL logins
from the old db (unless you restored the Master db as
well).

If you can log in elsewhere, then we can rule that out
and focus on the
code.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address
unmonitored]


zif wrote:
Hi All!

I have a piece of code that was taken from a running
application. It uses ADO 2.5 to access MS Access and MS
SQL server. The code stops running when trying to
connect,
and the message is # 80004005. I have added MS ADO 2.5
library as available VBA reference, and restored SQL
database from a backup. I did not set any SLQ users
other
than those restored from the backup, and I am logging to
Win2000 station with different user id and password. For
this code to work, do I need to set SQLOLEDB as User
Data
Source from SQL server in the ODBC Data Administrator?

The code is:

Option Explicit
Dim cDC_OK As Boolean
Dim cDC As New ADODB.Connection

Public Sub DC_Open()

On Error GoTo ErrHandler

cDC.Provider = "SQLOLEDB"
cDC.Open "Data Source= 192.168.1.113; Initial Catalog =
DB1; UID=user1;PASSWORD=mis "
cDC_OK = True
cDC.Close


Exit Sub
ErrHandler:
MsgBox Err.Description
End Sub


Thanks,
zif

.