Open Append Query In Datasheet View With VBA

[Pricey]

Hello

I am currently writing some code to run a series of queries.

Is it possible to open an Append Query in datasheet view using vba before I append the data to a table? I am using access 07.

I am currently trying to use DoCmd.OpenQuery

Cheers

Andrew

 

[datAdrenaline]

Nope ... I would advise you to create a SELECT query for your APPEND queries source, then you can open your SELECT to see the records you are about to append.

For example:

SelectQuery1
-------------
SELECT * FROM MyTable WHERE Posted = False

AppendQuery1
--------------
INSERT INTO SomeTable (SomeField1, SomeField2)
SELECT SomeField1, SomeField2 FROM [SelectQuery1]

 

[JeanMarc22]

Yes there is a way in VBA to do this:
DoCmd.OpenQuery strQueryName, acViewDesign
DoCmd.RunCommand acCmdDatasheetView
No elegant but it works. And if it's a long running query you can interrupt it with Ctrl-Break.

 

[The_Doc_Man]

Sorry, @Pricey / Andrew - Append (actually, INSERT INTO queries) do not return recordsets. Datasheet view requires a recordset. Technically, you cannot do what you asked. JeanMarc22's suggestion probably should not work at all. If it DOES work, it would be the first time I've ever seen that happen that way.

You CAN open a SELECT query to see what is in a table. IF you were using an INSERT INTO .... SELECT FROM sequence, the SELECT FROM portion can be copied to become a stand-alone SELECT query that can easily be opened. See also the response from datAdrenaline.

 

[Gasman]

Yes there is a way in VBA to do this:
DoCmd.OpenQuery strQueryName, acViewDesign
DoCmd.RunCommand acCmdDatasheetView
No elegant but it works. And if it's a long running query you can interrupt it with Ctrl-Break.

Yes there is a way in VBA to do this:
DoCmd.OpenQuery strQueryName, acViewDesign
DoCmd.RunCommand acCmdDatasheetView
No elegant but it works. And if it's a long running query you can interrupt it with Ctrl-Break.

This is a 12 year old thread?

 

[CJ_London]

Actually 13 years

 

[The_Doc_Man]

Got fooled by the more recent response. Shows how tired I was after my morning exercise.

 

[JeanMarc22]

My bad. I didn't look at the date when I posted.

 

[isladogs]

It doesn't need to be done using VBA
You have always been able to view the data to be appended in an INSERT query by opening it in DESIGN view then clicking Datasheet View.
The same is true for other action queries - update / delete / make table

This is standard behaviour that I use all the time to check whether what I'm about to do is actually what I want to do

 

[JeanMarc22]

Very true and I use this often but the originator of this thread was asking about doing it in code.

 

[isladogs]

13 years on so I ignored the OP's comments (as he/she was last seen in 2011).
I was responding mainly to post #4 (and indirectly to post #2) but your code is, of course, doing exactly the same thing