read/write loop MS Access

  • Thread starter Thread starter kevin
  • Start date Start date
K

kevin

Hi,

Hope you can help - I can't see the wood for the trees any more.

I currently have a local MS Access file.

I'd like to do this in VB.NET:

connect to file (select * from table)
for each row in table
if TEST(row) then
remove row
else
increment a field
end if
end for

The success/failure of the TEST function is only partly dependent of
the row's contents (so we can't use the SQL UPDATE command).
The MDB file may contain thousands of rows (so, I'd like to avoid
reading it all into memory at once)

Can someone please point me in the right direction?

Thanks,
Kevin
 
kevin said:
Hi,

Hope you can help - I can't see the wood for the trees any more.

I currently have a local MS Access file.

I'd like to do this in VB.NET:

connect to file (select * from table)
for each row in table
if TEST(row) then
remove row
else
increment a field
end if
end for

The success/failure of the TEST function is only partly dependent of
the row's contents (so we can't use the SQL UPDATE command). The MDB
file may contain thousands of rows (so, I'd like to avoid reading it
all into memory at once)

Can someone please point me in the right direction?
Click to expand...

Don't know what exactly the problem is, so this is a good place to start:

http://msdn2.microsoft.com/en-us/library/e80y5yhx.aspx
http://msdn2.microsoft.com/en-us/library/ms254937.aspx
http://msdn2.microsoft.com/en-us/library/ms254950.aspx

(not part of the MSDN TOC anymore (why?))

Further, language unrelated and ADO.Net related question fit best here:
microsoft.public.dotnet.framework.adonet


Armin
 
(not part of the MSDN TOC anymore (why?))


Maybe because Access has been declared obsolete as DB backend for
applications for a while

regards

Michel
 
Michel Posseth said:
Maybe because Access has been declared obsolete as DB backend for
applications for a while
Click to expand...

:-) The link goes to the ADO.Net docs. I guess it still exits.


Armin
 
Maybe because Access has been declared obsolete as DB backend for
applications for a while
Click to expand...

Could anyone advise on a suitable backend then?

My VB.NET app will generate "tasks" to perform, and perform them. If it
can't do them for some reason, it needs to store them, and retry later.
Each task will have an "attempts field", which will be incremented on
each attempt. There could be thousands of tasks at any one time.

I'd like to process these tasks in a seperate thread, which:

1) avoids reading the whole list of uncompleted tasks into memory at once.
2) doesn't involve installing separate products onto the system (but I
can include files in my installation). I assume .NET Framework 2.

Thanks,
Kevin
 
Kevin,

In my idea are you asking two conflicting questions at ones.

How can I do this with Select * from table
How can I avoid that I read all data at once.

For the first question you can use an SQLExpress server. Works almost
equally to the Jet engine which is used in MS Access if you would OleDB, but
you would be stupid to do that better is SQLClient.

The second is that you can use a datareader and make your own SQL delete and
update statement.
if TEST(row) then
execute.nonquery with "Delete statement" remove row
else
increment a field execute.nonQuery(with update statement)
end if
end for
Click to expand...

I hope this helps,

Cor
 
Back
Top