If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Query behaving differently in VBA than when running as regular Que
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_num ber) 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_leve l_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; ------------------- Would be really greatful for some input Baard |
#2
|
|||
|
|||
Query behaving differently in VBA than when running as regular Que
You seem to have extra spaces around you date/time values and are missing "
AND " about he & Me.text_time_to & " # nlhe_game_players.total_won 25000" _ Duane Hookom MS Access MVP "bvdahl" wrote in message ... 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_num ber) 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_leve l_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; ------------------- Would be really greatful for some input Baard |
Thread Tools | |
Display Modes | |
|
|