Running Queries in other Databases (1 Viewer)

[MatthewH]

Hi everyone,
I have a form and want one of the combo box's to run a VBA code that does the following:
Opens up 2 databases and appends an excel file to each and then runs a query that is in each of those databases.

E.G.
On click:

Dim appAccess As New Access.Application
Dim FilePath As String
Dim BackEndDB As String
Set appAccess = Access.Application
FilePath = "Jatabases"
BackEndDB = "DB1.accdb"

appAccess.OpenCurrentDatabase FilePath & BackEndDB
'Insert code here to append excel file "DB1Excel.xlsx" from Jatabases\
DoCmd.OpenQuery "DeleteDupesDB1"


I know there's something wrong because it's saying that the database is already open. I have a table from all of these databases linked to my current DB I'm working in (my front end).

Please let me know if there's a way to open and run the back end database and append the excel sheet. I'm really struggling here :/

Thanks!

 

[StevePupel]

You can't really append and excel file, do you mean you're importing the excel file and creating a table?

It's easy to create a Make Table query and then in the window where you specify the table name you can specify that you want to make it into another database. Then you can view the SQL so that you can construct the SQL yourself in VBA and run the SQL.

As for running a query in another database... What are these other queries doing? You can do whatever they're doing from within the database your already in. Just link to the tables in those remote databases and create whatever query you need based on the linked tables.

 

[The_Doc_Man]

You have a syntax error while we are at it.

FilePath is "J : Databases" without the spaces and BackEndDB is "DB1.accdb" - so let's concatenate them - and we get...

J : DatabasesDB1.accdb

You need a trailing slash on your path. I have no clue as to why you get the error you get but I can tell you that you won't open what you think you were going to open.

However, Steve is correct about how to determine the syntax of an external file query. On the other hand, he's ALSO correct about how you could just link to more than one back end and then do queries on linked tables.

 

[static]

Private Sub Command0_Click()
    
    Const DBPath As String = "J:\Databases\DB1.accdb"
    Const DBXLLink As String = "MyExcelLink"
    Const DBQuery As String = "MyQuery"
    
    Const XLFile As String = "J:\Databases\DB1Excel.xlsx"
    Const XLRange As String = "Sheet1$"
   
    Dim db As dao.Database, tdf As dao.TableDef
    Set db = DBEngine(0).OpenDatabase(DBPath)
    
    With db
        'delete tables if they already exist
        If TblExists(db, DBXLLink) Then .TableDefs.Delete DBXLLink
        
        'create link to xl
        Set tdf = db.CreateTableDef(DBXLLink)
        With tdf
            .Connect = "Excel 12.0;HDR=YES;IMEX=2;ACCDB=YES;DATABASE=" & XLFile
            .SourceTableName = XLRange
        End With
        .TableDefs.Append tdf
        
        'run query
        .Execute DBQuery
    End With
End Sub

Function TblExists(db As dao.Database, t As String) As Boolean
    TblExists = Not db.OpenRecordset("select 1 from msysobjects where name='" & t & "' and type=6").EOF
End Function

 

[MatthewH]

Hey everyone thanks for your responses and attempts. I think I'm over-complicating this and I want to be sure I am before I go about this other method.

I want to import an excel sheet into a table and append it to a linked database. Very simple. I have the path of the excel file and the path of the back-end database. I want it to run from my front-end where the DB is linked.

Please let me know if I can simply just create a VBA function to import (DoCmd.TransferSpreadsheet) and then run a query in my front-end database that would append the data to the linked table and then another query to delete my imported table since I won't need it anymore. I also wanted to run a query to delete any duplicates on the back-end databases, but I don't know if I can also do this from the front-end (I have the queries on the back-end, didn't know if I could bring them to the front-end.)

Any help is appreciated, thanks!

 

[isladogs]

Please let me know if I can simply just create a VBA function to import (DoCmd.TransferSpreadsheet) and then run a query in my front-end database that would append the data to the linked table and then another query to delete my imported table since I won't need it anymore.

Yes to all of the above - assuming you are SURE you won't need the import file again

I also wanted to run a query to delete any duplicates on the back-end databases, but I don't know if I can also do this from the front-end (I have the queries on the back-end, didn't know if I could bring them to the front-end.)

You can edit linked databases but NOT linked Excel files

 

[MatthewH]

Yes to all of the above - assuming you are SURE you won't need the import file again



You can edit linked databases but NOT linked Excel files

Yes I'm 100% sure I won't need it. Once it's appended to the linked table I use the linked table for everything.
That should be a very simple task for me to do (this entire thread), I'll come back here in about an hour or two if I run into any problems haha.

Thank you all for everything!!