UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Query across multiple DBs -- with Update    
 
   
BillyBob
post Dec 17 2008, 01:28 PM
Post#1



Posts: 52
Joined: 13-June 08



What I'm dealing with is a 97 mdb and 2007 accdb. The reaon we're doing this for now is it's a transition from 97 to 2007 so we have the front end in 2007 and the backend in 97. What a headache so far.
Here's the query: (strTbl is from accdb, the rest are from mdb).
strCommand = "UPDATE " + strTbl +
" INNER JOIN ([Employee Departments] INNER JOIN Departments " +
" ON [Employee Departments].DepartmentID = Departments.DepartmentID) ON " + strTbl +
".EmployeeID = [Employee Departments].EmployeeID SET " + strTbl +
".DepartmentID = [Employee Departments].[DepartmentID]" +
"WHERE ((([Employee Departments].DefaultEmployeeDepartment)=True));";
This is an access query that is being run from Visual Studio .Net.
I was told before I could reference the accdb or mdb directly and that worked ok in a select query but what about an update query where I can't alias the way I can in a select query.
Can I query over multiple dbs with an update query?
Thanks for any help.
Go to the top of the page
 
Doug Steele
post Dec 17 2008, 08:42 PM
Post#2


UtterAccess VIP
Posts: 21,490
Joined: 8-January 07
From: St. Catharines, ON (Canada)


I'm not sure it's possible to have an update query that uses two inner joins. But since one of your tables has spaces in the name, is it possible that whatever's in strTbl might have spaces as well?
If so, try
CODE
strCommand = "UPDATE [" & strTbl & "] " & _
  " INNER JOIN ([Employee Departments] INNER JOIN Departments " & _
  " ON [Employee Departments].DepartmentID = Departments.DepartmentID) ON [" & strTbl &  _
  "].EmployeeID = [Employee Departments].EmployeeID SET [" & strTbl & _
  "].DepartmentID = [Employee Departments].[DepartmentID]" &
  "WHERE [Employee Departments].DefaultEmployeeDepartment=True"

I have to say, though, the fact that you'd require multiple identical queries like that makes me suspect that your database design is incorrect.
Go to the top of the page
 
BillyBob
post Dec 17 2008, 08:48 PM
Post#3



Posts: 52
Joined: 13-June 08



Not my design... Since I've been in the programming world (nearly 10 years) I have almost never had the chance to implement my own db design. I've always inheirited the "fun" from those before me.
ey djsteele, remember you showed me the [Database =: C:\temp\test.mdb] or something similar?
Well, I was looking how to implement that with the update query above. What do you think about that one?
Go to the top of the page
 
mishej
post Dec 17 2008, 09:02 PM
Post#4


Retired Moderator
Posts: 11,289
Joined: 25-September 02
From: Milwaukee, WI


I don't think Doug would mind me stepping in here...
et (the database engine in Access) SQL can reference other files such as this SELECT:
CODE
SELECT *
FROM tblEmployee IN 'c:\temp\SimpleTime.mdb';

The "IN" is even more versatile, e.g. you could reference a worksheet in an Excel file. Here I reference two other files from a third file:
CODE
SELECT *
FROM tblDescriptionsToChange IN 'C:\src\access\JobDescriptionClean.mdb'
WHERE NDC IN (SELECT NDC FROM tblDescriptionClean IN 'C:\src\access\JobCleanData.mdb');

THere is another example:
CODE
SELECT c.*, d.*
FROM (;database=c:\src\football\k_data.mdb) Team AS c INNER JOIN (;database=c:\src\football\k_data_may_2004.mdb) Team AS d ON c.Team = d.Team;

And here is an INSERT into a local table from another file:
CODE
INSERT INTO tblJourney
SELECT *
FROM tblJourney IN 'c:\src\access\journey.mdb';

I'm sure you can do an UPDATE as well depending on the exact requirements. I just couldn't quickly find an example in my library. Hopefully the code samples above will help you find an answer.
Go to the top of the page
 
Doug Steele
post Dec 18 2008, 08:08 AM
Post#5


UtterAccess VIP
Posts: 21,490
Joined: 8-January 07
From: St. Catharines, ON (Canada)


Thanks for stepping in John, but your third example is wrong. It should be
SELECT c.*, d.* FROM [;database=c:\src\football\k_data.mdb].Team AS c INNER JOIN [;database=c:\src\football\k_data_may_2004.mdb].Team AS d ON c.Team = d.Team;
(In other words, you need square brackets around the database name, not parentheses, and you need a period between the square brackets and the table name, not a space)
I'm still not sure, though, that you can do an Update there there are two inner joins in the query.
Go to the top of the page
 
mishej
post Dec 18 2008, 08:23 PM
Post#6


Retired Moderator
Posts: 11,289
Joined: 25-September 02
From: Milwaukee, WI


Hi Doug; hope you're doing well.
That query works. Access will convert the parens to the format you describe but you can enter as I did and it works.
All the above examples are from actual applications. But I couldn't find an UPDATE example.
Go to the top of the page
 
mishej
post Dec 18 2008, 09:27 PM
Post#7


Retired Moderator
Posts: 11,289
Joined: 25-September 02
From: Milwaukee, WI


OK... this is interesting. I went back to check on the app (which was developed originally in A2000).
The query is there and I can run it successfully but if I copy the SQL into a new query it doesn't run as you said.
Perhaps there is a query property set or the query has never been decompiled... no that can't be it since I compact this app all the time and that is what decompiles a query and forces the query into un-compiled state with the next running of the query forcing it to do its SHOW PLAN and re-compile.
It's a mystery... but it works in my A2007 but I'll concede that it is a useless sample for the poster unless they make the changes yoiu identified.
Thanks. I'll let you know if I figure out what is going on (out of curiosity).
Go to the top of the page
 
mishej
post Dec 18 2008, 09:48 PM
Post#8


Retired Moderator
Posts: 11,289
Joined: 25-September 02
From: Milwaukee, WI


You were absolutely right Doug. What happened is that I must have created the query with the syntax you provided. So I just now created a new query with your syntax and saved it. I then ran it and then re-opened it up in Design mode and Access had converted your syntax into the SQL I posted.
This led to my erroneous belief that the SQL had been entered that way.
Thanks for correcting my error. Happy Holidays.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    12th December 2017 - 03:24 AM