Hi all

Need some help so hoping there's an easy answer.

Background: I have a database from which I'm creating a temp database that's intended to be used to hold some temp tables while the data is used to build a master table that's re-imported back into the database. All this to avoid bloating of the actual database. I've managed to create the db and put in the base table and the first temp table. What I need to do now is have a query run that uses the temp table in the temp db update the base table in the temp db but execute from the "real" database.

The update query, if I built it in the temp db would be:

UPDATE tmp_tblDeckTaxes

INNER JOIN tmp_tbldecktaxes_1
ON (tmp_tblDeckTaxes.Ent_ID = tmp_tbldecktaxes_1.Ent_ID)
AND (tmp_tblDeckTaxes.Loc_ID = tmp_tbldecktaxes_1.Loc_ID)
AND (tmp_tblDeckTaxes.Country = tmp_tbldecktaxes_1.Country)
AND (tmp_tblDeckTaxes.Rpt_ID = tmp_tbldecktaxes_1.Rpt_ID)
AND (tmp_tblDeckTaxes.Rule = tmp_tbldecktaxes_1.Rule)

SET tmp_tblDeckTaxes.Tax1_Juris = [tmp_tbldecktaxes_1].[Tax1_Juris],
tmp_tblDeckTaxes.Tax1_Name = [tmp_tbldecktaxes_1].[Tax1_Name],
tmp_tblDeckTaxes.Tax1_Reg = [tmp_tbldecktaxes_1].[Tax1_Reg],
tmp_tblDeckTaxes.Tax1_Tx = [tmp_tbldecktaxes_1].[Tax1_Tx],
tmp_tblDeckTaxes.Tax1_Rate = [tmp_tbldecktaxes_1].[Tax1_Rate];

What I'm trying to do is something like the following but I don't know the syntax to get this to fly:

UPDATE [C:\Users\RC02041\Desktop\ASWorkDisc1\T-Rx-v\TRxTemp.accdb].tmp_tblDeckTaxes_1
IN 'C:\Users\RC02041\Desktop\ASWorkDisc1\T-Rx-v\TRxTemp.accdb'

INNER JOIN [C:\Users\RC02041\Desktop\ASWorkDisc1\T-Rx-v\TRxTemp.accdb].tmp_tblDeckTaxes
ON (tmp_tblDeckTaxes1.Ent_ID = tblDeckTaxes.Ent_ID)
AND (tmp_tblDeckTaxes1.Loc_ID = tblDeckTaxes.Loc_ID)
AND (tmp_tblDeckTaxes1.Country = tblDeckTaxes.Country)
AND (tmp_tblDeckTaxes1.Rpt_ID = tblDeckTaxes.Rpt_ID)
AND (tmp_tblDeckTaxes1.Rule = tblDeckTaxes.Rule)

SET tblDeckTaxes.Tax1_Juris = [tmp_tblDeckTaxes1].[Tax1_Juris],
tblDeckTaxes.Tax1_Name = [tmp_tblDeckTaxes1].[Tax1_Name],
tblDeckTaxes.Tax1_Reg = [tmp_tblDeckTaxes1].[Tax1_Reg],
tblDeckTaxes.Tax1_Tx = [tmp_tblDeckTaxes1].[Tax1_Tx],
tblDeckTaxes.Tax1_rate = [tmp_tblDeckTaxes1].[Tax1_rate]

(This is in a loop so there could ultimatley be a lot of these temp tables and corresponding queries - hence the desire to do all this outside the main db).

Any ideas?!?

Thanks!

Adrian

(IMG:style_emoticons/default/welcome2UA.gif)

I would imagine that specifying and creating a link to an external database each time you perform an action query would have a significant impact on performance. Why not link the tables from your temp database to your front end - renaming the lnked tables in the FE with an identifying tag (e.g. 'tblSomeTableName' in the temporary tables is named as table 'tblSomeTableName_temp'), you can then perform all the queries you need without having to specify the target database.

hth

Hi Adrian,

(IMG:style_emoticons/default/welcome2UA.gif)

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

I agree with Bernie but you were not too far off. Try something like:

UPDATE [;Database=C:\Users\RC02041\Desktop\ASWorkDisc1\T-Rx-v\TRxTemp.accdb].tmp_tblDeckTaxes_1
INNER JOIN [;Database=C:\Users\RC02041\Desktop\ASWorkDisc1\T-Rx-v\TRxTemp.accdb].tmp_tblDeckTaxes
ON [;Database=...

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Woo hoo! It works! (IMG:style_emoticons/default/woohoo.gif)

Thanks for your help. (I'm on Access 2010 BTW - will remember for future pleas for assistance).

Wasn't sure how to create links to temporary tables in the midst of my loop so I fought through the query with the syntax snippet. I ended up with a lot of bracketing issues so I plonked it into variables and then used some aliases to keep myself sane. The end result is below in case anyone's interested who's found this in search of similar.

Dim Temp As String
Dim TempNew As String

Temp = "[;Database=C:\Users\RC02041\Desktop\ASWorkDisc1\T-Rx-v\TRxTemp.accdb].tblDeckTaxes "
TempNew = "[;Database=C:\Users\RC02041\Desktop\ASWorkDisc1\T-Rx-v\TRxTemp.accdb].tmp_tblDeckTaxes_" & Ctr2

strSQLUpdate = "UPDATE " & Temp & " AS A" & _
" INNER JOIN " & TempNew & " AS B" & _
" ON (A.Ent_ID = B.Ent_ID) " & _
"AND (A.Loc_ID = B.Loc_ID) " & _
"AND (A.Country = B.Country) " & _
"AND (A.Rpt_ID = B.Rpt_ID) " & _
"AND (A.Rule = B.Rule) " & _
"SET A.Tax" & Ctr2 & "_Juris = B.[Tax" & Ctr2 & "_Juris], " & _
"A.Tax" & Ctr2 & "_Name = B.[Tax" & Ctr2 & "_Name], " & _
"A.Tax" & Ctr2 & "_Reg = B.[Tax" & Ctr2 & "_Reg], " & _
"A.Tax" & Ctr2 & "_Tx = B.[Tax" & Ctr2 & "_Tx], " & _
"A.Tax" & Ctr2 & "_Rate = B.[Tax" & Ctr2 & "_Rate];"
("Ctr2" is another variable. This is an awful loop dealing with an awful table that doesn't have a fixed number of columns and therefore there isn't a fixed number of these temporary tables).

Again - really appreciate the help.

Adrian

Hi Adrian,

(IMG:style_emoticons/default/yw.gif)

Bernie and I are happy to help. Good luck with your project.