Queries in a Split Database

[Cheesewoman]

Hi all,

I have an Access 2010 database that I am developing at work. I recently split the database (using the wizard) into the front end database with the interface and all the queries/reports etc., and the back end with just the tables.

That's all fine, and my front end database shows the tables with little arrows in the navigation pane which indicates that the split worked and the tables are all linked.

I have successfully gotten as far as opening a new instance of access and the "back end" database (using pseudo names in code) and then running a couple of simple queries from the code:

    Dim acc As Access.Application
    Dim db As DAO.Database
    Dim strDbName As String

    strDbName = "filepath/backEndDatabaseName.accdb"
    
    Set acc = New Access.Application
    Set db = acc.DBEngine.OpenDatabase(strDbName, False, False)
    acc.OpenCurrentDatabase strDbName

    acc.DoCmd.DeleteObject acTable, "TempTable"
    acc.DoCmd.TransferSpreadsheet acImport, , "TempTable", "filepath/filename.xls"

That all works fine. However the issue I'm having is after these lines of code, where I have quite a few OpenQuery commands which should run the queries within the database, such as;

    DoCmd.OpenQuery "Query_ConvertImportDates", acViewNormal

I have tried getting the query to run from within the back end database;

    acc.DoCmd.OpenQuery "Query_ConvertImportDates", acViewNormal

but of course it can't find the query because it's actually in the front end database.

I'm guessing that I need to reference the backend database in the query itself so it knows where to look, but I'm not entirely sure how to go about it, or if it's the right way to do it (or if it's even necessary, I thought that having a split database would automatically link the tables and queries so it would know where to look??)

I hope I've provided enough information to get the idea...

Please help!!

 

[CJ_London]

I'm guessing that I need to reference the backend database in the query itself so it knows where to look, but I'm not entirely sure how to go about it, or if it's the right way to do it (or if it's even necessary, I thought that having a split database would automatically link the tables and queries so it would know where to look??)

Once split, your queries are in the front end and should be referencing the linked tables in the front end. There is no need to address the backend directly.

If you move or rename the backend, you will need to relink the front end

A simple test is to simply open the linked table - you should see the data held in the backend. If you don't, you need to relink.

However I think your problem is docmd.openquery - these are used for select or crosstab queries, not action queries such as Append, Update and Delete. For these you should use currentdb.execute or docmd.runsql

 

[Cheesewoman]

However I think your problem is docmd.openquery - these are used for select or crosstab queries, not action queries such as Append, Update and Delete. For these you should use currentdb.execute or docmd.runsql

Thanks for the tip - I think I see what you're saying, however I'm not sure that that's what is causing it to fail.

I have tried changing it to;

    CurrentDb.Execute "Query_ConvertImportDates", acViewNormal

but still have the same issue. The error is 3078 - "The Microsoft Access database engine cannot find the input table or query 'TempTable'. Make sure it exists and that its name is spelled correctly"

I know that the table exists and is spelt correctly in the back end database, however it gets dropped and re-created every time the database is opened so I don't think it can be linked to the front end database like the others.

Therefore I think that the problem is that it can't find TempTable in the query itself (because in the query I don't reference the back end database).

Given that the above information is correct, I assume that instead of having my query look like this;

UPDATE TempTable SET TempTable.[Order Date] = REPLACE([TempTable].[Order Date], '.', '/');

It should look like this;

UPDATE BackEndDB.TempTable SET BackEndDB.TempTable.[Order Date] = REPLACE([BackEndDB.TempTable].[Order Date], '.', '/');

If I am correct and this is the case, could please point me in the direction of how to "correctly" reference the other database in the queries themselves?

Thanks for the help

 

[CJ_London]

really you should not store temp tables in the backend (or front end) as it will cause bloat but in a separate temporary database.

but you have identified the problem and you probably don't have a linked table to your temp table, so ensure the backend has the table and create a linked table in the front end (use the external data tab>access) to navigate to it.

If the temp table is deleted, the linked table will remain in place but will fail if the temp table does not exist - this won't matter providing it exists when the query is running.

Alternatively have the temp table in the front end

 

[Cheesewoman]

but you have identified the problem and you probably don't have a linked table to your temp table, so ensure the backend has the table and create a linked table in the front end (use the external data tab>access) to navigate to it.

If the temp table is deleted, the linked table will remain in place but will fail if the temp table does not exist - this won't matter providing it exists when the query is running.

Alternatively have the temp table in the front end

I get what you're saying about not using temp tables this way, but for the purposes of what I'm "trying" to do it works, so I'll leave it as is

I wasn't really wanting to put any tables into the front end database, so I followed your suggestion of creating a link to the TempTable and it works perfectly now!! (Don't know why I didn't think of doing that before?)

Can't thank you enough for your help!