Data from Tables

[Lawrence Gould]

I am currently producing an Epos application in Access 97, I have used a Make Table query to produce a table of 3 fields namely Ref Desc Price, the number of records is determined by the number of each item purchased.
I need to extract each record as 3 string variables in order to send them to a serial printer. I have tried pointing a string at the table using the Build function but i can only pick up the first record, how can i pick up subsequent record to EOF ?. I have also tried using ADODB to get a recordset but i get error messages when trying to open the connection i have loaded the appropriate library but when i use this code
Dim Cnn1 As ADODB.Connection
Set Cnn1 = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
Set myRecordSet.ActiveConnection = Cnn1

I get error CurrentProject Variable not defined and when i set this to the specific database ie DB5 i get an error with .Connection i get Method or Data member not found ?? Does any one have any ideas ??

 

[reclusivemonkey]

Dim Cnn1 As ADODB.Connection
Set Cnn1 = CurrentProject.Connection
Dim myRecordSet As New ADODB.Recordset
Set myRecordSet.ActiveConnection = Cnn1

I think the "Set myRecordSet.ActiveConnection = Cnn1" is the one giving you problems. I am by *no* means an ADODB expert, but try the following (obviously with whatever operation you want to perform in the middle of your loop);

Dim cnConnection As Connection
Dim rs As New ADODB.Recordset
Set cnConnection = CurrentProject.Connection
strSQL = "[I]Your SQL String[/I]"
rs.Open strSQL, cnConnection, adOpenStatic, adLockReadOnly
Do While Not rs.EOF
[I][insert your actions here][/I]
Loop