Relío con string para insertarlo como tipo Fecha

Hola

Tengo una tabla Pedidos2 con un campo string pero que muestra la fecha sin las barras, por ejemplo 20240429, 20240430, etc. Y yo lo que quiero es insertar ese campo a otra tabla y con el campo de tipo fecha que se llama tblPedidosPRUEBA. Quiero decir, ese campo ya está definido en la tabla como Fecha, formato fecha corta. Si paso la sentencia SQL directamente del sigiente modo, me inserta en la tabla destino, los valores convertidos en fecha: 29/04/2024, 30/04/2024, etc. OK.

Pero si lo que quiero es pasar el INSERT INTO a través de vba ahí ya no me funciona, me pone: 30/12/1899...

Esta es la sentencia SQL que funciona:

Lo que le hago al string es poner los 2 últimos dígitos que representan el día al principio, el 5º y el 6º dígito a continuación y al final los 4 primeros dígitos para ordenar el valor como: dd/mm/yyyy

Y el código vba que no funciona:

Todo parece indicar que son las comillas las que me están dando problemas. Antes me venía conformando con pasar el string y añadir las barras de separadores pero es que sigue siendo un valor string y yo lo que quiero es que sea Fecha, que funciona en SQL pero no a través de vba. He probado sin Formato, con Formato, con Formato delante, detrás, con ValueDate, con intentar convertirlo una vez insertado, mil vueltas y tiempo que estoy perdiendo que me da una rabia... ¿pero cómo puedo solucionarlo? He hecho miles de pruebas ya

Muchas gracias,

Saludos,

Hola Mihura, gracias por responder, ¿te refieres a la cadena de vba verdad? ¿y cómo debería armarla?

Así me da error:

Y de esta forma me dice que se requiere un objeto:

Del formato yanki que dices, pero en SQL, osea en la ventana de inmediato como dices, no necesito ponerla en mm/dd/yyyy ¿solo afecta a vba entonces no?