Discussion Locked

Access queries and SQL Server views.

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hi guys,

I'm trying to run a couple of Access SQL queries with the DoCmd.RunSQL command. Here is a simplified version of my code:

"Private Sub Report_Open(Cancel As Integer)
Dim db As Database

Set db = CurrentDb
DoCmd.RunSQL "DELETE * FROM tblLocalCompanies"
AddRunSQL
Set db = Nothing
End Sub

Private Sub AddRunSQL()
Dim db As Database

Set db = CurrentDb

DoCmd.RunSQL "SELECT ID, NAICCN, COMPANY, SuperFleet_Code, " & _
"SuperFleet_Apex, FLEET_CODE, Fleet_Apex, MEMBER_TYPE, CO_ID " & _
"INTO tblLocalCompanies" & _
"FROM vw_CompaniesOnly"

Set db = Nothing

End Sub"

The problem is the second RunSQL statement. I'm trying to put a recordset, selected from a SQL view (vw_CompaniesOnly), into a local table in an .mdb file. The .mdb operates with linked tables and views to a SQL Server backend. When I try to create the recordset from the linked view "vw_CompaniesOnly" and dump it in a local table "tblLocalCompanies", the compiler gives me the following error "Run-time error '3067': Query input must contain at least one table or query."

Anyone who had run into a similar problem?

Regards. Boris.

3 replies

by glen_mcleod

The problem here is that the "Select Into" statement *creates* a new table based on the column list of the select statement. If the table already exists, then you get an error and the statement fails.

To do what you want here, either drop the table instead of deleting * from it, or change the statement to:

INSERT INTO tblLocalCompanies
SELECT ID, NAICCN, COMPANY, SuperFleet_Code, SuperFleet_Apex, FLEET_CODE, Fleet_Apex, MEMBER_TYPE, CO_ID
FROM vw_CompaniesOnly

Glen

by Former member

This question was closed by the author

This discussion is closed to new replies.