Copy Table using DSN less connection (part2)

[jodjim]

I've experimented on several options to copy a table from an external database into my local Access table. After so many attempts and internet searches, my efforts were unsuccessful. So far the script below looks promising as it's only giving me "Invalid Argument" error. Please help as I'm quite desperate already. Thanks a lot.

strsql = "SELECT * INTO MyLocalTable FROM [Provider=SQLOLEDB.1;User ID=MyID;password=MyPass;Initial Catalog=MyDatabase;Data Source = MyDataSource;].MyDbTables;"

With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = strsql
.CommandType = adCmdText
.Execute 'ERROR!!!! Invalid Argument
End With

 

[lamarw]

Should the 'MyLocalTableName', 'MyID', 'MyPass', 'MyDatabase' and 'MyDataSources' be strings or are they variables? If they are variables then wouldn't you want something like:
strsql = "SELECT * INTO " & MyLocalTable & " FROM [Provider=SQLOLEDB.1;User ID=" & MyID & ";password=" & MyPass & ";Initial Catalog=" & MyDatabase & ";Data Source =" & MyDataSource & ";].MyDbTables;"

 

[jodjim]

Actually they are string and I understand they should be entered without the single quote. The data source is entered as Data Source =MySource.OurCompany.com

Any thoughts? Thanks.

 

[jodjim]

Got an idea from another forum, applied to my project, it worked. Here it is:

strsql = "SELECT * INTO " & MyLocalTable & " FROM [ODBC;Driver={SQL Server};SERVER=MyServer;DATABASE=MyDatabase;UID=MyID;PWD=MyPwd].MyTable;"

strTableName = "tblSourceTable"
If TableExists(CurrentDb, strTableName) Then
DoCmd.DeleteObject acTable, strTableName
End If

With cmd
.ActiveConnection = CurrentProject.Connection
.CommandText = strSQL
.CommandType = adCmdText
.Execute
End With

Private Function TableExists(db As Database, strTableName As String) As Boolean
Dim td As DAO.TableDef
On Error Resume Next
Set td = db.TableDefs(strTableName)
TableExists = Err.Number = 0
End Function

====
Another way is to use pass-through query. Create pass-through query and in Properties, type:
ODBC;Driver={SQL Server};SERVER=MyServer;DATABASE=MyDatabase;UID=MyID;PWD=MyPwd


Hope this will be of help to others