CODEWITHSUNDEEP
sql-serverms-accesscopy
captonssj
captonssj

Reputation: 311

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."}

Upvotes: 1

Views: 2777

Answers (2)

Ralph
Ralph

Reputation: 1

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)

Upvotes: 0

THEn
THEn

Reputation: 1938

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.

Upvotes: 1

Related Questions