Query behaving differently in VBA than when running as regular Que

[bvdahl]

Hello,

I have been struggeling with this problem the whole day, and I hope some one
can give me some insight. I have an INSERT query that I want to run from VBA,
but when I do that, I get the errormessage : "This Recordset is not
updatable. (Error 3326)"

The strange part is that when I run it as a regular query, I have no
problems. The query is based on tables that are linked to a postgrSQL. I
don't know if it is relevant or not, but just so you know.

Here are the two statements. First the VBA one:

Dim nlhe_largest As String

nlhe_largest = "INSERT INTO temp_largest_pots ( screen_name, pot_size,
game, final_hand, hole_cards, hand_history )" _
& " SELECT TOP 25 nlhe_players.screen_name, nlhe_game_players.total_won,
nlhe_game_blinds.game_level_desc, nlhe_game_players.final_hand,
nlhe_game_players.hole_cards, nlhe_hand_histories.hand_history" _
& " FROM (nlhe_players INNER JOIN ((nlhe_game_players INNER JOIN
nlhe_game ON nlhe_game_players.game_id = nlhe_game.game_id) INNER JOIN
nlhe_hand_histories ON nlhe_game.game_number =
nlhe_hand_histories.game_number) ON nlhe_players.player_id =
nlhe_game_players.player_id) INNER JOIN nlhe_game_blinds ON
nlhe_game.game_level_id = nlhe_game_blinds.game_level_id" _
& " WHERE nlhe_game.date_played > #" & Me.text_date_from & " " &
Me.text_time_from & " # And nlhe_game.date_played < # " & Me.text_date_to & "
" & Me.text_time_to & " # nlhe_game_players.total_won > 25000" _
& " ORDER BY nlhe_game_players.total_won DESC;"

DoCmd.RunSQL nlhe_largest

---------------------

INSERT INTO temp_largest_pots ( screen_name, pot_size, game, final_hand,
hole_cards, hand_history )
SELECT TOP 25 nlhe_players.screen_name, nlhe_game_players.total_won,
nlhe_game_blinds.game_level_desc, nlhe_game_players.final_hand,
nlhe_game_players.hole_cards, nlhe_hand_histories.hand_history
FROM (nlhe_players INNER JOIN ((nlhe_game_players INNER JOIN nlhe_game ON
nlhe_game_players.game_id=nlhe_game.game_id) INNER JOIN nlhe_hand_histories
ON nlhe_game.game_number=nlhe_hand_histories.game_number) ON
nlhe_players.player_id=nlhe_game_players.player_id) INNER JOIN
nlhe_game_blinds ON nlhe_game.game_level_id=nlhe_game_blinds.game_level_id
WHERE (((nlhe_game_players.total_won)>25000) AND
((nlhe_game.date_played)>#1/1/2010# And (nlhe_game.date_played)<#1/15/2010#))
ORDER BY nlhe_game_players.total_won DESC;

 

[Duane Hookom]

You seem to have extra spaces around you date/time values and are missing "
AND " about here:
& Me.text_time_to & " # nlhe_game_players.total_won > 25000" _

Duane Hookom
MS Access MVP