Solved

Transfer data from SQL table to Access table

Posted on 2016-08-08
6
52 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
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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

856 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