Table comparison

[Pwyd]

If i wanted to programmatically compare the contents of a table to another
string, is there a way to do that all at once, or must you loop through each
row of the table? Whats the proper code, in either case?

 

[pietlinden]

If i wanted to programmatically compare the contents of a table to another
string, is there a way to do that all at once, or must you loop through each
row of the table?  Whats the proper code, in either case?

use a SQL statement. If the result returns records, you have
matches. If not, you don't.


You could also use Seek/FindFirst but I think seek requires that you
search on an indexed field or the primary key field.

 

[Pwyd]

So

if [Blah].MailLog = "string"
then DoCmd.openQuery "queryname"

is a valid statement, for comparing an entire table's contents with
fieldname.databasename and so on being the correct syntax?

 

[pietlinden]

So

if [Blah].MailLog = "string"  
  then DoCmd.openQuery "queryname"

is a valid statement, for comparing an entire table's contents with
fieldname.databasename and so on being the correct syntax?

Okay, take a step backwards. Why are you trying to do this?
If you want to get all the records where a fieldname in a table in
your database = "string" then why not just open a query based on the
table and then process it in code?

dim qdf as dao.querydef
dim rs as dao.recordset

set qdf=DBEngine(0)(0).Querydefs("QueryName")
set rs=qdf.OpenRecordset
do until rs.eof
'send your e-mail here
rs.movenext
loop

rs.close

 

[Pwyd]

All right, fair question. At some point, i will be leaving the workplace i'm
at. They do work for a government contractor, which if you're not familiar
with them, means they don't change for many, many years at a time. So along
this line of thinking, and based on the current set of databases i've built
for them, i'm going to make a single external table for use as a "name list"
so that they can add and remove names from just that one table, and set up
access to several databases. It will be simple and to the point, and not
require any real knowledge to operate. What i'm using now is a set of VB
comparisons to strings. They have to be added and removed manually. I'm
still in the process of re-learning my SQL and my VB as i hadn't used them
for about 8 years previous to my current job.

So

if [Blah].MailLog = "string"
then DoCmd.openQuery "queryname"

is a valid statement, for comparing an entire table's contents with
fieldname.databasename and so on being the correct syntax?

Okay, take a step backwards. Why are you trying to do this?
If you want to get all the records where a fieldname in a table in
your database = "string" then why not just open a query based on the
table and then process it in code?

dim qdf as dao.querydef
dim rs as dao.recordset

set qdf=DBEngine(0)(0).Querydefs("QueryName")
set rs=qdf.OpenRecordset
do until rs.eof
'send your e-mail here
rs.movenext
loop

rs.close

 

[Pwyd]

I was thinking along the lines, originally, of doing the following:

a text box on a hidden form, that gets the username that's logged into the
machine.
a linked table, that has a list of all of the valid users that may use the
database, and what forms they may open up.

On the same hidden form, a listbox that uses a row source that is a query of
the NameList table, with the criteria for the only field in the query using
the same GetUserID() that i use to get the username originally. My problem
was having it treat this as a string. you can't put In("GetUserID()" ) in
a criteria line.. which leads me to where i am now.

All right, fair question. At some point, i will be leaving the workplace i'm
at. They do work for a government contractor, which if you're not familiar
with them, means they don't change for many, many years at a time. So along
this line of thinking, and based on the current set of databases i've built
for them, i'm going to make a single external table for use as a "name list"
so that they can add and remove names from just that one table, and set up
access to several databases. It will be simple and to the point, and not
require any real knowledge to operate. What i'm using now is a set of VB
comparisons to strings. They have to be added and removed manually. I'm
still in the process of re-learning my SQL and my VB as i hadn't used them
for about 8 years previous to my current job.

So

if [Blah].MailLog = "string"
then DoCmd.openQuery "queryname"

is a valid statement, for comparing an entire table's contents with
fieldname.databasename and so on being the correct syntax?

Okay, take a step backwards. Why are you trying to do this?
If you want to get all the records where a fieldname in a table in
your database = "string" then why not just open a query based on the
table and then process it in code?

dim qdf as dao.querydef
dim rs as dao.recordset

set qdf=DBEngine(0)(0).Querydefs("QueryName")
set rs=qdf.OpenRecordset
do until rs.eof
'send your e-mail here
rs.movenext
loop

rs.close

 

[Pwyd]

I think i've got it now. Thanks.

I was thinking along the lines, originally, of doing the following:

a text box on a hidden form, that gets the username that's logged into the
machine.
a linked table, that has a list of all of the valid users that may use the
database, and what forms they may open up.

On the same hidden form, a listbox that uses a row source that is a query of
the NameList table, with the criteria for the only field in the query using
the same GetUserID() that i use to get the username originally. My problem
was having it treat this as a string. you can't put In("GetUserID()" ) in
a criteria line.. which leads me to where i am now.

All right, fair question. At some point, i will be leaving the workplace i'm
at. They do work for a government contractor, which if you're not familiar
with them, means they don't change for many, many years at a time. So along
this line of thinking, and based on the current set of databases i've built
for them, i'm going to make a single external table for use as a "name list"
so that they can add and remove names from just that one table, and set up
access to several databases. It will be simple and to the point, and not
require any real knowledge to operate. What i'm using now is a set of VB
comparisons to strings. They have to be added and removed manually. I'm
still in the process of re-learning my SQL and my VB as i hadn't used them
for about 8 years previous to my current job.

So

if [Blah].MailLog = "string"
then DoCmd.openQuery "queryname"

is a valid statement, for comparing an entire table's contents with
fieldname.databasename and so on being the correct syntax?

Okay, take a step backwards. Why are you trying to do this?
If you want to get all the records where a fieldname in a table in
your database = "string" then why not just open a query based on the
table and then process it in code?

dim qdf as dao.querydef
dim rs as dao.recordset

set qdf=DBEngine(0)(0).Querydefs("QueryName")
set rs=qdf.OpenRecordset
do until rs.eof
'send your e-mail here
rs.movenext
loop

rs.close