If I can't visit New Brunswick, where else can I walk ocean floors due to tidal erosion, like in Hopewell Rocks?
You can walk the ocean floor (exposed as sand bars) in Boundary Bay, BC. A good place is Boundary Bay Regional Park, specifically Centennial Beach, on the west side of the bay (near Tsawwassen).
The best times to go sand bar walking are the highest/lowest tides during periods of full and new moon. You can easily walk 1 km or more out from the beach. Consult a local tide table for the best times.
Hopewell Rocks has a large amount of ocean floor to explore because the tidal variation in the Bay of Fundy is extremely large, among the largest in the world. There are areas (not as spectacularly possessive of scenery, mind) in Nova Scotia on the Fundy shore, across the bay, that have similar effects. See here for some details. Note that the most significant effect is toward the northern end or bottom of the bay, where the tide gets concentrated the most. The further southeast you go, the less intense the tidal variation is. Still, the effects are significant much further down the bay, even in areas like St. Stephen and St. Andrews, NB and Digby, NS.
On the B.C. coast, the tidal variation is much less intense. Ocean floor is certainly uncovered at low tide, but not to anywhere near the significant degree that is seen in Fundy. These areas are much less deeply covered in water during high tide. (It can vary by up to 16m or 53' at the Minas Basin at the end of the Bay of Fundy in Nova Scotia/New Brunswick.)