Writing a record to another Access database using append query.

I am trying to write a transaction to another database when the user updates a record. The VBA statement is:    
 If Me.txtUpdate = -1 Then
        mConn.Execute "qClerkTransact"
End If

The append and database  information is in the query itself. I get the error:
Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'
smm6809Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
What's the syntax of "qClerkTransact"?

You might find it simplest to link the "other" database table in your Access app. This way, you can work with it as if it were a local table.
0
smm6809Author Commented:
That's a thought. I'll try linking first.
0
smm6809Author Commented:
I got the same error. Here is the SQL for the query.

INSERT INTO tblDealersTransact ( dtTransact, strTransactUser, numDealerID, strDealer, ysnDelete, dtDelete ) IN 'C:\Sue\DealLogTransactions.accdb'
SELECT Date() AS [Date], [forms]![frmMain]![txtUser] AS [User], tblDealers.numDealerID, tblDealers.strDealer, tblDealers.ysnDelete, tblDealers.dtDelete
FROM tblDealers
WHERE (((tblDealers.numDealerID)=[forms]![frmDealers]![txtDealerID]));
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you link the table, you've got to remove this part of the query:

 IN 'C:\Sue\DealLogTransactions.accdb'

You'd end up with:

INSERT INTO tblDealersTransact ( dtTransact, strTransactUser, numDealerID, strDealer, ysnDelete, dtDelete )
SELECT Date() AS [Date], [forms]![frmMain]![txtUser] AS [User], tblDealers.numDealerID, tblDealers.strDealer, tblDealers.ysnDelete, tblDealers.dtDelete
FROM tblDealers
WHERE (((tblDealers.numDealerID)=[forms]![frmDealers]![txtDealerID]));
0
smm6809Author Commented:
I did that. Same error...
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
If you open a new query and switch to SQL view, and paste this in:

SELECT Date() AS [Date], [forms]![frmMain]![txtUser] AS [User], tblDealers.numDealerID, tblDealers.strDealer, tblDealers.ysnDelete, tblDealers.dtDelete
FROM tblDealers
WHERE (((tblDealers.numDealerID)=[forms]![frmDealers]![txtDealerID]));

Then run that part of the query, do you get the results you expect from that SELECT statement?
0
smm6809Author Commented:
Yes.
0
smm6809Author Commented:
Is there anyone who can help?
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Patience ... we're all volunteers, and have jobs/lives outside of EE.

Were there any NULL or missing values from the SELECT statement you ran? Your error is fairly specific - Invalid SQL - but your SQL statements look correct.

Only other suggestion I'd have would be to enclose the entire SELECT statement in parentheses, but I don't think that'll help.

You might also Compact your database. It's not uncommon for corruption to cause odd behavior like this.
0
smm6809Author Commented:
Thanks, didn't mean to sound insincere, far from it. I need you guys! I will look at what you suggested.
0
smm6809Author Commented:
Nothing seemed to work so I ended up using a select statement in my code and updating each field one by one. Thanks for your help though.
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
You really shouldn't select my comment as a solution, since it did not resolve your issue. Instead, you should post what you did that worked (i.e. the code using the SELECT statement) and accept that comment.

You can ask the Moderators to reopen this question to do that.
0
smm6809Author Commented:
I ended up using a SELECT statement and looping through the recordset to append the data.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
smm6809Author Commented:
I accepted my own comment because it was what ended up working for me in this scenario.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.