Solved Fill two cells by using INSERT INTO... (1 Viewer)

[Gizem]

Hello from Germany,

i got so many helpful advices yesterday and now i have an other problem. I think you can help me also.

It is possible to fill one cell bye using INSERT INTO with the following code:

DB.Execute "INSERT INTO  ProjekteNam(PS_P_NAME)SELECT (''" & Wert & "')"

But now i have to fill the cell behind of the column:

DB.Execute "INSERT INTO  ProjekteNam(POSITION,PS_P_NAME)SELECT (1,'" & Wert & "')"

There is not any error, but the code does not work.
Maybe you can see the failure

 

[Gasman]

I would just use the Values clause?
Plus you do not even have a space between ) and Select?

Select would normally be used fetching records from another source?

Also google is your friend. https://docs.microsoft.com/en-us/of...ce/insert-into-statement-microsoft-access-sql

You are best off putting the statement into a string variable and then Debug.Print that until you get it correct.

You can even test the output by pasting to a sql window and run from there, to see if it works.

 

[Uncle Gizmo]

The Notorious INSERT INTO SQL Statement Raises its Ugly Head again!​

The problem with the INSERT INTO SQL Statement is that it's difficult to get the Punctuation/Syntax of the SQL string correct. My solution is to separate out the problem. You can see how it's done on my blog here:-

Insert Into SQL Statement - Nifty Access

INSERT INTO SQL Statement I regularly see messages on MS Access Forums where people are struggling to make the INSERT INTO SQL Statement work. I can understand why, for a start the error messages (if you get any) have very little relationship to what is actually wrong. The syntax, the usual...

www.niftyaccess.com

 

[Galaxiom]

It is possible to fill one cell bye using INSERT INTO with the following code:

There are no "cells" in Access.

 

[arnelgp]

you can remove the parenthesis ():

DB.Execute "INSERT INTO ProjekteNam(POSITION,PS_P_NAME) SELECT 1,'" & Wert & "'"

 

[Galaxiom]

The Notorious INSERT INTO SQL Statement Raises its Ugly Head again!​

The problem with the INSERT INTO SQL Statement is that it's difficult to get the Punctuation/Syntax of the SQL string correct. My solution is to separate out the problem. You can see how it's done on my blog here:-

Insert Into SQL Statement - Nifty Access

INSERT INTO SQL Statement I regularly see messages on MS Access Forums where people are struggling to make the INSERT INTO SQL Statement work. I can understand why, for a start the error messages (if you get any) have very little relationship to what is actually wrong. The syntax, the usual...

www.niftyaccess.com

Pointless obfuscation. There is nothing notorious about the Inserts. The key to writing SQL is formatting.

 

[Uncle Gizmo]

Pointless obfuscation

No,no, no, Greg you obviously haven't looked at it.

Dividing it up into to separate logical pieces is not obfuscation.

Obfuscation is where you use cryptic sounding variables and the like to make your code difficult and awkward to read and copy.

However, I agree with you that when you are skilled in writing SQL, as I know you are, this approach would be pointless. But the number of regular posts here from people having problems with INSERT INTO SQL Statements confirms there is an issue.

 

[Pat Hartman]

1. Append queries insert ROWS NOT column values. If you want to update values in an existing row, use an Update query.
2. If you want to insert a new record, with only some values filled in, that's fine. Just create the query that way. When you don't know the syntax, open the QBE and build the query there. To build an append query from data in an existing row, start with the select part of the query. Include a where argument so you can select the row to get the data from. When that works, change the query type to Append and Access will request the target table name. If the column names match in both tables, Access will automatically fill in the Append To column names. If the names don't match, you'll need to type them yourself. Save the query. Switch to SQL view to see the SQL. I always use querydefs but if you prefer, you can copy and paste the SQL string into your VBA.
3. There is no such thing as a Cell in a relational database. A cell is the name assigned to the INTERSECTION of a row and a column. In relational databases you refer to the Column name and you use a where clause to select the row(s) you want to work with.

 

[Gizem]

Thank you all

DB.Execute "INSERT INTO  ProjekteNam(POSITION,PS_P_NAME) Values ('" & varr & "' , '" & Wert & "')"

 

[Gasman]

I cannot see that working as no space before Values?

 

[arnelgp]

you previous sql will work if you change the delimiter:


DB.Execute "INSERT INTO ProjekteNam(POSITION,PS_P_NAME) SELECT '" & varr & "','" & Wert & "'"

 

[MajP]

To make your life a lot easier here is a Convert to SQL CSQL function. It will properly delimit dates, strings, nulls, booleans, text etc.

Get Filter from Controls, CSQL, and Combine Multiple Filters

If you follow my threads, I try to demonstrate how to write code that is flexible, generic, encapsulated, fault proof, and debuggable. My goal is always to try to write code once that can be re-used often and everywhere. I do this in functions, procedures, and class modules. This may require...

www.access-programmers.co.uk

so it makes typing a lot easier especially for dates

"...values (" & csql(dateField) & ", " & csql(textField) & ", " csql(numericField) & ", " & csql(fieldwithNull) & ")"

 

[The_Doc_Man]

This is partly a matter of nomenclature. Since you are relatively new here and your question shows that you are new to Access, I'll explain why you got answers all over the map.

When you want to add a WHOLE RECORD and it represents data that was not previously in the table, you use the INSERT INTO syntax. When you want to change a part of an existing record, you use UPDATE syntax. Then realize that Access at that level is working on records. Therefore, your choice of action words confused the issue.

Yes, you can target a single "cell" - except that Access doesn't use cells. The correct language is to "UPDATE a field in a record." To do that, you need to know the table name, the field name, the new value, and a way to specify the exact record using some other field. Yes, SQL can do that.

UPDATE table-name SET field-name = new-value WHERE other-field-name = selection-value ;

If you have to set fields A, B, and C to new values in the same record, that becomes

UPDATE table-name SET A=a-value, B=b-value, C=c-value WHERE ...