HELP in Append Query in VBA

[yami_86 via AccessMonster.com]

I need help to do a append query in VBA.

The code is the following:

Public Sub CreateForm()
Dim DateInv As String
Dim strQryDelete As String
Dim strQryAppend As String


DateInv = fnGetDate ' fnGetDate is a function that gets the date from an
input box (mmddaaaa)'
strQryDelete = "DELETE * FROM tblCourseReport"
strQryAppend = "INSERT INTO tblCourseReport SELECT" & DateInv & " From "
& DateInv
DoCmd.RunSQL strQryDate
DoCmd.RunSQL strQryAppend


Note:
***DateInv is the variable that changes everyday. The table is name after the
variable for example: 04032009.
All the field values in DateInv is added to another table in the Current
Database which is tblCourseReport but I just don't know how to do this. You
can see that the strQryAppend is not correct and I get code errors.

Please help me to make this WORK.!!!!!!!!

Thanks

 

[Dirk Goldgar]

I need help to do a append query in VBA.

The code is the following:

Public Sub CreateForm()
Dim DateInv As String
Dim strQryDelete As String
Dim strQryAppend As String


DateInv = fnGetDate ' fnGetDate is a function that gets the date from
an
input box (mmddaaaa)'
strQryDelete = "DELETE * FROM tblCourseReport"
strQryAppend = "INSERT INTO tblCourseReport SELECT" & DateInv & " From
"
& DateInv
DoCmd.RunSQL strQryDate
DoCmd.RunSQL strQryAppend


Note:
***DateInv is the variable that changes everyday. The table is name after
the
variable for example: 04032009.
All the field values in DateInv is added to another table in the Current
Database which is tblCourseReport but I just don't know how to do this.
You
can see that the strQryAppend is not correct and I get code errors.

I think what you want is this:

strQryAppend = _
"INSERT INTO tblCourseReport SELECT * From " & DateInv

I trust that you will discard the table after importing. It would be
problematic to work with a table name consisting solely of numbers.

 

[John Spencer MVP]

This line needs () after fnGetDate to return the value
DateInv = fnGetDate()

Mext you need to check to see if you got a valid value returned

If (some test to confirm valid table name has been returned) Then
strQryDelete = "DELETE * FROM tblCourseReport"

Next you need to make changes to this line
strQryAppend = "INSERT INTO tblCourseReport SELECT * From [" & DateInv & "]"
'That ASSUMES that tblCourseReport has the same structure/field names as
DateInv table

DoCmd.RunSQL strQryDate
DoCmd.RunSQL strQryAppend
End IF

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[John W. Vinson]

I need help to do a append query in VBA.

The code is the following:

Public Sub CreateForm()
Dim DateInv As String
Dim strQryDelete As String
Dim strQryAppend As String


DateInv = fnGetDate ' fnGetDate is a function that gets the date from an
input box (mmddaaaa)'
strQryDelete = "DELETE * FROM tblCourseReport"
strQryAppend = "INSERT INTO tblCourseReport SELECT" & DateInv & " From "
& DateInv
DoCmd.RunSQL strQryDate
DoCmd.RunSQL strQryAppend


Note:
***DateInv is the variable that changes everyday. The table is name after the
variable for example: 04032009.
All the field values in DateInv is added to another table in the Current
Database which is tblCourseReport but I just don't know how to do this. You
can see that the strQryAppend is not correct and I get code errors.

Please help me to make this WORK.!!!!!!!!

Thanks

I'd suggest that you STOP, and take a step back.

It's *very* rare that you would need to empty and repopulate a table. It's
almost certainly not necessary to do so merely to generate a report, as your
table name suggests - a Report can be, and usually would be, based on a Query.

What is this Dateinv table??? Is it coming from some outside source, or do you
actually have a different table in your database for each day? That would be a
*very* bad idea.

I suspect the error in your strQryAppend is that you don't have a blank after
the SELECT - your query string will come out

INSERT INTO tblCourseReport SELECT04032009 FROM 4032009

Try using a blank, and square brackets to ensure that the numeric string is
treated as a name. To select all fields in the table use *.

strQryAppend = "INSERT INTO tblCourseReport SELECT * From ["
& DateInv & "]"