Execute Queries in Seperate Databases

[robbydogg]

Hi,

I've been looking around for a while, but can't find a solution to what i need.

Due to IT restritions I have 3 databases, on of which holds order data, one holds sales data and the other to hold the summary tables and reporting suite.

I'm looking for a bit of code, where i can click a buitton in the reporting database, which will run the queries in the other two, to update their data tables.

So i need db1 to run qry_update, then db2 to run qry_update .
I cannot link the tables and such as the tables those individuals link to slow down the database tremendously (due to network connections etc.

So basically i need code to call a query in a seperate database. I have tried the below, but it doesn;t work

Private Sub Command2_Click()
     
Dim db As Database
Set db = OpenDatabase("[URL="file://\\server\order"]\\server\order[/URL] data\SEDC.accdb")
db.Execute ("macro to run queries")
Set db = Nothing

End Sub

any ideas or solutions would be outstanding!!

thanks in advance

 

[jdraw]

Could you elaborate on the "IT restrictions"?

In typical operational/production systems, there are designed/controlled processes that update the authoritative tables based on business rules. Also, there are Monthend/Management Reporting Databases that hold Summary and reporting info. Typically these are read only - so that no "rogue processes" change the production data.

Perhaps there is more to the big picture than meets the eye.

 

[robbydogg]

Hi,

When i say IT restrictions i was trying to be nice about the slow performance of the servers here and the fact that i've been waiting for them to install SQL server so i can transfer the storage to there.
The databases were created and updtaed by myself so there's no conflict between using them.

thanks.

 

[jdraw]

Did you consider linking to the other tables from whatever database you choose?


Ooops just reread post 1....sorry about that.

This is how I did stuff in a second database in Acc2003

'---------------------------------------------------------------------------------------
' Procedure : testdb
' Author    : Jack
' Created   : 12/8/2009
' Purpose   : Test opening second A2003 database.
'---------------------------------------------------------------------------------------
' Last Modified:
'
' Inputs: N/A
' Dependency: N/A
'------------------------------------------------------------------------------
'
Sub testdb()

Dim dbsCurrent As DAO.Database
Dim dbsSecond As DAO.Database
Dim tbl As DAO.TableDef
   On Error GoTo testdb_Error

Set dbsCurrent = CurrentDb
Set dbsSecond = DBEngine.Workspaces(0).OpenDatabase("c:\users\jack\a2k\db1New.mdb")
Debug.Print dbsCurrent.name
Debug.Print dbsSecond.name

For Each tbl In dbsSecond.TableDefs
Debug.Print "  " & tbl.name
Next
Debug.Print Now()

   On Error GoTo 0
   Exit Sub

testdb_Error:

    MsgBox "Error " & Err.number & " (" & Err.Description & ") in procedure testdb of Module Module5"

End Sub

 

[vbaInet]

Can you not create stored procedures on the databases concerned and execute the appropriate ones?

 

[robbydogg]

thanks for all your help, the code i finallty used was below.

Private Sub Command2_Click()
Dim db As DAO.Database
Set db = DBEngine.Workspaces(0).OpenDatabase("[URL="file://\\server\folder\folder\SEDC.accdb"]\\server\folder\folder\SEDC.accdb[/URL]")
db.Execute "Query1", dbFailOnError
Set db = Nothing
End Sub

thanks again tho, as per usual this forum has provided the best information and help.
​