Select Query With A Twist!!! 3

[quest4]

I need to make a query to see if a EmployeeNumber exists, but in two different dbases, a production dbase and a archive dbase. Can this be done? How? Thannks in advance for anyone answering this one.

 

[Maquis]

Use a union query on the 2 tables. (See Access help for more details on union queries).

Of course you will need to link in the table from the archive database. (Sorry, quest, you're not going to get away from the linked tables issue.) Maq B-)
<insert witty signature here>

 

[quest4]

Maq thanks for the respnse. You mean I finally figured out what was wrong with that query and I get the correct data trandfer across the network, even. Linking won't work, it will put the wrong data into the archive dbase table and screw up what I have accomplished with it. Maybe I will have to use an old progrqaming trick. lie to it. I will play with the union query and wsee if I can get anything. Thanks again for the response.

 

[BoxHead]

Why not two DLookUps?

John

Use what you have,
Learn what you can,
Create what you need.

 

[quest4]

John, thanks for the response. How do you do that and where do you do that? I am trying to do this oin a form, under the command buttom for Add. Basically all I am tring to do is make sure that I don't duplicate and EmployeeNumbers. Thanks again for the response, sound like it may in someway have potiential.

 

[BoxHead]

Dim MyCheck1 as String, MyCheck2 as String

MyCheck1 = DLookUp(&quot;[EmployeeID]&quot;, &quot;tblProduction&quot;, &quot;[NumberToBeAdded] = [EmployeeID]&quot

MyCheck2 = DLookUp(&quot;[EmployeeID]&quot;, &quot;tblArchive&quot;, &quot;[NumberToBeAdded] = [EmployeeID]&quot

If Not IsNull(MyCheck1) Or Not IsNull(MyCheck2) Then
  MsgBox "This Employee ID Number is already assigned." & vbnewline & vbnewline & [NumberToBeAdded]

Exit Sub

End If

'otherwise go on with your code for adding the number.


HTH John

Use what you have,
Learn what you can,
Create what you need.

 

[quest4]

Thanks alot, John. One last question, sense I never used this DLookup before, would I put this in the After Update event procedure? This will work, even thoe tblArchive is in another dbase? Thanks again ever so much for the help.

 

[Maquis]

Quest, as far as where to put the code is concerned, I believe you answered that question yourself in an earlier post. You said you wanted the code in the click event of your command button.

Now about the table being in the other database part....

I already know you don't want to hear this, but you will need to link the archive table into your database. <<insert horror scream here>> Please, please, please learn about linking tables. You'll save yourself a lot of headaches in the future.

You said in an earlier thread that linking the tables would corrupt them. I don't understand how that is possible? Linking the tables doesn't actually move the tables to another database, it just adds a reference to them so that they can be easily updated/queried. Maq B-)
<insert witty signature here>

 

[quest4]

Maq, thanks for the respnse. I do understnad linking, it has been done for years in UNIX. I have link literally hundred of files and directories there. It looks like the same thing here, when I am in the archive dbase, I use file -> import -> link tables, I get an updatable vision of the table I am linked to, which is what I really expected. If I delete something in the archive dbase table, it is also delleted in the production dbase table and vice-a- versa, again this is exactlt what I expected. This is what the link that I have played with for years is supposed to do. This is not what I wanted, what I did want is what I got now, current data in the production dbase table and old data in the archive dbase table. And that i why I have avoided use the link, which I have tried several times to use. I realize the in the world of Willie Wondka and the Chocolate Factory the concept of linking is fairly new, but in reality, it is a very old concept, used for a very long time in Lynix and UNIX. Without knowing a lot, but learning quickly, about VB all I need to know is how to set two different dbases in one procedure. Does it have something to do with DIM amd SET ... AS, or what. Thanks again for the response.

 

[jebry]

Hi Quest4!

We do understand what you are trying to do. But I think you have been missing something about what we have been trying to say, so I'll try again. The easiest way to archive information in a separate database is to create an archive table(s) in the archive database, which you have done. Then link from the archive database to the production table in the live database, which you have not done. Alternatively, you can link from the live database to the table in the archive database. If you do this, you will be able to write simple update queries to get information from your production table and store that information in your archive table because Access will treat both tables as if they are in the same database.

BTW, what is UNIX?

hth Jeff Bridgham
bridgham@purdue.edu

 

[quest4]

Jeff, thanks for the response. UNIX is simple to explain, if you want to do one thing a million different ways UNIX is your answer, if you want to doa million different things one way MS is the answer. But lets not get into that debate or this may never end. I made another linked table and it is like the rest, it displays a the data from the productinon table, as I expected. The only way I could see if this method could work is to create a second table for the archive data. Then in theory you run the queries in this dbase. Is that what you are suggesting? I hope not because that opens up a real can of worms. Please let me know one way or another. Thanks alot for the help.

 

[Maquis]

Hi Quest,

Here's what I'm suggesting and I believe Jeff's reasoning is somewhat along the same line of thought. Create a link table in your production database that points to the archive database. You would only need the 1 linked table. You could then very easily run your updates in the production database to update the archives. Maq B-)
<insert witty signature here>

 

[jebry]

Hi again!

Maq has what I am suggesting down absolutely! And it really is the easiest way to do what you are needing all from one database. And you can make it run behind the scenes so the users are not even aware of the archiving that is going one.

BTW, I love UNIX and have used it for years, but MS has many advantages as well.

hth Jeff Bridgham
bridgham@purdue.edu

 

[Maquis]

The headaches really start when you try to make Access talk to UNIX. X-) Maq B-)
<insert witty signature here>

 

[quest4]

Maq, thanks for the response. That is right along the lines I was think last night, on the back patio. With that done, can't I also run all of the clean-up queries and the form in the production dbase and vitually leave the archive dbase as only a table? I was not tring to make Access to to UNIX, rather using the same logic on Access as I would in UNIX, the talking to UNIX is done with Somba. After all isn't logic is always logic only the termnology changes. Thanks again for all of the help.

 

[quest4]

Got it straightened out thanks for the help.