Executing queries in Access from Excel

[Felipe99]

Hi,

I am making a tool that has as a front end an Excel workbook and as a back end an Access DB. I am storing and processing the data at the DB. All the data processing and storing is triggered by buttons in the workbook, therefore I have a set of macros that runs queries in the DB. All the queries that I run are defined at the access data base.

Here the problem: I run the queries in the Excel macros by their names, and this sometimes works and sometimes not. Bellow a piece of my code.

Please Login or Register  to view this content.

--------------------------------------------------------------------------------------------------------------------------

In the function EjecutarSQL "c" is for example an Insert query defined in the DB and named C1, thus when I want to execute this query I write EjecutarSQL("C1") in my code, and not EjecutarSQL("Insert (x1, x2, x3) into ..."). I want to keep running the queries in this way because this gives me flexibility to modify my tool without modifying the code.

The error I sometimes get is in the line “cn.Execute c”, and the message is invalid SQL syntax.

Any idea or suggestion is welcomed. Thanks in advance.

Felipe99.

[JeanRage]

Hi,

Make sure in VBE Tools References Microsoft ActiveX DataObjects 2.8 or higher is checked...

HTH

[Felipe99]

I have review this and it is checked.

Any other idea?

Thanks,

Felipe99

[romperstomper]

The error would imply that the SQL itself is incorrect, rather than the code necessarily. What is the SQL in question?

[royUK]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

Added this once only,future posts that break the Forum Rules may be closed,so READ THEM

[Richard Schollar]

In a similar vein to what the big R asked, is it always the same query that fails (and does this particular query ever work)? Also, what are you doing with the recordset produced? You don't seem to be assigning it anywhere.

Richard

[romperstomper]

Surely, of the two of us, you would be the big R?

[DonkeyOte]

The mighty big R's ...

[Richard Schollar]

Surely, of the two of us, you would be the big R?

Certainly in terms of girth and height Probably not brain power, unfortunately