How to Validate Data coming in from SQL Database

[GBalcom]

I have a "bridge" I've created (3 separate Append queries, controlled by a macro) to take data from a Read Only SQL database, and move it to another SQL database that our ERP system uses.

There is a field in the first database that is an open text field. In order for the "bridge" to work correctly, it must have a valid Product Code (as determined by the second SQL Database, we have about 30 codes currently) . What I really need is to have in my macro some sort of data validation occur on this field, then let the user know that it is incorrect. My trouble is coming up with the right way to throw up a flag.....not sure if I need to incorporate Dcount or not, as there are multiple records that would need to be validated per append query (could be 1, or could be 100 records)....

 

[GBalcom]

The more I look at this, the more I wonder if I need a recordset? (of all the records that would come back with the affected append query)

 

[mdlueck]

What I really need is to have in my macro some sort of data validation occur on this field, then let the user know that it is incorrect.

YIKES!!!!

That sounds much more complicated than what I would consider macros capable of performing.

If you only have 30 codes, then you could SELECT DISTINCT codes from the one database, then on the other database do a SELECT NOT IN type query to pick up the remaining records that do not have one of the provided codes.

 

[GBalcom]

Michael,
I'm a newbie, so I'm sorry but.....I'm unclear on where to use the SELECT DISTINCT AND SELECT NOT IN......are they sub queries? or would this be in a VBA string?


the below gives me the inverse of what I'm looking for (the rows that match)....Now if I could figure out how to make it so they don't match...lol

 

[mdlueck]

I'm unclear on where to use the SELECT DISTINCT AND SELECT NOT IN......

That is SQL lingo. Here are some links that describe the lingo I used...

http://www.w3schools.com/sql/sql_distinct.asp

http://stackoverflow.com/questions/5748620/sql-server-not-in#5748669

You should be doing this work as Pass-Through queries so that Access does not have its query optimizer adding its own critique to your queries.

In my mind, again this all is well beyond the capabilities of Macros. Best driven with VBA scripting instead.

 

[GBalcom]

Michael,
Thanks for your help...I'll take a look at the links when I can. I just figured it out (enough to accomplish the task anyways).
What I did was set up an "unmatched" query, that was looking to the form I'm using for the parameters. I was able to use a Dcount function right in a macro to determine if any matched and make conditional actions based on that.

Thanks again,
Gary