Update query question

  • Thread starter Thread starter OldEnough
  • Start date Start date
O

OldEnough

When you create an action query in design view access provides a popup widow
that allows you to select "Current database" or another database for Append
queries and Maketable queries. However, it doesn't provide this option for
UPDATE queries.

If the two tables were local tables the SQL statement would be

Docmd.runsql "UPDATE tblsvcCall INNER JOIN tblTransSvcCall ON
tblsvcCall.OrderDetailID = tblTransSvcCall.OrderDetailID
SET tblTransSvcCall.CustomerID = [tblSvcCall].[CustomerID],
tblTransSvcCall.DateTaken = [tblSvcCall].[DateTaken];"

Can this be done using ADO for an unlinked table? Can someone suggest a
direction on how to do this. I have spent hours hunting without success. I
would appreciate any help someone could offer.

Thanks in advance for looking at this.
 
Thanks Jeff

I hope my question isn't foolish. I think there could be a problem with
early morning links to the database exceeding Access limits. The connections
are brief but I think there are too many to permit linking the tables for all
the users who would use them in a short period. I hoped for a solution using
ADO for a connect - update - disconnect for each user. I already use this
approach for collecting data and inserting new records. What do you think?

Jeff Boyce said:
One approach to updating DatabaseB.TableB is to link to it from within
DatabaseA. Once linked, you can use it as if it were "local" to DatabaseA.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

OldEnough said:
When you create an action query in design view access provides a popup widow
that allows you to select "Current database" or another database for Append
queries and Maketable queries. However, it doesn't provide this option for
UPDATE queries.

If the two tables were local tables the SQL statement would be

Docmd.runsql "UPDATE tblsvcCall INNER JOIN tblTransSvcCall ON
tblsvcCall.OrderDetailID = tblTransSvcCall.OrderDetailID
SET tblTransSvcCall.CustomerID = [tblSvcCall].[CustomerID],
tblTransSvcCall.DateTaken = [tblSvcCall].[DateTaken];"

Can this be done using ADO for an unlinked table? Can someone suggest a
direction on how to do this. I have spent hours hunting without success. I
would appreciate any help someone could offer.

Thanks in advance for looking at this.
Click to expand...
Click to expand...
 
My experience has been with DAO.

Perhaps one of the other newsgroup readers can offer ideas...

Good luck!

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

OldEnough said:
Thanks Jeff

I hope my question isn't foolish. I think there could be a problem with
early morning links to the database exceeding Access limits. The connections
are brief but I think there are too many to permit linking the tables for all
the users who would use them in a short period. I hoped for a solution using
ADO for a connect - update - disconnect for each user. I already use this
approach for collecting data and inserting new records. What do you think?

Jeff Boyce said:
One approach to updating DatabaseB.TableB is to link to it from within
DatabaseA. Once linked, you can use it as if it were "local" to DatabaseA.

--
Regards

Jeff Boyce
www.InformationFutures.net

Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

OldEnough said:
When you create an action query in design view access provides a popup widow
that allows you to select "Current database" or another database for Append
queries and Maketable queries. However, it doesn't provide this option for
UPDATE queries.

If the two tables were local tables the SQL statement would be

Docmd.runsql "UPDATE tblsvcCall INNER JOIN tblTransSvcCall ON
tblsvcCall.OrderDetailID = tblTransSvcCall.OrderDetailID
SET tblTransSvcCall.CustomerID = [tblSvcCall].[CustomerID],
tblTransSvcCall.DateTaken = [tblSvcCall].[DateTaken];"

Can this be done using ADO for an unlinked table? Can someone suggest a
direction on how to do this. I have spent hours hunting without success. I
would appreciate any help someone could offer.

Thanks in advance for looking at this.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top