Append Query

[Dawn]

I am trying to run a query and then append the results of
that query to another table in my database. Could someone
point me in the right direction to get started on this in
a macro?

I have one table called Test and I am importing another
table called Test2. Test and Test2 have the same fields,
just data from different date ranges. I want to query
Test2 for data from the current date and then append that
data onto the Table Test. I am looking for guidance on
how to append the data (the result of a SQL query) to
Test.

Thanks!

 

[Steve Schapel]

Dawn,

Make your query based on Test2 table into an Append Query. To do
this, from the query design window, select Append from the Query menu.
You will be prompted for the name of the table to append to, so you
just enter Test. Most likely, the field names from the Test table
will automaticallt be filled in for you in the Append To row of the
query design grid, but just adjust if necessary. When you run this
query, the Test2 reords will be added to the Test table. If records
in Test2 already exist inTest (based on primary key or unique index),
they will simply not be included in the appended records.

- Steve Schapel, Microsoft Access MVP

 

[Guest]

Thanks. I actually am ok with how to do this manually
through Access. I am looking for a way to automate the
process though through vba. Here is what I have so far to
import the second table and run the query against it. I
am just having trouble figuring out how to append the
results of the query onto the first table, Test.

Sub Import_CCUN()
Dim intDay, intMonth, intYear As Integer

Set Db = CurrentDb()

'Imports the ccun_shares database as "Test"
Application.DoCmd.TransferDatabase acImport, "ODBC
Database", "ODBC; DRIVER=SQL Server;
SERVER=111.111.111.111;UID=xxx; PWD=xxx;" _
'& "LANGUAGE=us_english", acTable, "ccun_shares", "Test"

intDay = Day(Now - 1)

If intDay <= 9 Then
intDay = "0" & intDay
End If

If intDay = 1 Then
intMonth = Month(Now) - 1
Else
intMonth = Month(Now)
End If

If Month(Now) = 1 And Day(Now) = 1 Then
intYear = Year(Now) - 1
Else
intYear = Year(Now)
End If

strdate = intMonth & "/" & intDay & "/" & intYear

qrySQL = "SELECT * FROM Test1 WHERE
Test1.sh_ccun_biz_date=#" & strdate & "#"
MakeQueryDef (qrySQL)

End Sub

Function MakeQueryDef(strSQL As String) As Boolean

Dim qdf As QueryDef
Dim rs As Recordset

If qrySQL = " " Then Exit Function

Set qdf = CurrentDb.CreateQueryDef("TestQuery")
qdf.SQL = qrySQL
CurrentDb.QueryDefs.Append qdf
CurrentDb.QueryDefs.Refresh
'????Set rs = qdf.OpenRecordset()
qdf.Close
RefreshDatabaseWindow

MakeQueryDef = True

End Function

 

[Steve Schapel]

Dawn,

DoCmd.OpenQuery "NameOfAppendQuery"