What's the best solution???

[jane30]

Hi, With Access97, I'm doing this: my job slices one master table into 13 pieces by department No and dumps them in 13 different databases, then FTP to individual dept. It does what I want it to do so far.
But I have a concern: to ensure the data accuracy in the department database, I want to set up a Audit Trail, i.e. compare the records count and total $ amount between the master table with those in 13 department databases. Currently I'm using Union query to handle this issue. However, it turns out to be a pain if the location of those 13 department databases changes. It's sort of tedious to change one by one in the union query. I want to use variables in VBA code to feed in the location and database name, etc. But Union query doesnt' work in VBA code. Any suggestions??? Thans a bunch in advance.

 

[rushdib]

I had to go through a similar problem once and I copied the SQL part of the query (from SQL View) and made it as a string in VBA. Then you can manipulate the string using variables. Are you familiar with SQL strings in VBA. If not let me know I will give you an example.

 

[jane30]

yes. I'm familiar with VBA code. I tried to write before. But it seemed that Union query doesn't work in docmd.runsql this command. It'll be great if you can show me one example. I reall appreciate. thanks alot. or %-)

 

[rushdib]

You can use a function like the following in a loop changing the deptno and path of the database or whatever other variables you want to use as parameters.

function test(deptno as string, path as string) as boolean

dim dbs as database
dim rst as recordset

set dbs = opendatabas(path) 'ex. c:\db1.mdb
set rst = dbs.openrecordset("select * from table1" _
& "where table1.deptno" _
& "= '" & deptno & "')
if rst.eof then
error
else
'do other task
end if

end function

This way you don't need a union query. I don't if this going to help you, otherwise let me know.