• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 260
  • Last Modified:

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'
0
smm6809
Asked:
smm6809
  • 9
  • 5
1 Solution
 
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
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
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
 
smm6809Author Commented:
I accepted my own comment because it was what ended up working for me in this scenario.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 9
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now