Create Query in VB?

[Jim]

How do I program visual basic to create a "Find Unmatched Query"?

I have two tables:

- TodaysImport
- YesterdaysImpor t

Both tables have the same 6 fields:

- User
- User Name
- Curriculum
- eMail
- OpGroup
- UserCurriculum

The UserCurriculum field is the key field that I want to find the
unmatched records on. I want the results from TodaysImport to show me
the records that were not in the table YesterdaysImpor t.

Any help would be appreciated!

[Larry Linson]

Re: Create Query in VB?

Is there some reason not to use the Find Unmatched Query Wizard to generate
the pattern and then use that as the basis for your VBA code?

Just an idle question: what is the definition of the "user curriculum"
field? If it is not a unique value for each record, you will have to
further clarify what makes the records different.

Larry Linson
Microsoft Access MVP

"Jim" <jlrehmann@gmai l.comwrote in message
news:1186583873 .702946.276100@ x40g2000prg.goo glegroups.com.. .

How do I program visual basic to create a "Find Unmatched Query"?
>
I have two tables:
>
- TodaysImport
- YesterdaysImpor t
>
Both tables have the same 6 fields:
>
- User
- User Name
- Curriculum
- eMail
- OpGroup
- UserCurriculum
>
The UserCurriculum field is the key field that I want to find the
unmatched records on. I want the results from TodaysImport to show me
the records that were not in the table YesterdaysImpor t.
>
Any help would be appreciated!
>

[John Winterbottom]

Re: Create Query in VB?

something like this:

dim sql as string

sql = "create procedure UnmatchedRecord s as " _
& "select * from TodaysImport as t " _
& "where not exists(" _
& "select * from YesterdaysImpor t as y " _
& "where y.UserCurriculu m = t.UserCurriculu m)"

currentproject. connection.exec ute sql , , adexecutenoreco rds



Jim wrote:

How do I program visual basic to create a "Find Unmatched Query"?
>
I have two tables:
>
- TodaysImport
- YesterdaysImpor t
>
Both tables have the same 6 fields:
>
- User
- User Name
- Curriculum
- eMail
- OpGroup
- UserCurriculum
>
The UserCurriculum field is the key field that I want to find the
unmatched records on. I want the results from TodaysImport to show me
the records that were not in the table YesterdaysImpor t.
>
Any help would be appreciated!
>

[Jim]

Re: Create Query in VB?

On Aug 8, 10:54 am, "Larry Linson" <boun...@localh ost.notwrote:

Is there some reason not to use the Find Unmatched Query Wizard to generate
the pattern and then use that as the basis for your VBA code?
>
Just an idle question: what is the definition of the "user curriculum"
field? If it is not a unique value for each record, you will have to
further clarify what makes the records different.
>
Larry Linson
Microsoft Access MVP
>
"Jim" <jlrehm...@gmai l.comwrote in message
>
news:1186583873 .702946.276100@ x40g2000prg.goo glegroups.com.. .
>
>
>

How do I program visual basic to create a "Find Unmatched Query"?

>

I have two tables:

>

- TodaysImport
- YesterdaysImpor t

>

Both tables have the same 6 fields:

>

- User
- User Name
- Curriculum
- eMail
- OpGroup
- UserCurriculum

>

The UserCurriculum field is the key field that I want to find the
unmatched records on. I want the results from TodaysImport to show me
the records that were not in the table YesterdaysImpor t.

>

Any help would be appreciated!- Hide quoted text -

>
- Show quoted text -

You're right Larry, I didn't think about it. Here's the SQL from the
query, so how do I get Access to create the query?

SELECT TodaysImport.Us er, TodaysImport.[User Name],
TodaysImport.Cu rriculum, TodaysImport.eM ail, TodaysImport.Op Group
FROM TodaysImport LEFT JOIN YesterdaysImpor t ON
TodaysImport.Us erCurriculum = YesterdaysImpor t.UserCurriculu m
WHERE (((YesterdaysIm port.UserCurric ulum) Is Null));

[Tom van Stiphout]

Re: Create Query in VB?

On Wed, 08 Aug 2007 11:57:05 -0400, John Winterbottom
<john.wb@rogers .cawrote:

Alternatively if you want to use the DAO methods:
'air code follows
dim q as DAO.Querydef
set q = CreateQuerydef( "qryName", "select ...")
currentdb.query defs.refresh

-Tom.

>something like this:
>
>dim sql as string
>
>sql = "create procedure UnmatchedRecord s as " _
& "select * from TodaysImport as t " _
& "where not exists(" _
& "select * from YesterdaysImpor t as y " _
& "where y.UserCurriculu m = t.UserCurriculu m)"
>
>currentproject .connection.exe cute sql , , adexecutenoreco rds
>
>
>
>Jim wrote:

>How do I program visual basic to create a "Find Unmatched Query"?
>>
>I have two tables:
>>
>- TodaysImport
>- YesterdaysImpor t
>>
>Both tables have the same 6 fields:
>>
>- User
>- User Name
>- Curriculum
>- eMail
>- OpGroup
>- UserCurriculum
>>
>The UserCurriculum field is the key field that I want to find the
>unmatched records on. I want the results from TodaysImport to show me
>the records that were not in the table YesterdaysImpor t.
>>
>Any help would be appreciated!
>>

[Larry Linson]

Re: Create Query in VB?

"Jim" <jlrehmann@gmai l.comwrote

You're right Larry, I didn't think about it. Here's the SQL from the
query, so how do I get Access to create the query?
>
SELECT TodaysImport.Us er, TodaysImport.[User Name],
TodaysImport.Cu rriculum, TodaysImport.eM ail, TodaysImport.Op Group
FROM TodaysImport LEFT JOIN YesterdaysImpor t ON
TodaysImport.Us erCurriculum = YesterdaysImpor t.UserCurriculu m
WHERE (((YesterdaysIm port.UserCurric ulum) Is Null));

In Access 2003 or earlier, in the Database Window, Queries tab, click the
New button and Find Unmatched is one of the options. Should do what you
want... I didn't attempt to recreate your table structure to try it, though.

In Access 2007... somebody else will have to take the question. I'm not
using it yet.

Larry Linson
Microsoft Access MVP

[Jens Schilling]

Re: Create Query in VB?

Hi,

In Access 2003 or earlier, in the Database Window, Queries tab, click
the New button and Find Unmatched is one of the options. Should do
what you want... I didn't attempt to recreate your table structure to
try it, though.
In Access 2007... somebody else will have to take the question. I'm
not using it yet.

;-)

In A07 the Query Wizard is located on the Ribbon "Create" (Group Other)

Regards
Jens

[Jim]

Re: Create Query in VB?

Thanks everyone, the following code worked!

'Create query
Dim createSQL As String
createSQL = "create procedure TodaysNewRecord s as SELECT
TodaysImport.Us er, TodaysImport.[User Name], TodaysImport.Cu rriculum ,
TodaysImport.eM ail, TodaysImport.Op Group FROM TodaysImport LEFT JOIN
YesterdaysImpor t ON TodaysImport.Us erCurriculum =
YesterdaysImpor t.UserCurriculu m WHERE
(((YesterdaysIm port.UserCurric ulum) Is Null));"
CurrentProject. Connection.Exec ute createSQL, , adExecuteNoReco rds