Open another database - revisited 1

[AArt]

I have a master DB that holds the tables to feed a number of other DBs i'm building. The Master DB needs to be updated (it uses ODBC queries to an AS400)occasionally.
I tried using the OpenDatabase method code, copied right from the help files, but I must be doing something wrong.How do you run queries in your other DB object that you've created?
I know I could add the same queries and ODBC links to my satellite DB's, and update the linked tables that way, but that seems most inelegant. How would you pros handle this? Your advice is greatly appreciated.

 

[MichaelRed]

Not sure I understand. Are the queries in the "foregin" db?

If so, it depends on how you are using them.

In general, however, you just need to set them to the "foregin" db, as in:

set qryFor = OtherDb.OpenRecordset("qryForName", dbopendynaset)

Of course, the syntax does vary if you use a different "object Model" (ADO?)


MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[AArt]

Please help with the syntax specifically... I'm kinda new at this data manipulation (I can make a nice form though!)
The large data tables, and the ODBC queries that update them, are in what I'll call DBMaster. I don't want to ODBC to the mainframe everytime I get data, 'cause it's too slow. But I'd like them to initiae an update when they go for coffee. So the DBs that access the data I'll call dbSatellite
here's the code from the MSAccess 97 Help:

Dim wsp As Workspace, dbs1 As Database, dbs2 As Database

Set dbs1 = CurrentDb 'dbsatellite
Set wsp = DBEngine.Workspaces(0)
Set dbs2 = wsp.OpenDatabase("dbsMaster.mdb&quot
'This is where I want to run the queries that
'update the tables in dbsMaster
dbs2.Close 'is this how I close the object?

 

[MichaelRed]

Dim wsp As Workspace
Dim dbs1 As Database
Dim dbs2 As Database
Dim qryUpDate as QueryDef

Set dbs1 = CurrentDb 'dbsatellite
Set wsp = DBEngine.Workspaces(0)
Set dbs2 = wsp.OpenDatabase("dbsMaster.mdb&quot

'This is where I want to run the queries that
'update the tables in dbsMaster
Set qryUpdate = dbs2.QueryDef("NameOfQuey")
qryUpdate.Execute

'This is "O.K."
dbs2.Close 'is this how I close the object?

MichaelRed
redmsp@erols.com

There is never time to do it right but there is always time to do it over

 

[AArt]

Thanks! unfortunately, the ODBC queries to the mainframe do not work, so I'll have to do them another way. They lock up Access (Not responding). But the other queries do work, and this opens up a whole mess o' fun for me!