What's the relation between the pointed motivic homotopy category $\mathcal{H}_*(k)$ and the derived category of motives $\mathbf{DM}^-_{eff}(k)$ besides the representability of motivic cohomology in the homotopy category?
I think there is a functor $\mathcal{H}(k) \to \mathbf{DM}^-_{eff}(k)$. How far is it from being full and faithful?
The short answer is that they are very different, but become quite similar if you 1) stabilize, i.e invert smash product by $\mathbb{P}^1_k$ on the homotopy side and invert tensor product by the Tate motive of the motivic side and 2) pass to rational coefficients. This is the analogue of the similar result in topology : the rationalized stable homotopy category is equivalent to the derived category of $\mathbb{Q}$-vector spaces.
The precise comparison result if you do those two operations was announced by Morel in http://www.mathematik.uni-muenchen.de/~morel/Splittinggrassman.pdf and a proof was written down recently by Deglise and Cisinski in the preprint http://www.math.univ-paris13.fr/~deglise/docs/2009/DM.pdf paragraph 15.2
Even the stable, integral versions are quite different : one way to quantify this is to say that spectra in $SH_{\mathbb{A}^1}(k)$ represent generalized cohomology theories - oriented ones like motivic cohomology, algebraic K-theory, algebraic cobordism but also non-oriented like Balmer-Witt groups, Hermitian K-theory - while objects in $DM_{k}$ represent only "oriented cohomology theories with additive group law" (in the sense of Quillen) : see e.g the Déglise-Cisinski preprint above, paragraph 10.3
For the different between unstable versions, a good simple example is the case of curves of genus greater that 1 : their effective motives are non-trivial (weight one effective motivic cohomology detects Pic ) while their unstable homotopy type is in a sense completely disconnected. This is essentially the reason why unstable $\mathbb{A}^1$-homotopy seems most interesting for "nearly rational" varieties, see the papers of Asok and Morel.
In the topological setting, you have a couple of adjoint functors "singular chain complex" and "eilenberg MacLane space" between the derived category of abelian groups and the homotopy category. The same is true in the motivic setting. You can look at this article of Voevodsky for example.
Rondigs and Ostvaer also proved here that the big category of motives is equivalent to the homotopy category of modules over the motivic cohomology spectrum.
With rational coefficients these two categories are 'almost isomorphic'; this was announced by F. Morel.
Almost never! I don't know any direct construction to give a functor from the unstable homotopy category to $DM^{eff,-}(k)$. However, one can take the composition $\Sigma^{\infty}_{S^1}: Ho_*(k) \rightarrow SH_{S^1}(k)$ and $SH_{S^1}(k) \rightarrow DM^{eff}(k)$, where $SH_{S^1}(k)$ is the homotopy category of $S^1$-spectra and the latter functor is the Hurewicz functor, which goes to the unbounded effective motives. After taking $\mathbb{Q}$-localization one does have $SH_{S^1}(k)_{\mathbb{Q}} \cong DM^{eff}(k)_{\mathbb{Q}}$, provided $-1$ is a sum of square in $k$.
