Solved Fill Access Table by using INSERT INTO ..SELECT.. (1 Viewer)

[June7]

Options:

Build a form where user can select filter criteria and VBA can modify query object or query can have dynamic parameters referencing form controls or VBA builds SQL statement for INSERT SELECT action. Review http://allenbrowne.com/ser-62.html

or

Export report with DoCmd.OutputTo command.

or

There is possibly a way to reference report's RecordSource or Recordset properties and save data to table, but I've never done it.

 

[Gizem]

I spoke to my boss. Now he agreed to the decision to deal with a query instead of a report.
But i get again a syntax error because of the FROM clausel

Dim SQLInsert As String
SQLInsert = " INSERT INTO Tabelle1(bla) " _
& "SELECT Projekt-Auswertung.PS_P_NAME_LANG " _
& " FROM Projekt-Auswertung" _
& " WHERE AnzahlvonRL_C_CAR_KG > '0' "

CurrentDb.Execute SQLInsert

 

[SHANEMAC51]

[Projekt-Auswertung]

 

[SHANEMAC51]

Dim SQLInsert As String
SQLInsert = " INSERT INTO Tabelle1(bla) " _
& "SELECT [Projekt-Auswertung].PS_P_NAME_LANG " _
& " FROM [Projekt-Auswertung]" _
& " WHERE AnzahlvonRL_C_CAR_KG > '0' "

CurrentDb.Execute SQLInsert

 

[Gizem]

Dim SQLInsert As String
SQLInsert = " INSERT INTO Tabelle1(bla) " _
& "SELECT [Projekt-Auswertung].PS_P_NAME_LANG " _
& " FROM [Projekt-Auswertung]" _
& " WHERE AnzahlvonRL_C_CAR_KG > '0' "

CurrentDb.Execute SQLInsert

Thank you

 

[June7]

This is why should not use spaces nor punctuation/special characters (underscore only exception) in naming convention.

 

[Gizem]

Dim SQLInsert As String
SQLInsert = " INSERT INTO Tabelle1(bla) " _
& "SELECT [Projekt-Auswertung].PS_P_NAME_LANG " _
& " FROM [Projekt-Auswertung]" _
& " WHERE AnzahlvonRL_C_CAR_KG > '0' "

CurrentDb.Execute SQLInsert

I want to requery my table with UPDATE instead of INSERT INTO. I get a syntax error. What is wrong do you see my failure?

 

[Gasman]

Debug.Print SQLInsert and see what you get?

 

[SHANEMAC51]

I want to requery my table with UPDATE instead of INSERT INTO.
I get a syntax error.
What is wrong do you see my failure?

I don't understand the question, what do you want to change via UPDATE

 

[The_Doc_Man]

I see that your boss agreed to allow use of a query, which is good, because reports normally don't contain data that is externally accessible. You can see things in a report that hasn't printed yet, but in the final analysis, that report is an IMAGE being sent to the printer. Once it has been sent, you cannot read its contents without something that does an action called "image scraping." Some programs can do this for pure text images but it is a complex process.

The report's internal code can see its data, but only while the report is being formatted. You can also pull data from a form only if/while it is open. This is because neither reports nor forms contain data when closed. To pull data properly, you must (make that MUST) use a table or query.

Next time your boss wants you to pull from a report, say "NO, not technically possible." The reason is that "pulling data from ...." requires what is called a RecordSet for the FROM clause of your SELECT query that will be doing the data extraction. Reports do not offer recordsets and so cannot legally be targets of FROM clauses. The same is true of forms, but there IS a syntax that lets you "peek" into an open form and see what it is showing at the moment. But forms show you one record at a time and if you want multiple records you run into the issue that you would have to manipulate the form to get it to step to another record - and you cannot issue those commands from query execution context.

In both cases, you COULD determine what was being used to provide a recordset (to the report or form) and copy THAT as a way to start with your data extraction. Note, however, that extraction will take more than one step using this approach.

Queries are the "work-horses" of Access. Learn to rely on them for data extraction.