Copy Table from SQL Server Express to Access MDB in VB.NET

Can't get this query to work for some reason

SELECT * INTO TableName 
FROM [ODBC;Driver=SQL Server;Server=localhost\SQLEXPRESS;Database=DBName;Trusted_Connection=Yes;].[TableName]

Keep getting Error :

{"ODBC--connection to '开发者_开发知识库SQL Serverlocalhost\SQLEXPRESS' failed."}

Create a linked table in access file and then with simple query you can insert to access table. Use following code to create a linked table in access.

DoCmd.TransferDatabase acLink, "ODBC Database", _
                               "ODBC;Driver=SQL Server;Server=localhost\SQLEXPRESS;Database=DBName;Trusted_Connection=Yes;",_
                               acTable, "dbo.[tablename", "tablename"

If can't create a linked table then try changing the connection string.

Very old post but this is simple and it works

Dim AccConn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + DBPath)

Dim AccessCommand As New System.Data.OleDb.OleDbCommand("SELECT * INTO [NewTableName] FROM [ODBC; Driver=SQL Server;Server=...;Database=...;Trusted_Connection=yes].[TableNameOnSQLServer]", AccConn)