About bit operate in ACCESS2000

  • Thread starter Thread starter GG
  • Start date Start date
G

GG

Hi there
Thanks Allen Browne and Gary Walter.

I make a VBA function in ACCESS2000 as Gary told me,
and named it BitAND, and make a query like :
SELECT * FROM TableName WHERE BitAnd(FieldName, 8) = 8

I am glad to see the query works well just like that in
SQL SERVER, but it runs too slowly. I have 3000 record in
my database, in SQL SERVER, it take about 2 seconds to
return the rowset, but in ACCESS2000, it spend 10 seconds.
How can i improve it? Any way best u can tell me?
Thanks!
 
GG said:
Hi there
Thanks Allen Browne and Gary Walter.

I make a VBA function in ACCESS2000 as Gary told me,
and named it BitAND, and make a query like :
SELECT * FROM TableName WHERE BitAnd(FieldName, 8) = 8

I am glad to see the query works well just like that in
SQL SERVER, but it runs too slowly. I have 3000 record in
my database, in SQL SERVER, it take about 2 seconds to
return the rowset, but in ACCESS2000, it spend 10 seconds.
How can i improve it? Any way best u can tell me?
Click to expand...
Hi GG,

There is something wrong with my
connection to newsgroups since early
yesterday morning, so apologies not
getting back to you.

Where is the data table?
-within your Access db?
-in an Access backend?
-in a SQL Server db?
-somewhere else?

How will you use this query?
-for a read-only form?
-for an editable form?
-for a report?
-something else?

Hopefully, I'll be able to read your response
before tomorrow AM.

gary
 
-----Original Message-----
Hi GG,

There is something wrong with my
connection to newsgroups since early
yesterday morning, so apologies not
getting back to you.

Where is the data table?
-within your Access db?
-in an Access backend?
-in a SQL Server db?
-somewhere else?

How will you use this query?
-for a read-only form?
-for an editable form?
-for a report?
-something else?

Hopefully, I'll be able to read your response
before tomorrow AM.

gary


.
Click to expand...

Hi Gary
I am developing an application with OLE DB, so the data
can be in ACCESS2000 and SQL SERVER. Now my data is both
in SQL SERVER and ACCESS2000 MDB files. In SQL SERVER, the
QUERY returns quickly, but when in ACCESS 2000, its very
slowly to return the rows with calling the function of VBA.
Now first, the data is in ACCESS MDB file with thouands
of record; Second the data should be editable.
How to do next?
Thank you!
 
Hi,

With Access 2000, you should be able to use BAND, if you use ADO. In the
immediate debug window, use:


? CurrentProject.Connection.Execute( "SELECT (1024+32+8+2) BAND 8
").Fields(0).Value
8

Since it is not a VBA function but a native one in Jet4.0 SQL, the
result should be faster.

In theory, we should store just one value per column. Having to use BAND
seems to indicate that this, which is sometimes referred to the
normalization rule 0, is not followed.



Hoping it may help,
Vanderghast, Access MVP
 
Hi,

With Access 2000, you should be able to use BAND, if you use ADO. In the
immediate debug window, use:


? CurrentProject.Connection.Execute( "SELECT (1024+32+8+2) BAND 8
").Fields(0).Value
8

Since it is not a VBA function but a native one in Jet4.0 SQL, the
result should be faster.

In theory, we should store just one value per column. Having to use BAND
seems to indicate that this, which is sometimes referred to the
normalization rule 0, is not followed.



Hoping it may help,
Vanderghast, Access MVP
 
-----Original Message-----
Hi,

With Access 2000, you should be able to use BAND, if you use ADO. In the
immediate debug window, use:


? CurrentProject.Connection.Execute( "SELECT (1024+32+8+2) BAND 8
").Fields(0).Value
8

Since it is not a VBA function but a native one in Jet4.0 SQL, the
result should be faster.

In theory, we should store just one value per column. Having to use BAND
seems to indicate that this, which is sometimes referred to the
normalization rule 0, is not followed.



Hoping it may help,
Vanderghast, Access MVP
Click to expand...

Thank you very much for your help!
I have solove the problem with your help. I use BAND
with the query, and its very fast. I am very glad to see
the result.
By the way, I am a new man in database. I designed the
database struct which store several logic value in one
field, now I have realized thats a bad mode.
Thanks very much again. And hope every thing goes well
to u. Thanks!
 
-----Original Message-----


Hi GG,

There is something wrong with my
connection to newsgroups since early
yesterday morning, so apologies not
getting back to you.

Where is the data table?
-within your Access db?
-in an Access backend?
-in a SQL Server db?
-somewhere else?

How will you use this query?
-for a read-only form?
-for an editable form?
-for a report?
-something else?

Hopefully, I'll be able to read your response
before tomorrow AM.

gary
Click to expand...
Thank you!
I have solved the problem. I develope the application
in VC++ and OLE DB, I just change my query as : SELECT *
FROM TableName WHERE FieldName BAND 8, and it worked very
well.
Thank you veru much for your help.
Best regard to you.
 
-----Original Message-----
Hi,

With Access 2000, you should be able to use BAND, if you use ADO. In the
immediate debug window, use:


? CurrentProject.Connection.Execute( "SELECT (1024+32+8+2) BAND 8
").Fields(0).Value
8

Since it is not a VBA function but a native one in Jet4.0 SQL, the
result should be faster.

In theory, we should store just one value per column. Having to use BAND
seems to indicate that this, which is sometimes referred to the
normalization rule 0, is not followed.



Hoping it may help,
Vanderghast, Access MVP
Click to expand...

By the way, what is MVP? Can I become a MVP? How to become
a MVP? To be a MVP must be very difficult.Are you tired?
Maybe, I can be the MVP instead of you, so you can have
much time on your vacation to my country, China.
Thanks! :)
 
Hi,

I will be glad to have time to go in China (in the south, mainly, it
seems the north is too much like here, in Canada). I was having the honor to
have two co-workers that were from China, a couple of years ago, and they
were excellent people. Unfortunately, I don't have time to make a trip ...
anywhere.

Microsoft MVP is an award given by Microsoft when they consider the
participations in the newsgroup. You become one, being selected and awarded
by Microsoft. For more info, see


Vanderghast, Access MVP
 
Thank you!
I am very glad to hear that Chinese ppl can do
something for you, and wish more Chinese can do much
better for you.
I am a controler engineer, and not very familer with
database, I wish I can learn more from you. Next time when
you see GG is in trouble on the board, remember to help
him, Thank you!
And next time, when you come to China, remember that GG
is a Chinese in the south_east, Maybe he can do something
for you too. :)
 
Back
Top