Data from another database

[damiendad]

Is it possible to get data from a query in another database without linking them?

 

[SforSoftware]

Hi,

Maybe this?

Function CallQuery()
  Dim wrk As DAO.Workspace
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  
  Set wrk = CreateWorkspace("", "admin", "")
  Set dbs = wrk.OpenDatabase("C:\MyDatabase.mdb")
  Set rst = dbs.OpenRecordset("QueryInMyDatabase")
  
  Do Until rst.EOF
    Debug.Print rst.Fields(0)
    rst.MoveNext
  Loop
End Function

Change the names to your situation. You'll get the query in a recordset.

 

[damiendad]

I'm not very good with code, but I copied and pasted it, but it wouldn't work because I'm on a network, and it was asking me questions about my user profile. Anyway this can be done in a macro?

 

[SforSoftware]

Hi,

In a macro, you can do it with TransferDatabase. That action creates in your database a table with the query-data, which table you can delete after using it. You can also let the table stay, because next time you run TransferDatabase, this table will be overwritten.
See Help for more info about transferDatabase.

For the code, look at VBA-Help voor "OpenDatabase" and "CreateWorkspace" for more information and examples.

 

[damiendad]

I tried it, and it's importing as a query, not a table, so when I try and open it, it is looking for the table that the data is coming from.

 

[SforSoftware]

I'm sorry, as ObjectType you must select 'Table', but Helps tells me it work with Exporting a query, so I'm not sure it will work with Importing. You can have a look at "TransferDatabase" in Help

 

[damiendad]

Well that worked. I appreciate all your help. Now I have another question. I use an append query to export all my data to a backup database, then in the backup database, I have a query with a count function to let me know how many records in the backup. I am transferring the data in the count query to my working database to a new table. Now I'm having a problem getting the data from the table onto my form to show the number of records. The form isn't bound to anything in the database, I can't seem to come up with a formula to get the data. Can you help with that?

 

[SforSoftware]

Glad it works!

I'd think the table you imported has always the same name, so you can bound your form to it or use:
=DLookUp("Field"; "TableName"; "Criteria")

 

[damiendad]

Thanks. You have been a huge help.

 

[SforSoftware]

Glad to help you