How can a current record counter accessed from a query?

[wallpack]

The application is a Server with 100 clients on it collecting measurement data. The clients are running a compiled Acess/VB6 app. w/RDO. The Odbc linked tblMaster consists of SampleDateTime, FileName, OperatorCode, Observation,RefNo. When tblMaster is updated-through any of the 100 clients at any time, we would like to resequence all the records in tblMaster with the same filename as the current filename in the client, using the RefNo to reflect its position with respect to the SampleDateTime. We need the server to do the work! This is easily done with a Stored Procedure in SQL 7. Prefer not to write a UDF. A possible-but messy solution--
1. "Execute" create temp table including a RefNo-Autonum.
2. "Execute" an append to the table with desired Filename.
3. "Execute" and update to tblMaster from the temp table.
4. Delete temp table.

Any suggestions?

 

[JustKIDn]

I know in Access, you can use a pass-through query.
It makes the server do the work.
I haven't tried to do a count this way but I'm sure you can.

Does this help?
tgus

____________________________
Families can be together forever...

 

[wallpack]

I really don't know how to get a specific query(s) to accomplish what we need. I would write something like this: 1. Zero a counter. 2. Open a recordset with the desired fields and the requested sorting. 3. Fetch each record out of the recordset. 4. Increment a Counter. 5. Update the recordset field "RefNo" = to the Counter. 6. Continue until all records have been updated. 7. Update the Master table with the information. The counter is the tricky part??? The recordset could contain 100+ thousands of records.

 

[JustKIDn]

Seems to me, you could;

Dim rs AS Recordset, ...

Do While
...
strSQL = "INSERT INTO ..."
counter = counter + 1
Loop

Just an example, but this way you write one record then update the counter and anything else you want to do and then loop through again.

Shoot me if I'm wrong. But it seems right.

I'll let you work out the details.

Does that help?
tgus

____________________________
Families can be together forever...

 

[wallpack]

Could you please expand on how the counter=counter + 1 is part of a query or procedure that is executed by the server. SQL code can be passed through a ADO connection where, as shown in the Connector/ODBC manual on the MYSQL web site, like the following code:
Private Sub myodbc_ado_Click()

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim fld As ADODB.Field
Dim sql As String

'connect to MySQL server using MySQL ODBC 3.51 Driver
Set conn = New ADODB.Connection
conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};"_
& "SERVER=localhost;"_
& " DATABASE=test;"_
& "UID=venu;PWD=venu; OPTION=35"

conn.Open

'create table
conn.Execute "DROP TABLE IF EXISTS my_ado"
conn.Execute "CREATE TABLE my_ado(id int not null primary key, name varchar(20)," _
& "txt text, dt date, tm time, ts timestamp)"

'direct insert
conn.Execute "INSERT INTO my_ado(id,name,txt) values(1,100,'venu')"
conn.Execute "INSERT INTO my_ado(id,name,txt) values(2,200,'MySQL')"
conn.Execute "INSERT INTO my_ado(id,name,txt) values(3,300,'Delete')"



lk

 

[JustKIDn]

It looks to me that if you're Dropping the table and recreating it. Then make your ID field an autonumber. It will start at 1 and continue to count for you.

Then you won't need to do any fancy counting.

But when you INSERT, don't put a value into the ID field. Let the DB do it for you. Just use single quotes.

conn.Execute "INSERT INTO my_ado(id,name,txt) values('','venu')"

Except for that, it looks like you got it. Nice example of code.
But I'm not sure about writing 100 + thousand lines of inserts.

Have you got a loop worked out?
tgus

____________________________
Families can be together forever...