Connection failed error number 3151

[Vinod]

Hi All,

Can any one help me to resolve in connection error issue with SQL Server 2005?

I'm using the following code to execute a stored procedure in SQL Server
2005 from Access 2003. Where the connection string does not include 'DSN'
name. The same code was working couple of days back but not now. When I look
at the server name it is matching with existed one and I'm able to execute
the stored procedure in SQL Server test database.

Some times I'm getting err.Number = -2147217900 and 3151.

Set qdfPass = CurrentDb.QueryDefs("qryPass")
'//Create connection string to qrypass
qdfPass.Connect = "ODBC;DRIVER={SQL
Server};SERVER=PERSONAL-4B2728\SQLEXPRESS;DATABASE=test;Trusted_Connection"

'//Update qrypass sql statement
qdfPass.SQL = "exec Restore_DB"

CurrentProject.Connection.Execute "qryPass"
Set qdfPass = Nothing

Please help me in resolving above said issue by sharing your thoughts and
ideas which could be appreciated.

Advanced Thanks
Vinod

 

[Scott McDaniel]

Hi All,

Can any one help me to resolve in connection error issue with SQL Server 2005?

I'm using the following code to execute a stored procedure in SQL Server
2005 from Access 2003. Where the connection string does not include 'DSN'
name. The same code was working couple of days back but not now. When I look
at the server name it is matching with existed one and I'm able to execute
the stored procedure in SQL Server test database.

Some times I'm getting err.Number = -2147217900 and 3151.

Set qdfPass = CurrentDb.QueryDefs("qryPass")
'//Create connection string to qrypass
qdfPass.Connect = "ODBC;DRIVER={SQL
Server};SERVER=PERSONAL-4B2728\SQLEXPRESS;DATABASE=test;Trusted_Connection"

'//Update qrypass sql statement
qdfPass.SQL = "exec Restore_DB"

CurrentProject.Connection.Execute "qryPass"
Set qdfPass = Nothing

It seems all you're doing is executing the Restore_DB Stored Procedure. If that's the case, you really don't need to
mess with QueryDefs,just do this:

Dim con As ADODB.Connection
set con = New ADODB.Connection
con.Open " "ODBC;DRIVER={SQL Server};SERVER=PERSONAL-4B2728\SQLEXPRESS;DATABASE=test;Trusted_Connection"
con.Execute "EXEC Restore_DB"

You might also try different connect strings. I'm not sure the one you're using is correct, although it may be. Here's
an example of a SSE connection string plucked from a web page:

"Provider = SQLNCLI;Data Source = .\SQLEXPRESS;Initial Catalog = myDatabase;User ID = myUserID;Password = myPassword;"

Please help me in resolving above said issue by sharing your thoughts and
ideas which could be appreciated.

Advanced Thanks
Vinod

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Vinod]

Thanks Scott for your response,

Your suggested below code is throughing while running con.open statement.
error '-2147467259(80004005):
[Microsoft][ODBC Driver Manager] datasource not found and no default driver
specified.'

Dim con As ADODB.Connection
set con = New ADODB.Connection
con.Open " "ODBC;DRIVER={SQL
Server};SERVER=PERSONAL-4B2728\SQLEXPRESS;DATABASE=test;Trusted_Connection"
con.Execute "EXEC Restore_DB"

When I ran another your suggested code I'm getting following error.
‘error: -2147217843 (80040e4d)’
Invalid Authorization specification.

I've used like this with trusted connection, since I need to use windows
authuntication.

Set cnn = New ADODB.Connection

cnn.Open "ODBC;DRIVER={SQL
Server};SERVER=PERSONAL-4B2728\SQLEXPRESS;DATABASE=test;Trusted_Connection"
cnn.Execute "EXEC Restore_DB"

Please guide me on how to resolve the said issue.

Thanks
Vinod

 

[a a r o n . k e m p f]

dude you're just plain silly

use ADO instead of DAO against SQL!!!

Dim cmd as new ADODB.command
cmd.CommandText = "EXEC MySproc"
cmd.ActiveConnection = strCnn
cmd.Execute

four lines of code-- seems a lot easier, imho

you also could do this
Dim cnn as new ADODB.connection
cnn.ConnectionString = strCnn
cnn.Open
cnn.Execute strSql

or of course, if you were using Access Data Proejcts, you would just
CurrentProject.connection.Execute "EXEC mySproc"
or
Docmd.RunSQL "EXEC mySproc"

using Jet and Linked tables-- to use data in SQL-- is kinda like using
a tricycle as a tow truck... sure, if you work your butt off-- writing
custom code-- you might be able to tow a car a little tiny bit.

but if you lost the ****ing training wheels-- and learned how to
_REALLY_ use SQL Server-- you'd be much better off