Accesing Records from MSAccess Database

[ShwethaDoss]

Hi!

I am trying to connect to a Access 2000 database. This is VB code ,which i have wriiten . I have to display the fields of the database in a combo box . Can you help me in debugging this ? Also , can u suggest some sites, which has information as to how to connect to a database.
Thanks in advance ,
Shwetha

Private Sub Form_Load()
Set db = New Connection
Set rs = New Recordset

db.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data source= C:\Projects\MSAccess\db1.mdb"


rs.Open "select Field from Table"

If rs.RecordCount > 0 Then
rs.MoveFirst
While Not rs.EOF
Combo1.AddItem (rs.Fields(0))
rs.MoveNext
Wend
End If

End Sub

 

[vb5prgrmr]

With your rs.Open I believe you need to do something like
[tt]
rs.Open "select Field From Table", db, adOpenForwardOnly
[/tt]
Beyond that it looks like you have everything right except for the parens around rs.fields(0) on the additem line. Also if you are just running through a recordset (as you are to load the combo box) you really do not need to check for a record. Although if you feel more comfortable in doing so then I would suggest changing your recordcount (some datasources or recordsets (if large enough) will return -1, meaning that there are more records than can be counted in the current page) to something like

[tt]
If rs.RecordCount <> 0 Then
[/tt]
or
[tt]
If rs.RecordCount <> 0 And rs.Eof = False And rs.Bof = False Then
[/tt]

To run through a recordset you really only need something like...
[tt]

Do While Not rs.Eof
Combo1.AddItem rs.Fields(&quot;Field&quot 'Makes for easier reading
rs.MoveNext
Loop
[/tt]

I hope this helps, Good Luck

 

[CCLINT]

Change:
If rs.RecordCount > 0 Then
to
If Not(rs.BOF And rs.EOF) Then

Or:

rs.MoveFirst
and then as in vb5prgrmr's example:
Do While Not rs.Eof

Also, if you use adOpenForwardOnly, then the RecordCount will be -1 (minus one), meaning, a check if RecordCount > 0 will not work. So using an EOF check is best.

Using rs.Fields(0) is faster than rs.Fields(&quot;Field&quot, if the field position will always be the same. [/b][/i][/u][sub]*******************************************************
General remarks:
If this post contains any suggestions for the use or distribution of code, components or files of any sort, it is still your responsibility to assure that you have the proper license and distribution rights to do so!

 

[GHolden]

Link below is an excellent place for looking up connection strings...

http://www.able-consulting.com/ADO_Conn.htm

There are two ways to write error-free programs; only the third one works.

 

[1127]

Hi,

Tip:

When You open the recordset (I suppouse it's ADO) define it's type.

If You open a recordset without any argument it will be an updatable one.

If You want to now if an updatable recordset (adOpenDynamic)recordcount is greater then 0 You have to check like this:

rst.Recordcount<0

An updatable recordset never will show You the rcord numbers but it will retun True if it's not 0!

Instead open the recordset adOpenDynamic (or without argument) open'it readonly (any other argument).

In this case You will receive the recordcount, and it's more economicaly.

But....If You want to find out if the recordset returns any record why dont investigate the EOF and the BOF properties of it:

If rst.EOF and rst.BOF then 'the rst it's empty

This sintax works with every type of recordset!

Tibi

 

[1127]

Hi,

The eassyest way to build up a connection string is to put an ADO control to Your form and set up the conection with it.

After that You just copy the connection string from it and You can remove the control.