I've been trying to copy an SQL database table into my local Access table using DSN-less connection.
Dim Conn As New ADODB.Connection
Dim rstADO As ADODB.Recordset
Dim sConnString As String

sConnString = "Provider=SQLOLEDB.1;User ID=MyID;password=MyPwd!!;Initial Catalog=MyDb;Data Source = MyServer;" & _
"Use Procedure for Prepare=1;Auto Translate=True;Packet Size=4096;"
Conn.Open sConnString
strSQL = "SELECT * INTO MyTable FROM MyDBTable"
Conn.Execute (strSQL)
When I run the above procedure, MyTable is written back into the MYServer database. What should be the syntax for the strSQL? I tried variations of inserting IN either after MyTable or MyDBTable but it looks like my syntax is always wrong.

Any help is greatly appreciated. Thanks