I am trying to Insert thr data from MS Access table to MS SQL Server using "OPENROWSET" .. But could not make it run...

I am using VB 6.0 , ADO 2.7
MS Access 2000 (User=Admin,Passward=password), MS SQL Server 2000..


Follwing is my code... (Code in Red)
Dim cnAccess As New ADODB.Connection
Dim cnSQL As New ADODB.Connection
Dim strSQL$

strSQL = "Provider=sqloledb;" & _
"Data Source=mlabserver2;Initial Catalog=dbDataTransfer;User Id=sa;Password=password;"

cnSQL.ConnectionString = strSQL
cnSQL.Open


' This code gives me
'Run-time error '-2147217900(80040e4d)
'Line 1: Incorrect syntax near ",".
'With DSN
cnSQL.Execute "SELECT * INTO tblTest2 " & _
"FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','myDSN','C:\P rogram Files\Common Files\System\System.mdw','Admin','password'," & _
"'SELECT * FROM tblTest')"


' This code gives me
'Run-time error '-2147217900(80040e4d)
'Line 1: Incorrect syntax near ",".
'With out DSN
cnSQL.Execute "SELECT * INTO tblTest2 " & _
"FROM OPENROWSET('Provider=Microsoft.Jet.OLEDB.4.0'," & _
"'Data Source=C:\Test.mdb'," & _
"'Jet OLEDBystem Database=C:\Program Files\Common Files\System\System.mdw'," & _
"'User Id=Admin'," & _
"'Password=password'," & _
"'SELECT * FROM tblTest')"


' THis code gives me
'Server: Msg 7399, Level 16, State 1, Line 1
'OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error. Authentication failed.
'[OLE/DB provider returned message: Cannot start your application. The workgroup 'information file is missing or opened exclusively by another user.]
cnSQL.Execute "SELECT * INTO tblTest2 " & _
"FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0'," & _
"'C:\Test.mdb';" & _
"'Admin';" & _
"'password'," & _
"'SELECT * FROM tblTest')"


Please help me to solve this out. I am trying this for past 3 days but no luck.

Thanks

Now it is working wth DSN

Below is the working code

cnSQL.Execute "SELECT * INTO tblTest5 " & _
"FROM OPENROWSET('MSDASQL','CRS';'Admin';'password'," & _
"'SELECT * FROM tblTest')"


but I would not be having the DSN. I am trying with follwing code


cnSQL.Execute "SELECT * INTO tblTest5 " & _
"FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0','C:\Test.mdb' ,'C:\Program Files\Common Files\System\System.mdw';'Admin';'password'," & _
"'SELECT * FROM tblTest')"


Problem is with workgroup file name. If I don't use User/Password then it is working but when I give user password then I need to give workgroup file also which it is not excepting...

Any Idea