Well, I figured it out finlly!! Take a look:

dim cnn as ADODB.Connection
dim strSQL as String
set cnn = new ADODB.Connection

cnn.Open "String to Access DB to Insert data Into"

'The Third Lines is the Key One. this is the SQL server DSN
strSQL = "INSERT INTO TradingDate (dTradingDate) "
strSQL = strSQL & " SELECT dTradingDate "
strSQL = strSQL & " FROM [ODBC;DSN=LIVE;UID=sa;PWD=;].Orders"

cnn.Execute strSQL

'You know the rest of this one. This is the only way I got this one to work.

Jason