Two Databases Talking? 1

[euang]

Hi, I have a wee bit of a problem in so far as I have promised a client something that I am finding to be rather difficult, nigh on impossible.

At the moment there are two databases on the server for the website. One called userdetails.mdb, and one called Admaster.mdb. The userdetails database contains, well, userdetails, such as name age id etc, and the Admaster database contains details of jobs. The jobs database is updated twice daily from the company premises and the userdetails database stays on the server, only being copied occasionally to gather usernames etc.

What I need to do is query the Admaster database using the details in the userdetails database. This must be done on the server automatically.

For example, if the user has put in as one of their search details, that they are interested in any job over 50,000 (who wouldn't be?) then the admaster database should be searched for all jobs with the 'salary from' field over 50,000. The results of the search should then be automatically emailed to the users email address (held in the userdetails database). There is tons to do here. but basically I need to know if it is possible for two seperate databases to query each other automatically(once a day). The email thing is another problem I am going to have to deal with. It will be really cool if I can get this to work, it seems a long way away at the moment though.

Any help, info etc would be greatly appreciated

Thanks in advance

Euan

 

[Amiel]

Simple, It will look something like this ...

Public Sub OpenAndQuery2DataBases()

dim db1 as Database
dim db2 as Database
dim rs1 as recordset
dim rs2 as recordset

' this is the current database "Admaster.mdb"
set db1 = DBEngine(0)(0)

set db2 = DBEngine.Workspaces(0).Opendatabase("C:\userdetails.mdb&quot

set rs1 = db1.OpenRecordset("SELECT * FROM [AddTable] WHERE [Salary]>"& Me![txtUserEnteredNumericValue]
' ... Gets everything paying more than 50k

set rs2 = db2.OpenRecordSet("SELECT * FROM [Contractors] Where [LocationState] =""" & rs1("State&quot & """ And [RequiredSalary] >" rs1("Salary&quot
End Sub


Now have two recordset from the two databases, rs1 contains jobs with the salary in the range, rs2 contains contractor(s) located in the state who's salary requirement are met (or greater) in rs1 . You can take it from here ......

Enjoy !