Retrieving records

[biggvito]

I have a table named "customer" in access 2000. it contains about 20 records with its main fields being "ID","Name","Address" and "Tel No". I was wondering if anyone could please tell me the program code of how to get each record one by one and put certain fields from each records into a list in VB. I know that i use .additem to put in the list but its just a matter of actually accessing the table and retrieving the data record by record from VB.

Any help would be greatly appreciated.

 

[wendas]

Could you confirm if you are using VBA thru Access, or are you trying to tie to an Access data base but truely using VB? Access is still better using DBO code which is an easy open & close.. VBA you should use ADO.. Which most people would start a beginner with the ADO Data Control, but I don't recommend, because my experience is that somewhere down the road the design will fail. But true ADO Data manipulation is a tough thing to explain...

Let me know if it is DAO or ADO you need.. then I will try to conjure you up a (will work in most cases. In the simplest form.) That will get you a very small startup base.
wenda


But then I would recommend a book. (if ADO something like ADO 2.1 from Wrox was a Godsend for my partner & I when we had to learn it.) because the open can be streamline a hundred diff. ways, and if you plan to do more programming, then you will need to learn updating,adding,deleting, filtering, finding keys.. how to update with safty catches.. etc. etc...

File manipulation is not a topic that can be covered in 2 paragraphs.

 

[wendas]

As luck would have it one of my on-line courses just gave a quick review of ADO code & simplified it where it will work for you.. But going beyond opening up a table & reading it record for record from start to finish will require you to study this subject further.. Below is code for ADO..

If you are working in Access.. It may also work depending on the version of Access you have.. I belive Access is only in 2000, & you do not have to do the first part of setting up the Reference to use it.

SIMPLE ADO CODE:

BEFORE CODING PROGRAM NEED TO REFERENCE ADO

On the project menu, click References
Click Microsoft ActiveX Data Objects 2.? Library (2.? = highest version you got)


PROGRAM CODE
THIS PROGRAM WILL OPEN & WORK WITH THE MS NWIND DATABASE....
THE SCREEN IS SET WITH ONE BUTTON NAMED CMDCONNECT... AND 3 TEXTBOXES IN AN ARRAY FROM 0 TO 2 NAMED TXTNAME.


Option Explicit
Dim cnNorthWind As Connection
Dim rsCustomer As ADODB.Recordset
Dim KO As Integer

Private Sub cmdConnect_Click()


'CONNECTION - 1 OPEN CAN SERVICE MANY TABLES
Set cnNorthWind = New Connection
cnNorthWind.ConnectionString = "Provider=Microsoft.jet.oledb.3.51;" & _
"Data Source=D:\Program files\Microsoft visual studio\" & "VB98\Nwind.mdb"
cnNorthWind.open

' NEED PER TABLE
Set rsCustomer = New Recordset

rsCustomer.open "Select * from customers", cnNorthWind


' YOUR TABLE SHOULD BE OPEN AT THIS POINT..
'This displays 3 Company Names from the table.
For KO = 0 To 2
If Not rsCustomer.EOF Then
txtname(KO) = rsCustomer!CompanyName
rsCustomer.movenext
End If
Next KO

'CLOSE THE TABLE
rsCustomer.Close
Set rsCustomer = Nothing

'CLOSE THE CONNECTION
cnNorthWind.Close
Set cnNorthWind = Nothing

End Sub