Inserting records into access table from sql database

[daveonion]

Hi I have an ado recordset created from a query,
dependent on selections from a user in a form certain tables are joined to create the select statement. I need to populate an access table with this data derived from the select statement. Looping through each individual record and adding them to the table is too time consuming, an insert statement would be more efficient, however access doesnt like the Inner joins created from the select statement so i cant run the docmd.runsql. Anybody know of a way,

 

[Remou]

Perhaps you could post the SQL for the Select statement?

 

[daveonion]

hi the statement is below

Select uid_scf, scf_uid_risk from [dbo_tbl_Safety checkform] T1
Inner Join dbo_tbl_site_number T2 on t1.uid_Scf = T2.ris_site_risuid
Inner Join dbo_tbl_illumination T3 on t1.uid_Scf = T3.ris_illumination_risuid
Where T2.ris1_sitenumber = 2 And T3.ris1_illumination = 27 And t1.uid_scf > 1
group by t1.uid_scf, t1.scf_uid_risk

 

[Remou]

Could it be that Jet is objecting to the lack of brackets in your joins? How about:

INSERT INTO [dbo_tbl_NEW_TABLE] ( uid_scf, scf_uid_risk )
SELECT T1.uid_scf, T1.scf_uid_risk
FROM ([dbo_tbl_Safety checkform] AS T1 INNER JOIN dbo_tbl_site_number AS T2 ON T1.uid_scf = T2.ris_site_risuid) INNER JOIN dbo_tbl_illumination AS T3 ON T1.uid_scf = T3.ris_illumination_risuid
WHERE (((T2.ris1_sitenumber)=2) AND ((T3.ris1_illumination)=27) AND ((T1.uid_scf)>1))
GROUP BY T1.uid_scf, T1.scf_uid_risk;

 

[daveonion]

Hi Remou,

yes thats the problem (the brackets), the issue i've got though is the sql statement can be created by selecting one of many fields contained in a list box which represent table names, as it is now i have it querying the sql server direct by using the created select statement, this however can change dependent on variables selected.
As its incompatible (the sql statement) with access i am having to loop through the recordset and add records into an access table individually, however this is taking forever as it could contain thousands of records.

The ideal solution would be to insert directly from my generated recordset into an access table but i have no idea how to do it

 

[Remou]

I thought the example above would append the select data to a table called dbo_tbl_NEW_TABLE, containing fields uid_scf and scf_uid_risk:
[tt]INSERT INTO [dbo_tbl_NEW_TABLE] ( uid_scf, scf_uid_risk ) ...[/tt]
Is this not the case?

If you have many possible set-ups, perhaps you need a make table query (?):
[tt]SELECT T1.uid_scf, T1.scf_uid_risk INTO dbo_tbl_NewTable
FROM ([dbo_tbl_Safety checkform] ...[/tt]