asked on

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.

fruhj

is your table name spelled right? Individus?

SOLUTION

fruhj

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

neonlights

ASKER

is your table name spelled right? Individus?
YES

neonlights

ASKER

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?

ASKER CERTIFIED SOLUTION

Arthur_Wood

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

neonlights

ASKER

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...

neonlights

ASKER

Thank you very much for your link fruhj.

Very useful, thanks..

Arthur_Wood

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

neonlights

ASKER

Thanks,.. Arthur_Wood.