Solved

Transfer data from SQL table to Access table

Posted on 2016-08-08
6
64 Views
Last Modified: 2016-08-08
I am using SQL server 2012, Access 2010.
I need to transfer data from the SQL database table named:  vntgCust
to a Access table named:  tblCust

All the fields in the SQL table should copy into the access table.

What statement can I use to accomplish this?

Note:  I don't want to setup ODBC connection names to the SQL database on each computer running this.
0
Comment
Question by:maverick0728
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
6 Comments
 
LVL 6

Expert Comment

by:Wayne Herbert
ID: 41747280
Export a .sql file of the data and layout and import it into the Access tables?
0
 

Author Comment

by:maverick0728
ID: 41747300
Thanks.  Yes, I could do that, but I'd prefer to query SQL to get the data directly.
0
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41747344
You should be able to import a SQL table via ODBC Data import directly to an Access table. You have to setup a connection to import the data from SQL Server, but you don't need it once the data is imported. Once the database is chosen, just pick the desired table to import.

If you want to import without a connection, export the SQL table to one of the import file formats supported by Access. Namely Excel, XML and text. I believe Access 2010 can import XML.

You can format a SQL table in XML thus:
SELECT * FROM sourceTable FOR XML PATH

Save the result to a file and import it into Access.

Good luck!
0
Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41747350
A direct SQL command would look like:

INSERT INTO targetTable
SELECT * FROM srcTable

You may or may not need to specify the column list ...
INSERT INTO targetTable (column1, column2, ...)
SELECT column1, Column2, ... FROM srcTable
0
 
LVL 12

Expert Comment

by:funwithdotnet
ID: 41747360
To create table & insert:

SELECT * INTO targetTable FROM srcTable
0
 
LVL 11

Accepted Solution

by:
CraigYellick earned 500 total points
ID: 41747362
You can create a linked table definition from VBA code, using an ODBC connection string that exists in the code module. That is, the connection is not defined by the local workstation. Once you have the linked table definition you can use it as a FROM table and perform  any action needed, including creating a local table copy.

Sub CreateLinkedTable()
  Dim con As String
  con = "ODBC;DRIVER=SQL Server Native Client 11.0;SERVER=myServer;Trusted_Connection=Yes;Database=Northwind;Table=dbo.Customers"
  Dim t As DAO.TableDef
  Set t = CurrentDb().CreateTableDef
  t.Connect = con
  t.SourceTableName = "Customers"
  t.Name = "LinkToCustomers'"
  CurrentDb.TableDefs.Append t
End Sub

To get the ODBC connections string, use Access to manually created a linked table ref. In the properties window you'll see a copy of the connection string.

-- Craig
0

Featured Post

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question