Solved

Connect to a database get an Error No :--2147024769 (VB-ADO)

Posted on 2003-11-26
9
883 Views
Last Modified: 2007-12-19
I have a terrible runtime error when connect an VB application to a database (both of MS-SQL and MS-Access)

Here my code

'For MS-SQL, connect to database server
If Not (cn Is Nothing) Then
        cn.Close
End If

With cn
        .Provider = "SQLOLEDB"
        .ConnectionString = "Data Source=" & strDatabaseServer & ";User ID=" & strUserName & "; Password =" & strUserPassWord
        .Open
End With

'For MS-Access, connect to database file
If Not (cn Is Nothing) Then
        cn.Close
End If

With cn
        .Provider = "Microsoft.Jet.OLEDB.4.0"
        .ConnectionString = "Data Source=" & strDatabase
        .Open
End With

-----------------------------------------------------
Error found

Error No : 2147024769
Description : Method 'Open' of object '_connection' failed

------------------------------------------------------
The code work is worked in the past and, I don't know why do it goes wrong now?

The permission is done (admin user)
The database target is done too, default user and password
The code of connection modul haven't been changed

Please let me know as soon as possible because of I can't continue my work without connect to the database.
0
Comment
Question by:fffworldltd
  • 5
  • 3
9 Comments
 
LVL 9

Expert Comment

by:svenkarlsen
ID: 9823661
Hi fffworldltd,

1. Have you tried to open the individual connections in separate functions for test ?

2. Have you checked the contents of strDatabaseServer and strDatabase ?

3. Have you tried closing explicitly (comment out the "If Not (cn Is Nothing) Then") ?


Kind regards,
Sven
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9823701
Hi  fffworldltd,


Give this a try for the jet connection, I'll get you a sqlconnection procedure.

Public Sub testConnection()

  Dim sJetDataPath As String
  sJetDataPath = "C:\Tabs.mdb"
  Dim cnJet As ADODB.Connection
  Set cnJet = New ADODB.Connection
 
  sCnJet = adoConnectJet40(sJetDataPath, "")
  cnJet.Open sCnJet
  cnJet.Close
  Set cnJet = Nothing

End Sub

Public Function adoConnectJet40(psDataPath, psFilePassword) As String
'   Returns Jet 4.0 ADO connect string
  Dim sProvider, sDataSource, sDBPassword

  sProvider = "Provider=Microsoft.Jet.OLEDB.4.0"
  sDataSource = ";Data Source=" & psDataPath
  If psFilePassword = "" Then
    sDBPassword = ""
  Else
    sDBPassword = ";Jet OLEDB:Database Password=" & psFilePassword
  End If

  adoConnectJet40 = sProvider & sDataSource & sDBPassword
End Function


Alan
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9823793
Hi  fffworldltd,


Give this a try for the SQL connection


Public Sub testSQLConnection()

  Dim cnSQL As ADODB.Connection
  Dim scnSQL As String
  Set cnSQL = New ADODB.Connection
 
  Dim sPassword As String
  Dim sUser As String
  Dim sCatalog As String
  Dim sDataSource As String
 
  sPassword = "xxxxxxx"
  sUser = "sa"
  sCatalog = "Northwind"
  sDataSource = "YourServer"
 
  scnSQL = adoConnectSQL(sPassword, sUser, sCatalog, sDataSource)
  cnSQL.Open scnSQL
  cnSQL.Close
  Set cnSQL = Nothing

End Sub


Public Function adoConnectSQL(psPassword, psUser, psCatalog, psDataSource) As String

'   Returns SQLOLEDB ADO connect string
  Dim sProvider, sPassword, sPersist, sUser, sCatalog, sDataSource

  sProvider = "Provider=SQLOLEDB.1;"
  sPassword = "Password=" & psPassword & ";"
  sPersist = "Persist Security Info=True;"
  sUser = "User ID=" & psUser & ";"
  sCatalog = "Initial Catalog=" & psCatalog & ";"
  sDataSource = "Data Source=" & psDataSource

  adoConnectSQL = sProvider & sPassword & sPersist & sUser & sCatalog & sDataSource
End Function

Alan :)


0
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

 
LVL 26

Expert Comment

