Insert

[ScottF]

Lets see if I can explain this. Here the code
Dim dfs As Database
Dim db As Database
Dim dbs As Recordset
Sql1 = "Select * from vac where month between #" & ee & "# and #" & ss & "#"
Set db = OpenDatabase(txtPath)
Set dbs = db.OpenRecordset(Sql1)
Set dtaVac.Recordset = dbs
dtaVac.Refresh
Set dfs = OpenDatabase(txtPath)
dfs.Execute " INSERT INTO vacreport " _
& "SELECT * " _
& "FROM '" & dtaVac.Recordset & "';"

dfs.Close
dbs.Close
db.Close
I want to insert the result of the SQL sql1 into the table vacreport. I try this way with no result can you help!! I'm have a mental block this should be easy to do.

[Gen-X]

Ok..

The problem you have here is that you are crossing recordsets with SQL statements... you just can't do it.

dtaVac.Recordset returns to you a collection of records based on the SQL statement you gave it... trying to assign that as part of a string is like trying to tack a house onto the back of your bike.

The answer depends on whether the table you are getting your info from (vac) and the table you are inserting into (vacreport) are on the same database. If they are then its simple :

sSQL = "INSERT INTO vacreport SELECT * FROM vac WHERE <clause>"

(I am not sure about your WHERE clause here though.... "month BETWEEN #x# AND #y#"... that doesn't sound like SQL to me at all!!! It certainly isn't Transact-SQL as far as I know.)

If they are in different databases you will need to do a field for field match using the recordsets.

Set rstVac = dfs.OpenRecordset("vac",<options>)
Set rstVacReport = dbs.OpenRecordset("vacreport",<options>)

Do while not rstVac.EOF
    rstVacReport.AddNew
    rstVacReport("field1") = rstVac("field1")
    ...
    ...
    rstVacReport("fieldX") = rstVac("fieldX")
    rstVacReport.Update
    rstVac.MoveNext
Loop

I think you should have a read up on Recordsets to get a feel for what they are all about.

[ScottF]

They are in the same database and I'll try this

[ScottF]

I used this code instead and it work.
Set db = OpenDatabase(txtPath)
db.Execute "INSERT INTO vacreport SELECT * FROM vac WHERE month between #" & ee & "# and #" & ss & "#"
db.Close
Now I built a Crystal Report for that table and printed it and it came out fine. Thanks Gen-X now I have a new way to create a Report with SQL insteated of trying to figure out how to work with Crystal R.