Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|