by:Alan Warren
ID: 9823840
Hi  fffworldltd,

Improved adoConnectSQL: If no password provided uses Windows Integrated security

Public Sub testSQLConnection()

  Dim cnSQL As ADODB.Connection
  Dim scnSQL As String
  Set cnSQL = New ADODB.Connection
 
  Dim sPassword As String
  Dim sUser As String
  Dim sCatalog As String
  Dim sDataSource As String
 
  sPassword = ""
  sUser = ""
  sCatalog = "Northwind"
  sDataSource = "Leanne"
 
  scnSQL = adoConnectSQL(sPassword, sUser, sCatalog, sDataSource)
  cnSQL.Open scnSQL
  cnSQL.Close
  Set cnSQL = Nothing

End Sub


Public Function adoConnectSQL(psPassword, psUser, psCatalog, psDataSource) As String

'   Returns SQLOLEDB ADO connect string
'   If no password provided uses Windows Integrated security
 
  Dim sProvider, sPassword, sPersist, sUser, sCatalog, sDataSource
 
  If psPassword = "" Then
    sProvider = "Provider=SQLOLEDB.1;Integrated Security=SSPI;"
    sPersist = "Persist Security Info=False;"
    sCatalog = "Initial Catalog=" & psCatalog & ";"
    sDataSource = "Data Source=" & psDataSource
    adoConnectSQL = sProvider & sPersist & sCatalog & sDataSource
  Else
    sProvider = "Provider=SQLOLEDB.1;"
    sPassword = "Password=" & psPassword & ";"
    sPersist = "Persist Security Info=True;"
    sUser = "User ID=" & psUser & ";"
    sCatalog = "Initial Catalog=" & psCatalog & ";"
    sDataSource = "Data Source=" & psDataSource
    adoConnectSQL = sProvider & sPassword & sPersist & sUser & sCatalog & sDataSource
  End If
 
End Function

Alan :)

0
 

Author Comment

by:fffworldltd
ID: 9823883
Hi Sven and Alan

Thanks for you helps but it seems my problem isn't in my code. I've tested some VB applications and they get this error too, even now I can not start MS-SQL Server (it auto shutdowns). I've asked my admin but this isn't wrong about my domain user account, I plan to re-install the Window, VB 6.0, MS-SQL 2000 and MS-Access 2000. I will let you know the results when it's done. But even if it's success, I still want to know what is the true problem because I don't want to get this error when transition to my client in the future (It will be hell).

Now, I'm not at my office and I will test 2 procedure when I come back too.

Thanks
0
 

Author Comment

by:fffworldltd
ID: 9823911
Oh, I forgot, I said my MS-SQL Server is not working but I can't connect though the VB applications to the working other on LAN. The Query analyze is fine.
0
 
LVL 26

Accepted Solution

by:
Alan Warren earned 500 total points
ID: 9824185
Hi fffworldltd,

Got a neat trick for building connect strings.
Create a new text document
Rename to connect.udl
DblClick udl file to invoke universal data link wizard
On the provider Tab, choose Microsoft OLe DB Provider for SQL Server
On the Connection tab type the name of your server in Select or enter Server Name textbox
Choose option use Windows NT Integrated security
Choose option Select he databse on the server
Select your database from the dropdown
Click test connection
Close udl
Right click connect.udl and choose Open With NotePad

Should have something like this:
[oledb]
; Everything after this line is an OLE DB initstring
Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=Northwind;Data Source=Leanne

One perfecltly formed sql connect string.

By the way if your Test connection succeeds then the problem is not with sql server or the sql driver.


hth Alan :)


0
 

Author Comment

by:fffworldltd
ID: 9833099
Thanks so much Alan,

With last your answer, I've tested my MS-SQL Server and found the ODBC modul was damaged (I still don't know the cause of this damage, but not by virus) and not work correctly, I've re-install the Windows and finally all was done.

Thank you one more.
0
 
LVL 26

Expert Comment

by:Alan Warren
ID: 9833830
Hi  fffworldltd,

thanks for the points, glad you got it sorted out.
The old UDL trick has saved me a few times too, seem to get better error reporting from the UDL wiz.

Alan

0

Featured Post

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

791 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question