I have used the VBA script 'AttachDSNLessT able' from the MS website to connect to my db in SQL Server 2000.

http://support.microsoft.com/kb/892490

Code:
'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     stRemoteTableName: Name of
...