Append SQL result to Table

[rtdc]

I have spend hours trying to work this out and either to thick, rusting or its very hard

What I want to do is take the result form a sql query in vba and append it to a pre-existing table with the same field names as used in the sql.

My SQL query is

Srch = built on a form and in this example = [Car List]![Make], [Car List]![Model], [Car List]![Transmission] though it could be any combination of many fields

WHE = built on a form and in this example = [Car List]![Colour(s)]) = "Red" though it could be anything suitable for a where statement

T1 is the table Car List and looked up

Set RS = CurrentDb.OpenRecordset("SELECT " & Srch & " FROM " & T1 & " WHERE (((" & WHE & " ))")

This works and returns: -

Volvo 144 Auto Manual
Renault 15 Manual
BMW 525 Auto
Renault 5 Manual
Rover SD1 Manual
Ford P100 Manual
Vauxhall Chevette Manual
Ford Fiesta Manual
Nissan Micra Manual
Nissan Micra Manual

During the on form building of this query a table called Temp was created with the fields (in this example) Make, Model, Transmission.

I can export this successfully to excel where I can see the full result of the query but what I want to do is append this result to the Temp table. I have looked on here and other sites and either I have not found the answer or missed it.

Any ideas on how to do this, if it is possible that is?

 

[Trevor G]

The easist way to learn to do this is to create a query in the Database and change it to an Append then when you look at the code it will give you what you want. But you need to look at INSERT INTO

INSERT INTO tblTemp
SELECT
FROM tblProjectName;

 

[rtdc]

The easist way to learn to do this is to create a query in the Database and change it to an Append then when you look at the code it will give you what you want. But you need to look at INSERT INTO

INSERT INTO tblTemp
SELECT
FROM tblProjectName;

I tried this approach and works until I want to pass it the variable field names, what I need is the code to insert into temp (any number variable filed names) from (sql result)

Can it be done?

 

[Trevor G]

From your thread

What I want to do is take the result form a sql query in vba and append it to a pre-existing table with the same field names as used in the sql.

But now you are asking for

what I need is the code to insert into temp (any number variable filed names) from (sql result)

You would be looking to create a table at the execution time, as you can't add fields at run time without being specific with there names. But you can re write a query at execution time which then may give you the result you want.

 

[jdraw]

Perhaps you could tell us what you are hoping to do with an example or two.
As Trevor has pointed out, your question seems to have changed between posts.

In order to add row(s) to a table, you really have to know which table and also which fields and field types comprise the table.

Can it be done?

Best answer I have is possibly, but we have to agree on what it is first.

Please elaborate on your requirement.

 

[rtdc]

Sorry I didn’t make it clear. When I said ‘pre-existing table with the same field names as used in the sql.’ What I meant was the same field names that had been searched on in the SQL query. In my example Make, Model and Transmission but these could be any name from the Car List table that I am searching on. As the fields can be anything from Car List I was hoping to pass a variable in the Insert into command but this is not possible.

I have a form set up to select which fields I want to query on and what Where I want to use. At the same time a table is created using the same fields used to query on, [Car List]![Make], [Car List]![Model], [Car List]![Transmission] are used in the query and the table is created and columns added named Make, Model and Transmission.

I have is my sql query returning the correct answer, this I can send to excel and read in full. I have an empty table with the field names used in my query as field names.

What I am trying to do is take my sql query output and append it to my empty table but because in each running of the query and creation of the table the field names can be deferent I don’t have a constant name I can use in the insert into line.

I know I am taking the wrong approach and have tried a few ways but to no avail, what I want to end up with is a access table that looks like this (in this example): -

Make Model Transmission
Volvo 144 Auto Manual
Renault 15 Manual
BMW 525 Auto

I tried to create a table at the execution time of the query and populate it then but I couldn’t make it work, I could do with some code samples to show me the way.

 

[jdraw]

To create a table at execution time you need code along these lines executed via vba

Sub CreateTbl()
Dim sqla As String
sqla = "CREATE table tblxmlnodes" & _
 "(id counter,  nodeparent Number " & _
      "  , nodename varchar(50)" & _
      "  , nodechain varchar(250)" & _
      "  , nodelevel varchar(20)" & _
      "  , nodeorder NUMBER " & _
      "  , nodeleaf varchar(30)" & _
      " , nodeValue varchar(250));"
CurrentDb.Execute sqla, dbFailOnError
End Sub

and in this case I know which fields to create in the table.

I'm not sure why you are creating a new table since, it seems, you can get the data from existing tables. Is there a real purpose for the new table?

 

[rtdc]

I got it to do what I wanted in the end by just exporting to Excel and bringing it back, not as clean as I would like but it does what I intended. Its more an exercise in can I do it rather than it must be done, I could have done it an easier way but I wouldn't have learned anything that way.

Thanks for the help.

 

[jdraw]

Glad you have it working. Learning is rewarding...