Syntax of Append Query

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

ca1358 - 03 Feb 2006 17:55 GMT

I am using Access Form and on a command button I want to run append query.
How would I write it. On Click Event - the following code is the SQL view
for the queries in Access.

Any help would greatly be appreciated!

INSERT INTO Table1 ( [Commit], [AS400 ID], [Client Name], RegBy, Buyer,
PoolCode, Term, LoanAmt, Coupon, Desk, Early, Other1, Other2, Other3, Net,
Quote, Par, PED, DelDt, PoolMth, MBSCC, Dealer, TradeDt, SettleDt, RecDt,
IntRate, Comment, AS400ErrMsg, Confirmation, AS400Update, TRPrice, SchedFund,
AfterHours, AOTConfirmDt, TakenTime, EnteredTime )
SELECT TransferFile.Commit, TransferFile.[AS400 ID], TransferFile.[Client
Name], TransferFile.RegBy, TransferFile.Buyer, TransferFile.PoolCode,
TransferFile.Term, TransferFile.LoanAmt, TransferFile.Coupon,
TransferFile.Desk, TransferFile.Early, TransferFile.Other1,
TransferFile.Other2, TransferFile.Other3, TransferFile.Net,
TransferFile.Quote, TransferFile.Par, TransferFile.PED, TransferFile.DelDt,
TransferFile.PoolMth, TransferFile.MBSCC, TransferFile.Dealer,
TransferFile.TradeDt, TransferFile.SettleDt, TransferFile.RecDt,
TransferFile.IntRate, TransferFile.Comment, TransferFile.AS400ErrMsg,
TransferFile.Confirmation, TransferFile.AS400Update, TransferFile.TRPrice,
TransferFile.SchedFund, TransferFile.AfterHours, TransferFile.AOTConfirmDt,
TransferFile.TakenTime, TransferFile.EnteredTime
FROM TransferFile;

Signature

ca1358

Reply to this Message

Douglas J Steele - 03 Feb 2006 18:09 GMT

If that query is saved, all you need to do is invoke it.

CurrentDb.Execute "MyQueryName", dbFailOnError

or

CurrentDb.QueryDefs("MyQueryName").Execute dbFailOnError

If it isn't saved, store it in a string (say strSQL), then use

CurrentDb.Execute strSQL, dbFailOnError

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> I am using Access Form and on a command button I want to run append query.
> How would I write it. On Click Event - the following code is the SQL view
[quoted text clipped - 20 lines]
> TransferFile.TakenTime, TransferFile.EnteredTime
> FROM TransferFile;

Reply to this Message

Klatuu - 03 Feb 2006 18:10 GMT

In the click event of the command button you can do it a couple of ways. One
would be to use a stored query and the other would be to create an SQL
statment as a string. The syntax is almost identical in either casel:

Using a stored query:
DoCmd.Execute("MyQueryName"), dbFailOnError

Using SQL:

Dim strSQL as String

strSQL = "INSERT INTO blah blah blan;"
DoCmd.Execute(strSQL), dbFailOnError

Reply to this Message