Solved

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

Posted on 2003-11-26
9
880 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
 
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

746 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

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now