Sql – Run SQL Statement in Access using VBA code

ms-accesssqlvba

I can't figure out what is wrong in this,
I'm collecting search criteria from a Form to use it in search.

The SQL line stored like this : (strWhere is the collected info from the Form)

SQLst = "SELECT Deposits.Fines, Deposits.[Deposit Value], Deposits.[Deposit Date], Deposits.Depositor, Info.Tower, Deposits.[Account Number] FROM Info, Deposits Where " & strWhere & ";"

The final SQL statement looks like this:

SELECT Deposits.Fines, Deposits.[Deposit Value], Deposits.[Deposit Date], Deposits.Depositor, Info.Tower, Deposits.[Account Number] FROM Info, Deposits Where ([Account Number] = "1234");

Now After I run the Line using this command (SQLst is the SQL Line up)

DoCmd.OpenQuery SQLst

I get this message:

enter image description here

Best Answer

Not DoCmd.OpenQuery SQLst, that is for saved queries, not SQL strings. You need:

' Best to use a separate instance, so you can get record counts etc.
Set db = CurrentDB

For action queries:

db.Execute SQLst, dbFailOnerror

For SELECT queries, you can either use a recordset or update or create a query.

Set rs = db.Openrecordset(SQLst)

' This query does not exist
Set qdf = db.CreateQueryDef("MyQuery", SQLst)

I doubt that the account number is text, so:

 ([Account Number] = 1234);

Quotes are used for text-type fields / columns, numbers are as is and dates are delimited with hash (#).

Related Solutions

SQLite – UPSERT not INSERT or REPLACE

Assuming three columns in the table: ID, NAME, ROLE

BAD: This will insert or replace all columns with new values for ID=1:

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (1, 'John Foo', 'CEO');

BAD: This will insert or replace 2 of the columns... the NAME column will be set to NULL or the default value:

INSERT OR REPLACE INTO Employee (id, role) 
  VALUES (1, 'code monkey');

GOOD: Use SQLite On conflict clause UPSERT support in SQLite! UPSERT syntax was added to SQLite with version 3.24.0!

UPSERT is a special syntax addition to INSERT that causes the INSERT to behave as an UPDATE or a no-op if the INSERT would violate a uniqueness constraint. UPSERT is not standard SQL. UPSERT in SQLite follows the syntax established by PostgreSQL.

GOOD but tedious: This will update 2 of the columns. When ID=1 exists, the NAME will be unaffected. When ID=1 does not exist, the name will be the default (NULL).

INSERT OR REPLACE INTO Employee (id, role, name) 
  VALUES (  1, 
            'code monkey',
            (SELECT name FROM Employee WHERE id = 1)
          );

This will update 2 of the columns. When ID=1 exists, the ROLE will be unaffected. When ID=1 does not exist, the role will be set to 'Benchwarmer' instead of the default value.

INSERT OR REPLACE INTO Employee (id, name, role) 
  VALUES (  1, 
            'Susan Bar',
            COALESCE((SELECT role FROM Employee WHERE id = 1), 'Benchwarmer')
          );

Sql – Exclude a column using SELECT * [except columnA] FROM tableA

You can try it this way:

/* Get the data into a temp table */
SELECT * INTO #TempTable
FROM YourTable
/* Drop the columns that are not needed */
ALTER TABLE #TempTable
DROP COLUMN ColumnToDrop
/* Get results and drop temp table */
SELECT * FROM #TempTable
DROP TABLE #TempTable

Best Answer

Related Solutions

SQLite – UPSERT *not* INSERT or REPLACE

Sql – Exclude a column using SELECT * [except columnA] FROM tableA

Related Topic

SQLite – UPSERT not INSERT or REPLACE