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'
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'
ASKER
That's a thought. I'll try linking first.
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\DealLogTransaction s.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 ]![txtDeal erID]));
INSERT INTO tblDealersTransact ( dtTransact, strTransactUser, numDealerID, strDealer, ysnDelete, dtDelete ) IN 'C:\Sue\DealLogTransaction
SELECT Date() AS [Date], [forms]![frmMain]![txtUser
FROM tblDealers
WHERE (((tblDealers.numDealerID)
If you link the table, you've got to remove this part of the query:
IN 'C:\Sue\DealLogTransaction s.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 ]![txtDeal erID]));
IN 'C:\Sue\DealLogTransaction
You'd end up with:
INSERT INTO tblDealersTransact ( dtTransact, strTransactUser, numDealerID, strDealer, ysnDelete, dtDelete )
SELECT Date() AS [Date], [forms]![frmMain]![txtUser
FROM tblDealers
WHERE (((tblDealers.numDealerID)
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 ]![txtDeal erID]));
Then run that part of the query, do you get the results you expect from that SELECT statement?
SELECT Date() AS [Date], [forms]![frmMain]![txtUser
FROM tblDealers
WHERE (((tblDealers.numDealerID)
Then run that part of the query, do you get the results you expect from that SELECT statement?
ASKER
Yes.
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.
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.
ASKER
Thanks, didn't mean to sound insincere, far from it. I need you guys! I will look at what you suggested.
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.
You can ask the Moderators to reopen this question to do that.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I accepted my own comment because it was what ended up working for me in this scenario.
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.