Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 170
  • Last Modified:

DB Connection Question

Hi experts,

I am using Access 97, and VB 6. However, I would like to make connection string for any access database.. (97, 2000...) - Different user might have different versions of access database.

in my frmmain, I have:

Option Explicit
Dim db As Database
Dim rs As Recordset

Private Sub Form_Load()
    Set db = OpenDatabase(App.Path & "\db_main.mdb")
    Set rs = db.OpenRecordset("tblclientsindividus") ***** GETTING ERROR IN HERE : "TYPE MISMATCH"
    frmmain.lvwcp.ListItems.Clear
    While Not rs.EOF
         Set lstlvwcp = frmmain.lvwcp.ListItems.Add(, , rs!Nom)
         Set lstlvwcp = frmmain.lvwcp.ListItems.Add(, , rs!Prenom)
         rs.MoveNext
    Wend
    If rs.RecordCount = 0 Then
        MsgBox "There are no records on the database"
    Else
        frmmain.lvwcp.Enabled = True
    End If
end sub

Can someone help me why I am getting this error message.
0
neonlights
Asked:
neonlights
  • 5
  • 2
  • 2
2 Solutions
 
fruhjCommented:
is your table name spelled right? Individus?
0
 
fruhjCommented:
also a nice reference for connection strings is http://www.connectionstrings.com
0
 
neonlightsAuthor Commented:
is your table name spelled right? Individus?
 YES
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
neonlightsAuthor Commented:
In my previous VB programs, I always used this method for opening connection:


Public Const sCnn = "Provider=Microsoft.Jet.OLEDB.4.0;Persist Security Info=False;Data Source=C:\db\db1.mdb"
Set rs = New ADODB.Recordset
Set cn = New ADODB.Connection

Private Sub Main()

cn.CursorLocation = adUseClient
cn.Open sCnn

rs.Open "Select * From Agents", cn, 3
    Do Until rs.EOF
        FrmLoginPage.Cbousernom.AddItem rs("Nom")
        rs.MoveNext
    Loop
        rs.Close
        Set rs = Nothing
end Sub
******************************************
But, I recently, checked someone's job, and they are using:

Option Explicit
Dim db As Database
Dim rs As Recordset

Private Sub Form_Load()
        Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\db1.mdb")
end sub

Public Sub ContactView()
   Set rs = db.OpenRecordset("Contacts")
   ........
End Sub

I found this method is much easier and cleaner, less coding.. What is the difference..  Because, when I open a table:

I state:
rs.Open "Select ID, Name from Contacts Where ID = " & Me.TxtID.Text & " ", cn, adOpenKeyset, adLockPessimistic

What is ADO?

0
 
Arthur_WoodCommented:
this code:

Option Explicit
Dim db As Database
Dim rs As Recordset

Private Sub Form_Load()
        Set db = DBEngine.Workspaces(0).OpenDatabase(App.Path & "\db1.mdb")
end sub

Public Sub ContactView()
   Set rs = db.OpenRecordset("Contacts")
   ........
End Sub


is using DAO (Data Access Objects) as the Data Access Technology (which is the only choice when using Access 97).

ADO (ActiveX Data Objects) is a newer, and better Data Access Technology, that was introduced with Access 2000.  Both have a RecordSet object, but the two are internally DIFFERENT, so a DAO recordset is not the same thing as an ADO Recordset.

AW
0
 
neonlightsAuthor Commented:
Hi AW,

Thanks for your info. Now, I understood, that means that I was using the properway(Better Data Access Technology)

So, it is the best thing to use is ADO?

Thanks...
0
 
neonlightsAuthor Commented:
Thank you very much for your link fruhj.

Very useful, thanks..

0
 
Arthur_WoodCommented:
I my experience, it depends on precisely what you are doing.  But in general, ADO is a better choice, all other factors being equal.

AW
0
 
neonlightsAuthor Commented:
Thanks,.. Arthur_Wood.

0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 5
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now