Link to home
Start Free TrialLog in
Avatar of smm6809
smm6809

asked on

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'
Avatar of Scott McDaniel (EE MVE )
Scott McDaniel (EE MVE )
Flag of United States of America image

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.
Avatar of smm6809
smm6809

ASKER

That's a thought. I'll try linking first.
Avatar of smm6809

ASKER

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]));
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]));
Avatar of smm6809

ASKER

I did that. Same error...
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?
Avatar of smm6809

ASKER

Yes.
Avatar of smm6809

ASKER

Is there anyone who can help?
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.
Avatar of smm6809

ASKER

Thanks, didn't mean to sound insincere, far from it. I need you guys! I will look at what you suggested.
Avatar of smm6809

ASKER

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.
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.
ASKER CERTIFIED SOLUTION
Avatar of smm6809
smm6809

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of smm6809

ASKER

I accepted my own comment because it was what ended up working for me in this scenario.